-- v4.25: transfer documents with in-transit state.
-- Transfers become documents: draft -> sent -> received (or cancelled).
-- Actual stock movement happens on 'receive' (origin -, destination +),
-- so goods in transit are tracked and discrepancies are explicit.

CREATE TABLE IF NOT EXISTS inventory_transfers (
    id SERIAL PRIMARY KEY,
    from_branch_id INTEGER NOT NULL REFERENCES branches(id),
    to_branch_id INTEGER NOT NULL REFERENCES branches(id),
    status VARCHAR(20) NOT NULL DEFAULT 'draft',  -- draft | sent | received | cancelled
    notes TEXT,
    employee_id INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    sent_at TIMESTAMPTZ,
    received_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS inventory_transfer_lines (
    id SERIAL PRIMARY KEY,
    transfer_id INTEGER NOT NULL REFERENCES inventory_transfers(id) ON DELETE CASCADE,
    inventory_id INTEGER NOT NULL REFERENCES inventory(id),
    quantity INTEGER NOT NULL,
    qty_received INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_transfers_status ON inventory_transfers(status);
CREATE INDEX IF NOT EXISTS idx_transfer_lines_transfer ON inventory_transfer_lines(transfer_id);