-- Transactions store for Lunch Money pipeline.

CREATE TABLE IF NOT EXISTS transactions (
    id TEXT PRIMARY KEY,                       -- Lunch Money transaction id (stringified)
    account_id TEXT,                            -- stringified asset_id or plaid_account_id
    account_source TEXT,                        -- 'asset' (manual) or 'plaid' (linked)
    date TEXT NOT NULL,                        -- ISO YYYY-MM-DD
    payee TEXT NOT NULL,                       -- Lunch Money's cleaned merchant
    amount REAL NOT NULL,                      -- positive = expense, negative = income
    currency TEXT DEFAULT 'usd',
    category_id INTEGER,
    category_name TEXT,                         -- denormalized for fast filter
    notes TEXT,
    status TEXT,                                -- cleared, uncleared, pending, recurring
    is_pending INTEGER DEFAULT 0,
    recurring_id INTEGER,                       -- non-null if Lunch Money matched a recurring pattern
    raw_json TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_transactions_date ON transactions(date);
CREATE INDEX IF NOT EXISTS idx_transactions_payee ON transactions(payee);
CREATE INDEX IF NOT EXISTS idx_transactions_recurring ON transactions(recurring_id);
CREATE INDEX IF NOT EXISTS idx_transactions_category ON transactions(category_name);

CREATE TABLE IF NOT EXISTS accounts (
    id TEXT PRIMARY KEY,
    source TEXT NOT NULL,                       -- 'asset' or 'plaid'
    name TEXT,
    type TEXT,
    subtype TEXT,
    mask TEXT,
    balance REAL,
    currency TEXT DEFAULT 'usd',
    institution_name TEXT,
    last_synced_at TEXT
);

CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    is_income INTEGER DEFAULT 0,
    is_group INTEGER DEFAULT 0,
    group_id INTEGER,
    excluded_from_budget INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS recurring_groups (
    id INTEGER PRIMARY KEY,                    -- Lunch Money recurring_expense id
    payee TEXT NOT NULL,
    typical_amount REAL,
    cadence TEXT,
    start_date TEXT,
    end_date TEXT,
    category_id INTEGER,
    notes TEXT,
    raw_json TEXT
);

CREATE TABLE IF NOT EXISTS analyses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,
    period_start TEXT,
    period_end TEXT,
    summary_md TEXT,
    structured_json TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
