const { createLogger } = require('./logger')
const log = createLogger('auth')

/**
 * Auth Module — JWT + SQLite + Google OAuth ready
 *
 * Users table: id, email, password_hash, name, tier, provider, provider_id, created_at
 * Tiers: free, pro
 * Providers: local, google
 */

const Database = require('better-sqlite3')
const bcrypt = require('bcryptjs')
const jwt = require('jsonwebtoken')
const path = require('path')
const crypto = require('crypto')

// --- Config ---
const DB_PATH = path.join(__dirname, 'data', 'users.db')
if (!process.env.JWT_SECRET) {
  log.fatal('CRITICAL: JWT_SECRET not set in env! Tokens will reset on every restart.')
  log.fatal('Set JWT_SECRET in PM2 ecosystem.config.js or .env file.')
  if (process.env.NODE_ENV === 'production') {
    log.fatal('Refusing to start in production without JWT_SECRET.')
    process.exit(1)
  }
}
const JWT_SECRET = process.env.JWT_SECRET || crypto.randomBytes(32).toString('hex')
const JWT_EXPIRES_IN = process.env.JWT_EXPIRES_IN || '30d'
const GOOGLE_CLIENT_ID = process.env.GOOGLE_CLIENT_ID || ''
const GOOGLE_CLIENT_SECRET = process.env.GOOGLE_CLIENT_SECRET || ''
const GOOGLE_REDIRECT_URI = process.env.GOOGLE_REDIRECT_URI || ''

// --- Database Setup ---
const db = new Database(DB_PATH)
db.pragma('journal_mode = WAL')
db.pragma('foreign_keys = ON')

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT,
    name TEXT DEFAULT '',
    tier TEXT DEFAULT 'free' CHECK(tier IN ('free', 'pro', 'admin')),
    provider TEXT DEFAULT 'local' CHECK(provider IN ('local', 'google')),
    provider_id TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
  );
  CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
  CREATE INDEX IF NOT EXISTS idx_users_provider ON users(provider, provider_id);

  -- User settings (JSON blob per user, server-side sync for PRO)
  CREATE TABLE IF NOT EXISTS user_settings (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    settings TEXT DEFAULT '{}',
    updated_at TEXT DEFAULT (datetime('now'))
  );

  -- Watchlists (server-side for PRO, replaces localStorage)
  CREATE TABLE IF NOT EXISTS watchlists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    symbol TEXT NOT NULL,
    color TEXT DEFAULT '',
    sort_order INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    UNIQUE(user_id, symbol)
  );
  CREATE INDEX IF NOT EXISTS idx_watchlists_user ON watchlists(user_id);

  -- Saved chart layouts (multi-chart configs)
  CREATE TABLE IF NOT EXISTS saved_layouts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name TEXT DEFAULT 'Default',
    layout_type TEXT DEFAULT '1',
    config TEXT DEFAULT '{}',
    is_active INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
  );
  CREATE INDEX IF NOT EXISTS idx_layouts_user ON saved_layouts(user_id);

  -- Price / density / impulse alert rules
  CREATE TABLE IF NOT EXISTS alerts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type TEXT NOT NULL CHECK(type IN ('price', 'density', 'impulse', 'listing', 'volume_spike', 'multi')),
    symbol TEXT,
    condition TEXT DEFAULT '{}',
    enabled INTEGER DEFAULT 1,
    cooldown_sec INTEGER DEFAULT 300,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
  );
  CREATE INDEX IF NOT EXISTS idx_alerts_user ON alerts(user_id);
  CREATE INDEX IF NOT EXISTS idx_alerts_enabled ON alerts(enabled);

  -- Alert trigger log (capped per user)
  CREATE TABLE IF NOT EXISTS alert_triggers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    alert_id INTEGER NOT NULL REFERENCES alerts(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL,
    symbol TEXT,
    message TEXT,
    data TEXT DEFAULT '{}',
    triggered_at TEXT DEFAULT (datetime('now'))
  );
  CREATE INDEX IF NOT EXISTS idx_alert_triggers_user ON alert_triggers(user_id);

  -- Signal log for backtest (density breakouts, impulses, etc.)
  CREATE TABLE IF NOT EXISTS signal_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,
    symbol TEXT NOT NULL,
    direction TEXT,
    entry_price REAL,
    confidence REAL,
    metadata TEXT DEFAULT '{}',
    spot_after_5m REAL,
    spot_after_15m REAL,
    spot_after_1h REAL,
    spot_after_4h REAL,
    outcome TEXT,
    pnl_pct REAL,
    created_at TEXT DEFAULT (datetime('now'))
  );
  CREATE INDEX IF NOT EXISTS idx_signal_log_type ON signal_log(type);
  CREATE INDEX IF NOT EXISTS idx_signal_log_symbol ON signal_log(symbol);
  CREATE INDEX IF NOT EXISTS idx_signal_log_created ON signal_log(created_at);
  CREATE INDEX IF NOT EXISTS idx_signal_log_dedup ON signal_log(type, symbol, created_at);

  -- Push notification subscriptions (Web Push API)
  CREATE TABLE IF NOT EXISTS push_subscriptions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    endpoint TEXT UNIQUE NOT NULL,
    keys_p256dh TEXT NOT NULL,
    keys_auth TEXT NOT NULL,
    filters TEXT DEFAULT '{}',
    created_at TEXT DEFAULT (datetime('now')),
    last_success TEXT,
    fail_count INTEGER DEFAULT 0
  );
  CREATE INDEX IF NOT EXISTS idx_push_endpoint ON push_subscriptions(endpoint);
