CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) NOT NULL PRIMARY KEY,
  `value` TEXT NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(100) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  last_seen_at DATETIME NULL,
  revoked_at DATETIME NULL,
  UNIQUE KEY uniq_token_hash (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

-- Optional seed defaults
INSERT INTO settings (`key`, `value`, updated_at) VALUES
  ('indicator_config', '{"short_ma":10,"long_ma":30,"rsi_period":14,"rsi_overbought":70,"rsi_oversold":30,"momentum_period":10,"slope_period":3,"recommendations_enabled":true}', NOW()),
  ('app_auth_fallback_enabled', '0', NOW())
ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at);