PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS schema_migrations (
  version TEXT PRIMARY KEY,
  applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('checking','savings','credit','investment','loan')),
  institution TEXT NOT NULL,
  is_retirement INTEGER NOT NULL DEFAULT 0 CHECK (is_retirement IN (0,1)),
  currency TEXT NOT NULL CHECK (currency = 'USD'),
  simplefin_id TEXT UNIQUE
);

CREATE TABLE balance_snapshots (
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  snapshot_date TEXT NOT NULL,
  balance_cents INTEGER NOT NULL,
  UNIQUE(account_id, snapshot_date)
);

CREATE TABLE categories (
  name TEXT PRIMARY KEY
);

CREATE TABLE category_rules (
  id INTEGER PRIMARY KEY,
  priority INTEGER NOT NULL,
  pattern TEXT NOT NULL,
  field TEXT NOT NULL CHECK (field IN ('description','merchant')),
  category TEXT NOT NULL REFERENCES categories(name),
  UNIQUE(priority, pattern, field)
);

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  posted_date TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  description TEXT NOT NULL,
  merchant TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT 'Uncategorized' REFERENCES categories(name),
  is_transfer INTEGER NOT NULL DEFAULT 0 CHECK (is_transfer IN (0,1)),
  transfer_override INTEGER CHECK (transfer_override IN (0,1) OR transfer_override IS NULL),
  pending INTEGER NOT NULL DEFAULT 0 CHECK (pending IN (0,1)),
  source TEXT NOT NULL CHECK (source IN ('seed','csv','simplefin')),
  external_id TEXT,
  dedupe_hash TEXT NOT NULL
);
CREATE UNIQUE INDEX transactions_external_id
  ON transactions(source, account_id, external_id) WHERE external_id IS NOT NULL;
CREATE UNIQUE INDEX transactions_dedupe
  ON transactions(account_id, dedupe_hash) WHERE external_id IS NULL;
CREATE INDEX transactions_date ON transactions(posted_date);
CREATE INDEX transactions_category ON transactions(category);

CREATE TABLE budgets (
  category TEXT PRIMARY KEY REFERENCES categories(name),
  monthly_target_cents INTEGER NOT NULL CHECK (monthly_target_cents >= 0)
);

CREATE TABLE holdings (
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  symbol TEXT NOT NULL,
  quantity REAL NOT NULL,
  cost_basis_cents INTEGER NOT NULL,
  latest_price_cents INTEGER NOT NULL,
  as_of_date TEXT NOT NULL,
  UNIQUE(account_id, symbol)
);

CREATE TABLE settings (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  birth_year INTEGER NOT NULL,
  retirement_age INTEGER NOT NULL,
  terminal_age INTEGER NOT NULL DEFAULT 95,
  annual_salary_cents INTEGER NOT NULL,
  contribution_percent REAL NOT NULL,
  employer_match_percent REAL NOT NULL,
  employer_match_cap_percent REAL NOT NULL,
  annual_retirement_spending_cents INTEGER NOT NULL,
  tax_rate_percent REAL NOT NULL,
  nominal_return_percent REAL NOT NULL DEFAULT 7,
  return_stdev_percent REAL NOT NULL DEFAULT 15,
  inflation_percent REAL NOT NULL DEFAULT 2.5
);

CREATE TABLE csv_mapping_profiles (
  name TEXT PRIMARY KEY,
  mapping_json TEXT NOT NULL
);