`)

// Migration: add user_id to push_subscriptions for per-user alert push
try {
  db.exec('ALTER TABLE push_subscriptions ADD COLUMN user_id INTEGER')
} catch(e) { /* column exists */ }
try {
  db.exec('CREATE INDEX IF NOT EXISTS idx_push_user ON push_subscriptions(user_id)')
} catch(e) { /* index exists */ }

// Migration: add new columns if missing (mfe_pct, mae_pct, spot_after_1d)
try {
  db.exec(`ALTER TABLE signal_log ADD COLUMN spot_after_1d REAL`)
} catch(e) { /* column exists */ }
try {
  db.exec(`ALTER TABLE signal_log ADD COLUMN mfe_pct REAL`)
} catch(e) { /* column exists */ }
try {
  db.exec(`ALTER TABLE signal_log ADD COLUMN mae_pct REAL`)
} catch(e) { /* column exists */ }

// Migration: add 'multi' type to alerts CHECK constraint (SQLite can't ALTER CHECK)
// IMPORTANT: foreign_keys OFF to prevent FK reference propagation on RENAME
try {
  const tableInfo = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='alerts'").get()
  if (tableInfo && tableInfo.sql && !tableInfo.sql.includes("'multi'")) {
    db.pragma('foreign_keys = OFF')
    db.transaction(() => {
      db.exec(`ALTER TABLE alerts RENAME TO _alerts_old`)
      db.exec(`CREATE TABLE alerts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        type TEXT NOT NULL CHECK(type IN ('price', 'density', 'impulse', 'listing', 'volume_spike', 'multi')),
        symbol TEXT, condition TEXT DEFAULT '{}', enabled INTEGER DEFAULT 1,
        cooldown_sec INTEGER DEFAULT 300,
        created_at TEXT DEFAULT (datetime('now')), updated_at TEXT DEFAULT (datetime('now'))
      )`)
      db.exec(`INSERT INTO alerts SELECT * FROM _alerts_old`)
      db.exec(`DROP TABLE _alerts_old`)
      db.exec(`CREATE INDEX IF NOT EXISTS idx_alerts_user ON alerts(user_id)`)
      db.exec(`CREATE INDEX IF NOT EXISTS idx_alerts_enabled ON alerts(enabled)`)
    })()
    db.pragma('foreign_keys = ON')
    log.info('Migration: alerts table recreated with multi type support')
  }
} catch(e) {
  try { db.pragma('foreign_keys = ON') } catch(_) {}
  log.warn({ err: e.message }, 'Migration: alerts multi type check')
}

// Migration: repair alert_triggers FK if it references _alerts_old (broken by prior migration)
try {
  const trigInfo = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='alert_triggers'").get()
  if (trigInfo && trigInfo.sql && trigInfo.sql.includes('_alerts_old')) {
    db.pragma('foreign_keys = OFF')
    db.transaction(() => {
      db.exec(`CREATE TABLE _alert_triggers_fix (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        alert_id INTEGER NOT NULL REFERENCES alerts(id) ON DELETE CASCADE,
        user_id INTEGER NOT NULL, symbol TEXT, message TEXT,
        data TEXT DEFAULT '{}', triggered_at TEXT DEFAULT (datetime('now'))
      )`)
      db.exec(`INSERT INTO _alert_triggers_fix SELECT * FROM alert_triggers`)
      db.exec(`DROP TABLE alert_triggers`)
      db.exec(`ALTER TABLE _alert_triggers_fix RENAME TO alert_triggers`)
      db.exec(`CREATE INDEX IF NOT EXISTS idx_alert_triggers_user ON alert_triggers(user_id)`)
    })()
    db.pragma('foreign_keys = ON')
    log.info('Migration: alert_triggers FK repaired (was referencing _alerts_old)')
  }
} catch(e) {
  try { db.pragma('foreign_keys = ON') } catch(_) {}
  log.warn({ err: e.message }, 'Migration: alert_triggers FK repair')
}

// --- Prepared Statements ---
const stmts = {
  findByEmail: db.prepare('SELECT * FROM users WHERE email = ?'),
  findById: db.prepare('SELECT id, email, name, tier, provider, created_at FROM users WHERE id = ?'),
  findByProvider: db.prepare('SELECT * FROM users WHERE provider = ? AND provider_id = ?'),
  create: db.prepare(`
    INSERT INTO users (email, password_hash, name, tier, provider, provider_id)
    VALUES (@email, @password_hash, @name, @tier, @provider, @provider_id)
  `),
  updateTier: db.prepare('UPDATE users SET tier = ?, updated_at = datetime(\'now\') WHERE id = ?'),
  updateName: db.prepare('UPDATE users SET name = ?, updated_at = datetime(\'now\') WHERE id = ?'),
  countUsers: db.prepare('SELECT COUNT(*) as count FROM users'),
  allUsers: db.prepare('SELECT id, email, name, tier, provider, created_at FROM users ORDER BY created_at DESC'),

  // Settings
  getSettings: db.prepare('SELECT settings FROM user_settings WHERE user_id = ?'),
  upsertSettings: db.prepare(`
    INSERT INTO user_settings (user_id, settings, updated_at) VALUES (?, ?, datetime('now'))
    ON CONFLICT(user_id) DO UPDATE SET settings = excluded.settings, updated_at = datetime('now')
  `),

  // Watchlists
  getWatchlist: db.prepare('SELECT * FROM watchlists WHERE user_id = ? ORDER BY sort_order'),
  addWatchlistItem: db.prepare('INSERT OR IGNORE INTO watchlists (user_id, symbol, color, sort_order) VALUES (?, ?, ?, ?)'),
  removeWatchlistItem: db.prepare('DELETE FROM watchlists WHERE user_id = ? AND symbol = ?'),
  updateWatchlistColor: db.prepare('UPDATE watchlists SET color = ? WHERE user_id = ? AND symbol = ?'),
  clearWatchlist: db.prepare('DELETE FROM watchlists WHERE user_id = ?'),

  // Layouts
  getLayouts: db.prepare('SELECT * FROM saved_layouts WHERE user_id = ? ORDER BY created_at'),
  getActiveLayout: db.prepare('SELECT * FROM saved_layouts WHERE user_id = ? AND is_active = 1'),
  createLayout: db.prepare('INSERT INTO saved_layouts (user_id, name, layout_type, config) VALUES (?, ?, ?, ?)'),
  updateLayout: db.prepare('UPDATE saved_layouts SET config = ?, layout_type = ?, updated_at = datetime(\'now\') WHERE id = ? AND user_id = ?'),
  setActiveLayout: db.prepare('UPDATE saved_layouts SET is_active = CASE WHEN id = ? THEN 1 ELSE 0 END WHERE user_id = ?'),
  deleteLayout: db.prepare('DELETE FROM saved_layouts WHERE id = ? AND user_id = ?'),

  // Alerts
  getAlerts: db.prepare('SELECT * FROM alerts WHERE user_id = ? ORDER BY created_at DESC'),
  getEnabledAlerts: db.prepare('SELECT a.*, u.tier FROM alerts a JOIN users u ON a.user_id = u.id WHERE a.enabled = 1'),
  createAlert: db.prepare('INSERT INTO alerts (user_id, type, symbol, condition, cooldown_sec) VALUES (?, ?, ?, ?, ?)'),
  updateAlert: db.prepare('UPDATE alerts SET condition = ?, enabled = ?, cooldown_sec = ?, updated_at = datetime(\'now\') WHERE id = ? AND user_id = ?'),
  deleteAlert: db.prepare('DELETE FROM alerts WHERE id = ? AND user_id = ?'),
  toggleAlert: db.prepare('UPDATE alerts SET enabled = ?, updated_at = datetime(\'now\') WHERE id = ? AND user_id = ?'),
  countUserAlerts: db.prepare('SELECT COUNT(*) as count FROM alerts WHERE user_id = ?'),

  // Alert Triggers
  logTrigger: db.prepare('INSERT INTO alert_triggers (alert_id, user_id, symbol, message, data) VALUES (?, ?, ?, ?, ?)'),
  getTriggers: db.prepare('SELECT * FROM alert_triggers WHERE user_id = ? ORDER BY triggered_at DESC LIMIT ?'),
  cleanupTriggers: db.prepare(`
    DELETE FROM alert_triggers WHERE user_id = ? AND id NOT IN (
      SELECT id FROM alert_triggers WHERE user_id = ? ORDER BY triggered_at DESC LIMIT 1000
    )
  `),

  // Signal Log
  logSignal: db.prepare('INSERT INTO signal_log (type, symbol, direction, entry_price, confidence, metadata, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)'),
  updateSignalOutcome: db.prepare(`
    UPDATE signal_log SET spot_after_5m = ?, spot_after_15m = ?, spot_after_1h = ?, spot_after_4h = ?, spot_after_1d = ?,
    outcome = ?, pnl_pct = ?, mfe_pct = ?, mae_pct = ? WHERE id = ?
  `),
  getPendingSignals: db.prepare("SELECT * FROM signal_log WHERE outcome IS NULL AND created_at > datetime('now', '-25 hours')"),
  getSignalStats: db.prepare(`
    SELECT type, COUNT(*) as total,
      SUM(CASE WHEN outcome = 'WIN' THEN 1 ELSE 0 END) as wins,
      AVG(pnl_pct) as avg_pnl
    FROM signal_log WHERE outcome IS NOT NULL GROUP BY type
  `),
  getRecentSignals: db.prepare('SELECT * FROM signal_log ORDER BY created_at DESC LIMIT ?'),
  getSignalsSince: db.prepare("SELECT * FROM signal_log WHERE created_at > datetime('now', '-' || ? || ' hours') ORDER BY created_at DESC"),
  cleanupSignals: db.prepare("DELETE FROM signal_log WHERE created_at < datetime('now', '-30 days')"),

  // Push subscriptions
  upsertPushSub: db.prepare(`
    INSERT INTO push_subscriptions (endpoint, keys_p256dh, keys_auth, filters)
    VALUES (?, ?, ?, ?)
    ON CONFLICT(endpoint) DO UPDATE SET
      keys_p256dh = excluded.keys_p256dh,
      keys_auth = excluded.keys_auth,
      filters = excluded.filters,
      fail_count = 0
  `),
  deletePushSub: db.prepare('DELETE FROM push_subscriptions WHERE endpoint = ?'),
  getAllPushSubs: db.prepare('SELECT * FROM push_subscriptions WHERE fail_count < 3'),
  countPushSubs: db.prepare('SELECT COUNT(*) as count FROM push_subscriptions'),
  incrementPushFail: db.prepare('UPDATE push_subscriptions SET fail_count = fail_count + 1 WHERE endpoint = ?'),
  resetPushFail: db.prepare(`UPDATE push_subscriptions SET fail_count = 0, last_success = datetime('now') WHERE endpoint = ?`),
  getPushSubsByUser: db.prepare('SELECT * FROM push_subscriptions WHERE user_id = ? AND fail_count < 3'),
  linkPushSubToUser: db.prepare('UPDATE push_subscriptions SET user_id = ? WHERE endpoint = ?'),
}

// --- Helpers ---
function hashPassword(password) {
  return bcrypt.hashSync(password, 10)
}

function verifyPassword(password, hash) {
  return bcrypt.compareSync(password, hash)
}

function generateToken(user) {
  return jwt.sign(
    { id: user.id, email: user.email, tier: user.tier },
    JWT_SECRET,
    { expiresIn: JWT_EXPIRES_IN }
  )
}

function verifyToken(token) {
  try {
    return jwt.verify(token, JWT_SECRET)
  } catch (e) {
    return null
  }
}

// --- Auth Functions ---

/**
 * Register with email + password
 */
function register(email, password, name = '') {
  if (!email || !password) {
    return { error: 'Email and password required' }
  }
  if (password.length < 6) {
    return { error: 'Password must be at least 6 characters' }
  }

  const existing = stmts.findByEmail.get(email.toLowerCase())
  if (existing) {
    return { error: 'Email already registered' }
  }

  const result = stmts.create.run({
    email: email.toLowerCase(),
    password_hash: hashPassword(password),
    name: name || email.split('@')[0],
    tier: 'free',
    provider: 'local',
    provider_id: null,
  })

  const user = stmts.findById.get(result.lastInsertRowid)
  const token = generateToken(user)

  return { success: true, user, token }
}

/**
 * Login with email + password
 */
function login(email, password) {
  if (!email || !password) {
    return { error: 'Email and password required' }
  }

  const user = stmts.findByEmail.get(email.toLowerCase())
  if (!user) {
    return { error: 'Invalid email or password' }
  }

  if (user.provider !== 'local' || !user.password_hash) {
    return { error: `This account uses ${user.provider} login` }
  }

  if (!verifyPassword(password, user.password_hash)) {
    return { error: 'Invalid email or password' }
  }

  const token = generateToken({
    id: user.id,
    email: user.email,
    tier: user.tier,
  })

  const safeUser = stmts.findById.get(user.id)
  return { success: true, user: safeUser, token }
}

/**
 * Google OAuth: exchange code for user info, create/login user
 */
async function googleAuth(code) {
  if (!GOOGLE_CLIENT_ID || !GOOGLE_CLIENT_SECRET) {
    return { error: 'Google OAuth not configured' }
  }

  try {
    // Exchange code for tokens
    const tokenRes = await fetch('https://oauth2.googleapis.com/token', {
      method: 'POST',
      headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
      body: new URLSearchParams({
        code,
        client_id: GOOGLE_CLIENT_ID,
        client_secret: GOOGLE_CLIENT_SECRET,
        redirect_uri: GOOGLE_REDIRECT_URI,
        grant_type: 'authorization_code',
      }),
    })
    const tokens = await tokenRes.json()
    if (!tokens.access_token) {
      return { error: 'Failed to get Google token' }
    }

    // Get user info
    const infoRes = await fetch('https://www.googleapis.com/oauth2/v2/userinfo', {
      headers: { Authorization: `Bearer ${tokens.access_token}` },
    })
    const info = await infoRes.json()
    if (!info.email) {
      return { error: 'Failed to get Google user info' }
    }

    // Find or create user
    let user = stmts.findByProvider.get('google', info.id)
    if (!user) {
      // Check if email already registered with local
      const existing = stmts.findByEmail.get(info.email.toLowerCase())
      if (existing) {
        return { error: 'Email already registered with password login' }
      }

      stmts.create.run({
        email: info.email.toLowerCase(),
        password_hash: null,
        name: info.name || info.email.split('@')[0],
        tier: 'free',
        provider: 'google',
        provider_id: info.id,
      })
      user = stmts.findByEmail.get(info.email.toLowerCase())
    }

    const safeUser = stmts.findById.get(user.id)
    const token = generateToken(safeUser)

    return { success: true, user: safeUser, token }
  } catch (e) {
    return { error: `Google auth failed: ${e.message}` }
  }
}

/**
 * Get Google OAuth URL (redirect user here)
 */
function getGoogleAuthUrl() {
  if (!GOOGLE_CLIENT_ID) return null
  const params = new URLSearchParams({
    client_id: GOOGLE_CLIENT_ID,
    redirect_uri: GOOGLE_REDIRECT_URI,
    response_type: 'code',
    scope: 'email profile',
    access_type: 'offline',
    prompt: 'consent',
  })
  return `https://accounts.google.com/o/oauth2/v2/auth?${params}`
}

