

CREATE TABLE IF NOT EXISTS refresh_runs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  message_sanitized VARCHAR(500) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS account_snapshots_hourly (
  ts DATETIME NOT NULL PRIMARY KEY,
  total_value DECIMAL(20,4) NULL,
  cash_available_to_trade DECIMAL(20,4) NULL,
  cash_reserved DECIMAL(20,4) NULL,
  investments_current_value DECIMAL(20,4) NULL,
  investments_total_cost DECIMAL(20,4) NULL,
  investments_unrealized_pnl DECIMAL(20,4) NULL,
  investments_realized_pnl DECIMAL(20,4) NULL,
  fx_impact DECIMAL(20,4) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS position_snapshots_hourly (
  ts DATETIME NOT NULL,
  ticker VARCHAR(32) NOT NULL,
  instrument_id VARCHAR(64) NULL,
  name VARCHAR(160) NULL,
  currency VARCHAR(8) NULL,
  quantity DECIMAL(20,6) NULL,
  current_price DECIMAL(20,6) NULL,
  value_account_ccy DECIMAL(20,4) NULL,
  total_cost_account_ccy DECIMAL(20,4) NULL,
  unrealized_pnl_account_ccy DECIMAL(20,4) NULL,
  fx_impact_account_ccy DECIMAL(20,4) NULL,
  PRIMARY KEY (ts, ticker),
  KEY idx_ticker_ts (ticker, ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS account_snapshots_daily (
  `date` DATE NOT NULL PRIMARY KEY,
  total_value DECIMAL(20,4) NULL,
  cash_available_to_trade DECIMAL(20,4) NULL,
  cash_reserved DECIMAL(20,4) NULL,
  investments_current_value DECIMAL(20,4) NULL,
  investments_total_cost DECIMAL(20,4) NULL,
  investments_unrealized_pnl DECIMAL(20,4) NULL,
  investments_realized_pnl DECIMAL(20,4) NULL,
  fx_impact DECIMAL(20,4) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS position_snapshots_daily (
  `date` DATE NOT NULL,
  ticker VARCHAR(32) NOT NULL,
  instrument_id VARCHAR(64) NULL,
  name VARCHAR(160) NULL,
  currency VARCHAR(8) NULL,
  quantity DECIMAL(20,6) NULL,
  current_price DECIMAL(20,6) NULL,
  value_account_ccy DECIMAL(20,4) NULL,
  total_cost_account_ccy DECIMAL(20,4) NULL,
  unrealized_pnl_account_ccy DECIMAL(20,4) NULL,
  fx_impact_account_ccy DECIMAL(20,4) NULL,
  PRIMARY KEY (`date`, ticker),
  KEY idx_ticker_date (ticker, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS derived_portfolio_latest (
  ts_latest DATETIME NOT NULL PRIMARY KEY,
  total_value DECIMAL(20,4) NULL,
  chg1d DECIMAL(12,4) NULL,
  chg1w DECIMAL(12,4) NULL,
  chg1m DECIMAL(12,4) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS derived_holding_latest (
  ticker VARCHAR(32) NOT NULL PRIMARY KEY,
  ts_latest DATETIME NOT NULL,
  value_account_ccy DECIMAL(20,4) NULL,
  total_cost_account_ccy DECIMAL(20,4) NULL,
  unrealized_pnl_account_ccy DECIMAL(20,4) NULL,
  chg1d DECIMAL(12,4) NULL,
  chg1w DECIMAL(12,4) NULL,
  chg1m DECIMAL(12,4) NULL,
  `signal` VARCHAR(16) NOT NULL DEFAULT 'Neutral',
  recommendation VARCHAR(32) NOT NULL DEFAULT 'Consider: Hold',
  explanation_json TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS instruments (
  instrument_id VARCHAR(64) NOT NULL PRIMARY KEY,
  ticker VARCHAR(32) NULL,
  name VARCHAR(160) NULL,
  isin VARCHAR(32) NULL,
  currency VARCHAR(8) NULL,
  type VARCHAR(40) NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Support instrument lookups by ISIN when instrument IDs are not present
CREATE INDEX idx_instruments_isin ON instruments (isin);

CREATE TABLE IF NOT EXISTS alerts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(40) NOT NULL,
  scope VARCHAR(20) NOT NULL,
  ticker VARCHAR(32) NULL,
  timeframe VARCHAR(10) NULL,
  comparator VARCHAR(4) NOT NULL,
  threshold DECIMAL(20,4) NOT NULL,
  cooldown_minutes INT NOT NULL DEFAULT 60,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_triggered_at DATETIME NULL,
  email_to VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS alert_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  alert_id INT NOT NULL,
  triggered_at DATETIME NOT NULL,
  payload_json TEXT NOT NULL,
  KEY idx_alert_id (alert_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(40) NOT NULL,
  message VARCHAR(500) NOT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL,
  meta_json TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  ip_address VARCHAR(45) NOT NULL PRIMARY KEY,
  attempts INT NOT NULL DEFAULT 0,
  last_attempt_at DATETIME NOT NULL,
  lock_until DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
