migrations/0021_settlement_ledger.sql

-- 0021_settlement_ledger.sql

CREATE TABLE IF NOT EXISTS settlements (
    settlement_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id VARCHAR NOT NULL REFERENCES orders(transaction_id),
    merchant_id VARCHAR NOT NULL REFERENCES merchants(merchant_id),
    gross_amount_inr DOUBLE PRECISION NOT NULL,
    platform_fee_inr DOUBLE PRECISION NOT NULL,
    delivery_fee_inr DOUBLE PRECISION NOT NULL,
    tax_amount_inr DOUBLE PRECISION NOT NULL,
    net_payout_inr DOUBLE PRECISION NOT NULL,
    utr_number VARCHAR,
    settled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR NOT NULL DEFAULT 'COMPLETED' -- 'COMPLETED', 'FAILED', 'REVERSED'
);

CREATE INDEX idx_settlements_merchant_id ON settlements(merchant_id);
CREATE INDEX idx_settlements_transaction_id ON settlements(transaction_id);