CREATE TABLE IF NOT EXISTS ledgers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS people (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS contributions (
    id INTEGER PRIMARY KEY,
    ledger_id INTEGER NOT NULL REFERENCES ledgers(id),
    person_id INTEGER REFERENCES people(id),
    amount NUMERIC NOT NULL,
    contributed_on TEXT NOT NULL,
    note TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS expenses (
    id INTEGER PRIMARY KEY,
    ledger_id INTEGER NOT NULL REFERENCES ledgers(id),
    category TEXT NOT NULL,
    amount NUMERIC NOT NULL,
    paid_by_id INTEGER REFERENCES people(id),
    status TEXT,
    spent_on TEXT NOT NULL,
    note TEXT,
    lhdn_claimable INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS receipts (
    id INTEGER PRIMARY KEY,
    expense_id INTEGER NOT NULL REFERENCES expenses(id),
    file_path TEXT NOT NULL,
    original_name TEXT,
    uploaded_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_contributions_ledger ON contributions(ledger_id);
CREATE INDEX IF NOT EXISTS idx_expenses_ledger ON expenses(ledger_id);
CREATE INDEX IF NOT EXISTS idx_expenses_lhdn ON expenses(lhdn_claimable);
CREATE INDEX IF NOT EXISTS idx_receipts_expense ON receipts(expense_id);