// --- Fastify Middleware ---

/**
 * Extract user from JWT (non-blocking — sets req.user or null)
 */
function authHook(req) {
  req.user = null
  const authHeader = req.headers.authorization
  if (!authHeader || !authHeader.startsWith('Bearer ')) return

  const token = authHeader.slice(7)
  const decoded = verifyToken(token)
  if (!decoded) return

  // Refresh user from DB (tier might have changed)
  const user = stmts.findById.get(decoded.id)
  if (user) req.user = user
}

/**
 * Require authenticated user (returns 401 if not)
 */
function requireAuth(req, reply) {
  authHook(req)
  if (!req.user) {
    reply.code(401).send({ error: 'Authentication required' })
    return false
  }
  return true
}

/**
 * Require PRO tier (returns 403 if free)
 */
function requirePro(req, reply) {
  if (!requireAuth(req, reply)) return false
  if (req.user.tier !== 'pro' && req.user.tier !== 'admin') {
    reply.code(403).send({ error: 'PRO subscription required' })
    return false
  }
  return true
}

// --- Admin Functions ---
function setTier(userId, tier) {
  stmts.updateTier.run(tier, userId)
  return stmts.findById.get(userId)
}

function listUsers() {
  return stmts.allUsers.all()
}

function getUserCount() {
  return stmts.countUsers.get().count
}

