schema.sql

-- =============================================================================
-- Fire Tools — Database Schema
-- =============================================================================
-- Target dialects:
--   * SQLite 3.38+  (first-class)
--   * PostgreSQL 14+ (compatible — see docs/database/README.md for notes)
--
-- Conventions:
--   * snake_case identifiers
--   * Surrogate INTEGER primary keys (SQLite ROWID alias; Postgres treats as
--     int4 and we rely on app-side ID generation or sequences — see README)
--   * Stable string "external" ids (e.g. asset.external_id) for IDs minted by
--     the client (UI uses ad-hoc string ids like `nw-...`, `txn-...`)
--   * Enum-like columns use TEXT + CHECK (... IN (...))
--   * Timestamps stored as TEXT in ISO-8601 UTC (works on SQLite + Postgres)
--   * Monetary values stored as REAL — precision is sufficient for FIRE
--     planning (consumer-grade, never accounting). Backend MAY upgrade to
--     NUMERIC on Postgres.
--   * Foreign keys cascade on user deletion.
--
-- SQLite reminder: PRAGMA foreign_keys = ON; must be set per connection.
-- Postgres reminder: replace `INTEGER PRIMARY KEY` with
--     `INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY` if you want
--     server-side IDs (see schema.postgres.sql notes in README).
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 0. Users (single-user-by-default; ready for multi-tenant)
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY,
    email         TEXT NOT NULL UNIQUE,
    display_name  TEXT,
    created_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Bootstrap the local single-user row. Backend code should run this once.
INSERT INTO users (id, email, display_name)
SELECT 1, 'local@firetools.local', 'Local User'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 1);

