-- A cash advance is money handed to a team up front so the team can go shopping
-- (kasbon in the Indonesian UI). It is deliberately not an expense: while the money
-- sits with the team it is still the outlet's, and what was actually spent is read
-- from the purchase orders and expenses charged back to the advance. Nothing about
-- that spending is copied here.
CREATE TABLE cash_advances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    outlet_id UUID NOT NULL REFERENCES outlets(id) ON DELETE CASCADE,
    code_number VARCHAR(50) NOT NULL,
    -- Same team shape as purchase_orders, with one difference: an advance is handed
    -- to a team, so there is no "no team chosen yet" state and team_scope is NOT NULL.
    team_scope VARCHAR(20) NOT NULL,
    team_category_id UUID REFERENCES categories(id) ON DELETE RESTRICT,
    amount DECIMAL(15,2) NOT NULL DEFAULT 0,
    -- Cash the team brought back unspent. Spending is not stored: it is summed from
    -- the purchase orders and expenses that point at this advance.
    returned_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
    issued_date DATE NOT NULL,
    due_date DATE,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Written as a CASE for the same reason as purchase_orders: an OR chain would
    -- evaluate to NULL for an unexpected scope and a CHECK only rejects FALSE.
    CONSTRAINT chk_cash_advances_team CHECK (
        CASE
            WHEN team_scope = 'category' THEN team_category_id IS NOT NULL
            WHEN team_scope = 'central'  THEN team_category_id IS NULL
            ELSE false
        END
    ),
    CONSTRAINT chk_cash_advances_amounts CHECK (amount >= 0 AND returned_amount >= 0)
);

-- Leading with organization_id means this also serves the plain per-organization
-- lookups, so there is no separate index on that column.
CREATE UNIQUE INDEX idx_cash_advances_organization_id_code_number ON cash_advances(organization_id, code_number);
CREATE INDEX idx_cash_advances_outlet_id ON cash_advances(outlet_id);
CREATE INDEX idx_cash_advances_team_category_id ON cash_advances(team_category_id);
CREATE INDEX idx_cash_advances_team_scope ON cash_advances(team_scope);
CREATE INDEX idx_cash_advances_issued_date ON cash_advances(issued_date);
CREATE INDEX idx_cash_advances_status ON cash_advances(status);