// --- DB Access Functions (for routes) ---

function getSettings(userId) {
  const row = stmts.getSettings.get(userId)
  return row ? JSON.parse(row.settings) : {}
}

function saveSettings(userId, settings) {
  stmts.upsertSettings.run(userId, JSON.stringify(settings))
}

function getWatchlist(userId) {
  return stmts.getWatchlist.all(userId)
}

function addToWatchlist(userId, symbol, color = '', sortOrder = 0) {
  stmts.addWatchlistItem.run(userId, symbol.toUpperCase(), color, sortOrder)
}

function removeFromWatchlist(userId, symbol) {
  stmts.removeWatchlistItem.run(userId, symbol.toUpperCase())
}

function getLayouts(userId) {
  return stmts.getLayouts.all(userId)
}

function getActiveLayout(userId) {
  return stmts.getActiveLayout.get(userId) || null
}

function createLayout(userId, name, layoutType, config) {
  return stmts.createLayout.run(userId, name, layoutType, JSON.stringify(config))
}

function updateLayout(layoutId, userId, config, layoutType) {
  stmts.updateLayout.run(JSON.stringify(config), layoutType, layoutId, userId)
}

function getUserAlerts(userId) {
  return stmts.getAlerts.all(userId).map(a => ({ ...a, condition: JSON.parse(a.condition || '{}') }))
}

