PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE COLLATE NOCASE,
  password_hash TEXT NOT NULL,
  is_demo INTEGER NOT NULL DEFAULT 1 CHECK (is_demo IN (0, 1)),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS portfolio_snapshots (
  user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  invested_paise INTEGER NOT NULL CHECK (invested_paise >= 0),
  current_balance_paise INTEGER NOT NULL CHECK (current_balance_paise >= 0),
  profit_paise INTEGER GENERATED ALWAYS AS (current_balance_paise - invested_paise) STORED,
  as_of_date TEXT NOT NULL,
  is_demo INTEGER NOT NULL DEFAULT 1 CHECK (is_demo IN (0, 1)),
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS account_transactions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  stable_key TEXT NOT NULL,
  label TEXT NOT NULL,
  amount_paise INTEGER NOT NULL,
  occurred_on TEXT NOT NULL,
  is_sample INTEGER NOT NULL DEFAULT 1 CHECK (is_sample IN (0, 1)),
  UNIQUE (user_id, stable_key)
);

CREATE TABLE IF NOT EXISTS login_attempts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL,
  ip_address TEXT NOT NULL,
  successful INTEGER NOT NULL DEFAULT 0,
  attempted_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS login_attempts_lookup ON login_attempts (email, ip_address, attempted_at);