-- -----------------------------------------------------------------------------
-- 1. User settings & preferences
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS user_settings (
    user_id                          INTEGER PRIMARY KEY
        REFERENCES users(id) ON DELETE CASCADE,
    account_name                     TEXT    NOT NULL DEFAULT 'My Portfolio',
    decimal_separator                TEXT    NOT NULL DEFAULT '.'
        CHECK (decimal_separator IN ('.', ',')),
    decimal_places                   INTEGER NOT NULL DEFAULT 2,
    default_currency                 TEXT    NOT NULL DEFAULT 'EUR'
        CHECK (default_currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    use_api_rates                    INTEGER NOT NULL DEFAULT 1 CHECK (use_api_rates IN (0,1)),
    last_api_update                  TEXT,
    fallback_rates_json              TEXT    NOT NULL DEFAULT '{}', -- JSON map ISO→EUR rate
    privacy_mode                     INTEGER NOT NULL DEFAULT 0 CHECK (privacy_mode IN (0,1)),
    country                          TEXT,                          -- ISO 3166-1 alpha-2
    date_format                      TEXT    NOT NULL DEFAULT 'DD/MM/YYYY'
        CHECK (date_format IN ('DD/MM/YYYY','MM/DD/YYYY','YYYY-MM-DD')),
    fire_asset_class_inclusion_json  TEXT    NOT NULL DEFAULT '{}', -- JSON: Record<AssetClass,bool>
    include_primary_residence_in_fire INTEGER NOT NULL DEFAULT 1 CHECK (include_primary_residence_in_fire IN (0,1)),
    search_threshold                 INTEGER NOT NULL DEFAULT 8,
    experimental_features_json       TEXT    NOT NULL DEFAULT '{}', -- JSON: ExperimentalFeatures
    llm_base_url                     TEXT,
    llm_api_key                      TEXT,                          -- store encrypted server-side
    llm_model                        TEXT,
    updated_at                       TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notification_preferences (
    user_id                       INTEGER PRIMARY KEY
        REFERENCES users(id) ON DELETE CASCADE,
    enable_in_app_notifications   INTEGER NOT NULL DEFAULT 1 CHECK (enable_in_app_notifications IN (0,1)),
    new_month_reminders           INTEGER NOT NULL DEFAULT 1 CHECK (new_month_reminders IN (0,1)),
    new_quarter_reminders         INTEGER NOT NULL DEFAULT 1 CHECK (new_quarter_reminders IN (0,1)),
    tax_reminders                 INTEGER NOT NULL DEFAULT 1 CHECK (tax_reminders IN (0,1)),
    dca_reminders                 INTEGER NOT NULL DEFAULT 1 CHECK (dca_reminders IN (0,1)),
    portfolio_alerts              INTEGER NOT NULL DEFAULT 1 CHECK (portfolio_alerts IN (0,1)),
    fire_milestones               INTEGER NOT NULL DEFAULT 1 CHECK (fire_milestones IN (0,1)),
    enable_email_notifications    INTEGER NOT NULL DEFAULT 0 CHECK (enable_email_notifications IN (0,1)),
    email_address                 TEXT    NOT NULL DEFAULT '',
    email_frequency               TEXT    NOT NULL DEFAULT 'NEVER'
        CHECK (email_frequency IN ('DAILY','WEEKLY','MONTHLY','NEVER')),
    tax_reminder_months_json      TEXT    NOT NULL DEFAULT '[3,6,9,12]', -- JSON int[]
    tax_reminder_days_before      INTEGER NOT NULL DEFAULT 7,
    last_checked                  TEXT,
    updated_at                    TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- -----------------------------------------------------------------------------
-- 2. Notifications
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS notifications (
    id            INTEGER PRIMARY KEY,
    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    external_id   TEXT NOT NULL,                  -- client-minted id (notif-...)
    type          TEXT NOT NULL
        CHECK (type IN ('NEW_MONTH','NEW_QUARTER','TAX_REMINDER','INCOME_LOGGED',
                        'EXPENSE_LOGGED','NET_WORTH_UPDATE','DCA_REMINDER',
                        'FIRE_MILESTONE','PORTFOLIO_REBALANCE','SYSTEM','WELCOME')),
    title         TEXT NOT NULL,
    message       TEXT NOT NULL,
    priority      TEXT NOT NULL DEFAULT 'MEDIUM'
        CHECK (priority IN ('LOW','MEDIUM','HIGH')),
    action_url    TEXT,
    action_label  TEXT,
    is_read       INTEGER NOT NULL DEFAULT 0 CHECK (is_read IN (0,1)),
    timestamp     TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at    TEXT,
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_notifications_user_unread
    ON notifications (user_id, is_read, timestamp DESC);

-- -----------------------------------------------------------------------------
-- 3. FIRE Calculator inputs
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS calculator_inputs (
    user_id                          INTEGER PRIMARY KEY
        REFERENCES users(id) ON DELETE CASCADE,
    initial_savings                  REAL    NOT NULL DEFAULT 0,
    stocks_percent                   REAL    NOT NULL DEFAULT 60,
    bonds_percent                    REAL    NOT NULL DEFAULT 30,
    cash_percent                     REAL    NOT NULL DEFAULT 10,
    current_annual_expenses          REAL    NOT NULL DEFAULT 0,
    fire_annual_expenses             REAL    NOT NULL DEFAULT 0,
    annual_labor_income              REAL    NOT NULL DEFAULT 0,
    labor_income_growth_rate         REAL    NOT NULL DEFAULT 0,
    savings_rate                     REAL    NOT NULL DEFAULT 0,
    desired_withdrawal_rate          REAL    NOT NULL DEFAULT 4,
    years_of_expenses                REAL    NOT NULL DEFAULT 25,
    expected_stock_return            REAL    NOT NULL DEFAULT 7,
    expected_bond_return             REAL    NOT NULL DEFAULT 3,
    expected_cash_return             REAL    NOT NULL DEFAULT -2,
    year_of_birth                    INTEGER NOT NULL DEFAULT 1990,
    retirement_age                   INTEGER NOT NULL DEFAULT 65,
    state_pension_income             REAL    NOT NULL DEFAULT 0,
    private_pension_income           REAL    NOT NULL DEFAULT 0,
    other_income                     REAL    NOT NULL DEFAULT 0,
    stop_working_at_fire             INTEGER NOT NULL DEFAULT 0 CHECK (stop_working_at_fire IN (0,1)),
    max_age                          INTEGER NOT NULL DEFAULT 100,
    use_asset_allocation_value       INTEGER NOT NULL DEFAULT 0 CHECK (use_asset_allocation_value IN (0,1)),
    use_expense_tracker_expenses     INTEGER NOT NULL DEFAULT 0 CHECK (use_expense_tracker_expenses IN (0,1)),
    use_expense_tracker_income       INTEGER NOT NULL DEFAULT 0 CHECK (use_expense_tracker_income IN (0,1)),
    updated_at                       TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Saved Monte Carlo runs (one row per run; per-simulation detail stored as JSON
-- to keep row counts sane — these can be tens of thousands of yearly entries).
CREATE TABLE IF NOT EXISTS monte_carlo_runs (
    id                       INTEGER PRIMARY KEY,
    user_id                  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    run_at                   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    num_simulations          INTEGER NOT NULL,
    stock_volatility         REAL    NOT NULL,
    bond_volatility          REAL    NOT NULL,
    black_swan_probability   REAL    NOT NULL,
    black_swan_impact        REAL    NOT NULL,
    success_count            INTEGER NOT NULL,
    failure_count            INTEGER NOT NULL,
    success_rate             REAL    NOT NULL,
    median_years_to_fire     REAL,
    fixed_parameters_json    TEXT    NOT NULL, -- MonteCarloFixedParameters
    logs_json                TEXT             -- optional SimulationLogEntry[]
);
CREATE INDEX IF NOT EXISTS idx_monte_carlo_runs_user_time
    ON monte_carlo_runs (user_id, run_at DESC);

-- -----------------------------------------------------------------------------
-- 4. Asset Allocation Manager
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS asset_allocation_config (
    user_id                       INTEGER PRIMARY KEY
        REFERENCES users(id) ON DELETE CASCADE,
    currency                      TEXT    NOT NULL DEFAULT 'EUR'
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    allow_negative_cash           INTEGER NOT NULL DEFAULT 0 CHECK (allow_negative_cash IN (0,1)),
    target_allocation_tolerance   REAL    NOT NULL DEFAULT 2,
    updated_at                    TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS assets (
    id                    INTEGER PRIMARY KEY,
    user_id               INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    external_id           TEXT    NOT NULL,            -- client-minted id
    name                  TEXT    NOT NULL,
    ticker                TEXT    NOT NULL DEFAULT '',
    isin                  TEXT,
    asset_class           TEXT    NOT NULL
        CHECK (asset_class IN ('STOCKS','BONDS','CASH','CRYPTO','REAL_ESTATE',
                               'COMMODITIES','VEHICLE','COLLECTIBLE','ART')),
    sub_asset_type        TEXT    NOT NULL DEFAULT 'NONE'
        CHECK (sub_asset_type IN ('ETF','SINGLE_STOCK','SINGLE_BOND',
                                   'SAVINGS_ACCOUNT','CHECKING_ACCOUNT','BROKERAGE_ACCOUNT',
                                   'MONEY_ETF','COIN','PROPERTY','REIT','PRIVATE_EQUITY',
                                   'PHYSICAL_GOLD','GOLD_ETC','SILVER_ETC','OIL_ETC',
                                   'NATURAL_GAS_ETC','COPPER_ETC','PLATINUM_ETC',
                                   'PALLADIUM_ETC','AGRICULTURAL_ETC','COMMODITY_ETF',
                                   'CAR','MOTORCYCLE','BOAT','OTHER_VEHICLE',
                                   'WATCH','WINE','JEWELRY','SPORTS_MEMORABILIA','OTHER_COLLECTIBLE',
                                   'PAINTING','SCULPTURE','DIGITAL_ART','OTHER_ART','NONE')),
    current_value         REAL    NOT NULL DEFAULT 0,  -- in EUR (config currency)
    shares                REAL,
    price_per_share       REAL,
    acquisition_price     REAL,
    original_currency     TEXT
        CHECK (original_currency IS NULL OR original_currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    original_value        REAL,
    target_mode           TEXT    NOT NULL DEFAULT 'OFF'
        CHECK (target_mode IN ('PERCENTAGE','OFF','SET')),
    target_value          REAL,
    target_percent        REAL,
    institution_code      TEXT,
    institution_name      TEXT,
    is_primary_residence  INTEGER NOT NULL DEFAULT 0 CHECK (is_primary_residence IN (0,1)),
    market_price          REAL,
    -- Mortgage fields (only meaningful for real estate)
    mortgage_principal_amount   REAL,
    mortgage_current_balance    REAL,
    mortgage_interest_rate      REAL,
    mortgage_term_years         INTEGER,
    mortgage_remaining_years    INTEGER,
    mortgage_monthly_payment    REAL,
    mortgage_start_date         TEXT,
    mortgage_property_value     REAL,
    mortgage_lender             TEXT,
    created_at            TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_assets_user_class ON assets (user_id, asset_class);

-- -----------------------------------------------------------------------------
-- 5. Expense / Income Tracker
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS expense_tracker_config (
    user_id      INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    currency     TEXT    NOT NULL DEFAULT 'EUR'
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    current_year  INTEGER NOT NULL,
    current_month INTEGER NOT NULL CHECK (current_month BETWEEN 1 AND 12),
    updated_at   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS expense_years (
    id           INTEGER PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    year         INTEGER NOT NULL,
    is_archived  INTEGER NOT NULL DEFAULT 0 CHECK (is_archived IN (0,1)),
    UNIQUE (user_id, year),
    UNIQUE (user_id, id)          -- target for same-user composite FKs
);

CREATE TABLE IF NOT EXISTS expense_months (
    id              INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    expense_year_id INTEGER NOT NULL,
    month           INTEGER NOT NULL CHECK (month BETWEEN 1 AND 12),
    is_closed       INTEGER NOT NULL DEFAULT 0 CHECK (is_closed IN (0,1)),
    UNIQUE (expense_year_id, month),
    UNIQUE (user_id, id),         -- target for same-user composite FKs
    FOREIGN KEY (user_id, expense_year_id)
        REFERENCES expense_years (user_id, id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS expense_entries (
    id                 INTEGER PRIMARY KEY,
    user_id            INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expense_month_id   INTEGER NOT NULL,
    external_id        TEXT    NOT NULL,                          -- txn-...
    date               TEXT    NOT NULL,                          -- YYYY-MM-DD
    amount             REAL    NOT NULL,
    description        TEXT    NOT NULL,
    currency           TEXT
        CHECK (currency IS NULL OR currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    category           TEXT    NOT NULL,                          -- built-in id or custom id
    sub_category       TEXT,
    expense_type       TEXT    NOT NULL CHECK (expense_type IN ('NEED','WANT')),
    is_recurring       INTEGER NOT NULL DEFAULT 0 CHECK (is_recurring IN (0,1)),
    UNIQUE (user_id, external_id),
    -- Ensure the referenced month belongs to the same user (tenant isolation)
    FOREIGN KEY (user_id, expense_month_id)
        REFERENCES expense_months (user_id, id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_expense_entries_month ON expense_entries (expense_month_id, date);
CREATE INDEX IF NOT EXISTS idx_expense_entries_category ON expense_entries (user_id, category);

CREATE TABLE IF NOT EXISTS income_entries (
    id                 INTEGER PRIMARY KEY,
    user_id            INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expense_month_id   INTEGER NOT NULL REFERENCES expense_months(id) ON DELETE CASCADE,
    external_id        TEXT    NOT NULL,
    date               TEXT    NOT NULL,
    amount             REAL    NOT NULL,
    description        TEXT    NOT NULL,
    currency           TEXT
        CHECK (currency IS NULL OR currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    source             TEXT    NOT NULL
        CHECK (source IN ('SALARY','FREELANCE','BUSINESS','INVESTMENTS','RENTAL',
                          'PENSION','SOCIAL_SECURITY','BONUS','GIFT','OTHER')),
    is_recurring       INTEGER NOT NULL DEFAULT 0 CHECK (is_recurring IN (0,1)),
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_income_entries_month ON income_entries (expense_month_id, date);

-- Per-month budgets (NULL expense_month_id ⇒ global budget)
CREATE TABLE IF NOT EXISTS category_budgets (
    id                 INTEGER PRIMARY KEY,
    user_id            INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expense_month_id   INTEGER REFERENCES expense_months(id) ON DELETE CASCADE,
    category           TEXT    NOT NULL,
    monthly_budget     REAL    NOT NULL,
    currency           TEXT
        CHECK (currency IS NULL OR currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    UNIQUE (user_id, expense_month_id, category)
);

CREATE TABLE IF NOT EXISTS custom_categories (
    id                     INTEGER PRIMARY KEY,
    user_id                INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    external_id            TEXT    NOT NULL,
    name                   TEXT    NOT NULL,
    icon                   TEXT    NOT NULL,
    color                  TEXT    NOT NULL,
    default_expense_type   TEXT    NOT NULL CHECK (default_expense_type IN ('NEED','WANT')),
    UNIQUE (user_id, external_id)
);

CREATE TABLE IF NOT EXISTS category_overrides (
    id           INTEGER PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id  TEXT    NOT NULL,           -- built-in ExpenseCategory id
    name         TEXT,
    icon         TEXT,
    color        TEXT,
    UNIQUE (user_id, category_id)
);

-- -----------------------------------------------------------------------------
-- 6. Net Worth Tracker
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS net_worth_config (
    user_id                       INTEGER PRIMARY KEY
        REFERENCES users(id) ON DELETE CASCADE,
    default_currency              TEXT    NOT NULL DEFAULT 'EUR'
        CHECK (default_currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    current_year                  INTEGER NOT NULL,
    current_month                 INTEGER NOT NULL CHECK (current_month BETWEEN 1 AND 12),
    show_pension_in_net_worth     INTEGER NOT NULL DEFAULT 1 CHECK (show_pension_in_net_worth IN (0,1)),
    include_unrealized_gains      INTEGER NOT NULL DEFAULT 1 CHECK (include_unrealized_gains IN (0,1)),
    sync_with_asset_allocation    INTEGER NOT NULL DEFAULT 0 CHECK (sync_with_asset_allocation IN (0,1)),
    updated_at                    TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS net_worth_years (
    id           INTEGER PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    year         INTEGER NOT NULL,
    is_archived  INTEGER NOT NULL DEFAULT 0 CHECK (is_archived IN (0,1)),
    UNIQUE (user_id, year),
    UNIQUE (user_id, id)          -- target for same-user composite FKs
);

CREATE TABLE IF NOT EXISTS net_worth_months (
    id                  INTEGER PRIMARY KEY,
    user_id             INTEGER NOT NULL,
    net_worth_year_id   INTEGER NOT NULL,
    month               INTEGER NOT NULL CHECK (month BETWEEN 1 AND 12),
    is_frozen           INTEGER NOT NULL DEFAULT 0 CHECK (is_frozen IN (0,1)),
    frozen_date         TEXT,
    month_note          TEXT,
    UNIQUE (net_worth_year_id, month),
    UNIQUE (user_id, id),         -- target for same-user composite FKs
    FOREIGN KEY (user_id, net_worth_year_id)
        REFERENCES net_worth_years (user_id, id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS asset_holdings (
    id                          INTEGER PRIMARY KEY,
    user_id                     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    net_worth_month_id          INTEGER NOT NULL,
    external_id                 TEXT    NOT NULL,
    ticker                      TEXT    NOT NULL,
    name                        TEXT    NOT NULL,
    shares                      REAL    NOT NULL DEFAULT 0,
    price_per_share             REAL    NOT NULL DEFAULT 0,
    acquisition_price           REAL,
    currency                    TEXT    NOT NULL
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    asset_class                 TEXT    NOT NULL
        CHECK (asset_class IN ('STOCKS','BONDS','ETF','CRYPTO','REAL_ESTATE',
                               'PRIVATE_EQUITY','VEHICLE','COLLECTIBLE','ART',
                               'COMMODITIES','OTHER')),
    note                        TEXT,
    isin                        TEXT,
    is_primary_residence        INTEGER NOT NULL DEFAULT 0 CHECK (is_primary_residence IN (0,1)),
    -- Sync metadata mirrored from asset allocation
    target_mode                 TEXT    CHECK (target_mode IS NULL OR target_mode IN ('PERCENTAGE','OFF','SET')),
    target_percent              REAL,
    target_value                REAL,
    sync_asset_class            TEXT    CHECK (sync_asset_class IS NULL OR sync_asset_class IN
        ('STOCKS','BONDS','CASH','CRYPTO','REAL_ESTATE','COMMODITIES','VEHICLE','COLLECTIBLE','ART')),
    sync_sub_asset_type         TEXT,
    -- Vehicle depreciation
    vehicle_depreciation_method TEXT    CHECK (vehicle_depreciation_method IS NULL OR
        vehicle_depreciation_method IN ('STRAIGHT_LINE','DECLINING_BALANCE','MANUAL')),
    vehicle_purchase_price      REAL,
    vehicle_purchase_date       TEXT,
    vehicle_salvage_value       REAL,
    vehicle_useful_life_years   INTEGER,
    vehicle_current_depreciation REAL,
    vehicle_annual_dep_rate     REAL,
    -- Mortgage info
    mortgage_principal_amount   REAL,
    mortgage_current_balance    REAL,
    mortgage_interest_rate      REAL,
    mortgage_term_years         INTEGER,
    mortgage_remaining_years    INTEGER,
    mortgage_monthly_payment    REAL,
    mortgage_start_date         TEXT,
    mortgage_lender             TEXT,
    UNIQUE (user_id, external_id),
    -- Ensure the referenced month belongs to the same user (tenant isolation)
    FOREIGN KEY (user_id, net_worth_month_id)
        REFERENCES net_worth_months (user_id, id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_asset_holdings_month ON asset_holdings (net_worth_month_id);

CREATE TABLE IF NOT EXISTS cash_entries (
    id                  INTEGER PRIMARY KEY,
    user_id             INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    net_worth_month_id  INTEGER NOT NULL REFERENCES net_worth_months(id) ON DELETE CASCADE,
    external_id         TEXT    NOT NULL,
    account_name        TEXT    NOT NULL,
    account_type        TEXT    NOT NULL
        CHECK (account_type IN ('SAVINGS','CHECKING','BROKERAGE','CREDIT_CARD','OTHER')),
    balance             REAL    NOT NULL DEFAULT 0,
    currency            TEXT    NOT NULL
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    note                TEXT,
    institution_code    TEXT,
    institution_name    TEXT,
    shares              REAL,
    price_per_share     REAL,
    target_mode         TEXT    CHECK (target_mode IS NULL OR target_mode IN ('PERCENTAGE','OFF','SET')),
    target_percent      REAL,
    target_value        REAL,
    sync_sub_asset_type TEXT,
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_cash_entries_month ON cash_entries (net_worth_month_id);

CREATE TABLE IF NOT EXISTS pension_entries (
    id                  INTEGER PRIMARY KEY,
    user_id             INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    net_worth_month_id  INTEGER NOT NULL REFERENCES net_worth_months(id) ON DELETE CASCADE,
    external_id         TEXT    NOT NULL,
    name                TEXT    NOT NULL,
    current_value       REAL    NOT NULL DEFAULT 0,
    currency            TEXT    NOT NULL
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    pension_type        TEXT    NOT NULL
        CHECK (pension_type IN ('STATE','PRIVATE','EMPLOYER','OTHER')),
    note                TEXT,
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_pension_entries_month ON pension_entries (net_worth_month_id);

CREATE TABLE IF NOT EXISTS debt_entries (
    id                  INTEGER PRIMARY KEY,
    user_id             INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    net_worth_month_id  INTEGER NOT NULL REFERENCES net_worth_months(id) ON DELETE CASCADE,
    external_id         TEXT    NOT NULL,
    name                TEXT    NOT NULL,
    debt_type           TEXT    NOT NULL
        CHECK (debt_type IN ('CREDIT_CARD','PERSONAL_LOAN','STUDENT_LOAN','CAR_LOAN','MORTGAGE','OTHER')),
    current_balance     REAL    NOT NULL DEFAULT 0,
    interest_rate       REAL,
    monthly_payment     REAL,
    currency            TEXT    NOT NULL
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    note                TEXT,
    creditor            TEXT,
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_debt_entries_month ON debt_entries (net_worth_month_id);

CREATE TABLE IF NOT EXISTS tax_entries (
    id                  INTEGER PRIMARY KEY,
    user_id             INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    net_worth_month_id  INTEGER NOT NULL REFERENCES net_worth_months(id) ON DELETE CASCADE,
    external_id         TEXT    NOT NULL,
    name                TEXT    NOT NULL,
    tax_type            TEXT    NOT NULL
        CHECK (tax_type IN ('INCOME_TAX','PROPERTY_TAX','CAPITAL_GAINS_TAX','OTHER')),
    amount              REAL    NOT NULL DEFAULT 0,
    due_date            TEXT,
    currency            TEXT    NOT NULL
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    note                TEXT,
    is_paid             INTEGER NOT NULL DEFAULT 0 CHECK (is_paid IN (0,1)),
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_tax_entries_month ON tax_entries (net_worth_month_id);

CREATE TABLE IF NOT EXISTS financial_operations (
    id                  INTEGER PRIMARY KEY,
    user_id             INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    net_worth_month_id  INTEGER NOT NULL REFERENCES net_worth_months(id) ON DELETE CASCADE,
    external_id         TEXT    NOT NULL,
    date                TEXT    NOT NULL,
    type                TEXT    NOT NULL
        CHECK (type IN ('PURCHASE','SALE','DIVIDEND','EXPENSE_REIMBURSEMENT',
                        'GIFT_RECEIVED','GIFT_GIVEN','TAX_PAID','CASH_TRANSFER',
                        'PENSION_CONTRIBUTION','PENSION_ADJUSTMENT','PRICE_UPDATE','OTHER')),
    description         TEXT    NOT NULL,
    amount              REAL    NOT NULL,
    currency            TEXT    NOT NULL
        CHECK (currency IN ('EUR','USD','GBP','CHF','JPY','AUD','CAD')),
    related_asset_external_id   TEXT,    -- soft FK (client-minted id)
    related_account_external_id TEXT,
    note                TEXT,
    UNIQUE (user_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_financial_ops_month_date
    ON financial_operations (net_worth_month_id, date);
CREATE INDEX IF NOT EXISTS idx_financial_ops_user_date
    ON financial_operations (user_id, date DESC);

-- -----------------------------------------------------------------------------
-- 7. Questionnaire results (FIRE persona)
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS questionnaire_results (
    id                          INTEGER PRIMARY KEY,
    user_id                     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    persona                     TEXT    NOT NULL
        CHECK (persona IN ('LEAN_FIRE','REGULAR_FIRE','FAT_FIRE','COAST_FIRE','BARISTA_FIRE')),
    persona_explanation         TEXT    NOT NULL,
    safe_withdrawal_rate        REAL    NOT NULL,
    suggested_savings_rate      REAL    NOT NULL,
    risk_tolerance              TEXT    NOT NULL
        CHECK (risk_tolerance IN ('conservative','moderate','aggressive')),
    asset_allocation_stocks     REAL    NOT NULL,
    asset_allocation_bonds      REAL    NOT NULL,
    asset_allocation_cash       REAL    NOT NULL,
    asset_allocation_crypto     REAL,
    asset_allocation_real_estate REAL,
    suitable_assets_json        TEXT    NOT NULL,  -- JSON string[]
    responses_json              TEXT    NOT NULL,  -- JSON QuestionnaireResponse[]
    completed_at                TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_questionnaire_user_time
    ON questionnaire_results (user_id, completed_at DESC);

-- -----------------------------------------------------------------------------
-- 8. PDF import drafts (experimental)
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS pdf_imports (
    id              INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    source_file     TEXT    NOT NULL,
    doc_type        TEXT    NOT NULL
        CHECK (doc_type IN ('auto','receipt','invoice','bank_statement','payslip')),
    drafts_json     TEXT    NOT NULL,  -- JSON ParsedTransactionDraft[]
    status          TEXT    NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending','reviewed','committed','discarded')),
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    committed_at    TEXT
);
CREATE INDEX IF NOT EXISTS idx_pdf_imports_user_status
    ON pdf_imports (user_id, status, created_at DESC);

-- -----------------------------------------------------------------------------
-- 9. Portfolio breakdown metadata cache (experimental)
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS portfolio_metadata_cache (
    id                  INTEGER PRIMARY KEY,
    user_id             INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ticker              TEXT    NOT NULL,
    quote_type          TEXT,
    long_name           TEXT,
    short_name          TEXT,
    currency            TEXT,
    exchange            TEXT,
    sector              TEXT,
    industry            TEXT,
    country             TEXT,
    fund_family         TEXT,
    category            TEXT,
    sector_weightings_json TEXT,    -- JSON SectorWeight[]
    region_weightings_json TEXT,    -- JSON RegionWeight[]
    error               TEXT,
    fetched_at          TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, ticker)
);

-- -----------------------------------------------------------------------------
-- 10. Banks lookup (read-only seed; mutable for deployer extensions)
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS banks (
    code                  TEXT    PRIMARY KEY,
    name                  TEXT    NOT NULL,
    country_code          TEXT    NOT NULL,        -- ISO 3166-1 alpha-2
    supports_open_banking INTEGER NOT NULL DEFAULT 0 CHECK (supports_open_banking IN (0,1)),
    bic                   TEXT,
    institution_type      TEXT
        CHECK (institution_type IS NULL OR institution_type IN
            ('BANK','BROKER','NEOBANK','CREDIT_UNION','BUILDING_SOCIETY')),
    logo_url              TEXT
);
CREATE INDEX IF NOT EXISTS idx_banks_country ON banks (country_code);

-- -----------------------------------------------------------------------------
-- 11. UI Preferences (generic per-user KV store)
-- -----------------------------------------------------------------------------
-- Replaces the encrypted-cookie store used by the pure-web build for things
-- like tour completion, banner dismissals, and prompt suppressions.
-- Values are opaque strings (typically JSON-encoded by the client).

CREATE TABLE IF NOT EXISTS ui_preferences (
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key        TEXT    NOT NULL,
    value      TEXT    NOT NULL,
    updated_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, key)
);
CREATE INDEX IF NOT EXISTS idx_ui_preferences_user ON ui_preferences (user_id);

-- -----------------------------------------------------------------------------
-- 12. Audit log (privacy-first record of user-meaningful actions)
-- -----------------------------------------------------------------------------
-- Mirrors the encrypted client-side audit log. Payloads carry only
-- non-sensitive context (ids, counts, field names) — never raw financial data.

CREATE TABLE IF NOT EXISTS audit_log (
    id           TEXT    PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    action_type  TEXT    NOT NULL
        CHECK (action_type IN
            ('CREATE_ASSET','UPDATE_ASSET','DELETE_ASSET','RUN_CALCULATION',
             'UPDATE_SETTINGS','IMPORT_DATA','EXPORT_DATA','CLEAR_DATA')),
    payload_json TEXT,
    session_id   TEXT,
    created_at   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_audit_log_user ON audit_log (user_id, created_at);

-- =============================================================================
-- End of schema
-- =============================================================================