function getAllEnabledAlerts() {
  return stmts.getEnabledAlerts.all().map(a => ({ ...a, condition: JSON.parse(a.condition || '{}') }))
}

function createUserAlert(userId, type, symbol, condition, cooldownSec = 300) {
  return stmts.createAlert.run(userId, type, symbol, JSON.stringify(condition), cooldownSec)
}

function logAlertTrigger(alertId, userId, symbol, message, data = {}) {
  stmts.logTrigger.run(alertId, userId, symbol, message, JSON.stringify(data))
  stmts.cleanupTriggers.run(userId, userId) // cap at 1000
}

function getAlertTriggers(userId, limit = 50) {
  return stmts.getTriggers.all(userId, limit).map(t => ({ ...t, data: JSON.parse(t.data || '{}') }))
}

function logSignal(type, symbol, direction, entryPrice, confidence, metadata = {}, createdAt = null) {
  const ts = createdAt || new Date().toISOString().replace('T', ' ').replace(/\.\d+Z$/, '')
  return stmts.logSignal.run(type, symbol, direction, entryPrice, confidence, JSON.stringify(metadata), ts)
}

function getSignalStats() {
  return stmts.getSignalStats.all()
}

function getRecentSignals(limit = 50) {
  return stmts.getRecentSignals.all(limit).map(s => ({ ...s, metadata: JSON.parse(s.metadata || '{}') }))
}

// --- Exports ---
module.exports = {
  // Auth
  register,
  login,
  googleAuth,
  getGoogleAuthUrl,
  authHook,
  requireAuth,
  requirePro,
  verifyToken,
  setTier,
  listUsers,
  getUserCount,
  GOOGLE_CLIENT_ID,

  // Settings
  getSettings,
  saveSettings,

  // Watchlists
  getWatchlist,
  addToWatchlist,
  removeFromWatchlist,

  // Layouts
  getLayouts,
  getActiveLayout,
  createLayout,
  updateLayout,

  // Alerts
  getUserAlerts,
  getAllEnabledAlerts,
  createUserAlert,
  logAlertTrigger,
  getAlertTriggers,

  // Signals
  logSignal,
  getSignalStats,
  getRecentSignals,

  // DB direct (for advanced use)
  db,
  stmts,
}