/**
 * SQLite access via better-sqlite3.
 *
 * The DB file lives at data/wonderkids.db (or DATABASE_PATH). It is created
 * and migrated automatically on first access, so a redeploy that keeps the
 * data/ directory never loses user data, and a fresh clone just works.
 *
 * Server-only.
 */
import Database from 'better-sqlite3';
import fs from 'node:fs';
import path from 'node:path';
import { env } from './env';

let _db: Database.Database | null = null;

function resolveDbPath(): string {
  if (env.databasePath) return env.databasePath;
  return path.join(process.cwd(), 'data', 'wonderkids.db');
}

function migrate(db: Database.Database) {
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');

  db.exec(`
    CREATE TABLE IF NOT EXISTS subscriptions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      subscriber_id TEXT NOT NULL UNIQUE,
      status TEXT NOT NULL DEFAULT 'UNREGISTERED',
      subscribed_at DATETIME,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS otp_sessions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      phone TEXT NOT NULL,
      reference_no TEXT NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS progress (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      subscriber_id TEXT NOT NULL,
      subject_id TEXT NOT NULL,
      lesson_id TEXT NOT NULL,
      stars INTEGER DEFAULT 0,
      duration_seconds INTEGER DEFAULT 0,
      completed_at DATETIME
    );

    CREATE TABLE IF NOT EXISTS settings (
      subscriber_id TEXT PRIMARY KEY,
      parent_pin_hash TEXT,
      daily_limit_minutes INTEGER,
      enabled_subjects TEXT DEFAULT 'all',
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_progress_subscriber ON progress (subscriber_id);
    CREATE INDEX IF NOT EXISTS idx_progress_subject ON progress (subscriber_id, subject_id);
    CREATE INDEX IF NOT EXISTS idx_otp_reference ON otp_sessions (reference_no);

    -- coins / reward wallet (one row per subscriber)
    CREATE TABLE IF NOT EXISTS coins (
      subscriber_id TEXT PRIMARY KEY,
      balance INTEGER NOT NULL DEFAULT 0,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    -- unlocked items: badges, mascot skins, avatars, bonus levels
    CREATE TABLE IF NOT EXISTS unlocks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      subscriber_id TEXT NOT NULL,
      item_type TEXT NOT NULL,
      item_id TEXT NOT NULL,
      unlocked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE (subscriber_id, item_type, item_id)
    );

    -- per-day AI usage counter (for cost/abuse caps)
    CREATE TABLE IF NOT EXISTS ai_usage (
      subscriber_id TEXT NOT NULL,
      day TEXT NOT NULL,
      feature TEXT NOT NULL,
      count INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (subscriber_id, day, feature)
    );

    -- AI chat/interaction log for parent review + safety audit
    CREATE TABLE IF NOT EXISTS ai_chat_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      subscriber_id TEXT NOT NULL,
      feature TEXT NOT NULL,
      role TEXT NOT NULL,
      content TEXT NOT NULL,
      flagged INTEGER NOT NULL DEFAULT 0,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    -- saved AI-generated stories
    CREATE TABLE IF NOT EXISTS ai_stories (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      subscriber_id TEXT NOT NULL,
      title TEXT NOT NULL,
      body TEXT NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    -- rate limiting buckets (SQLite-backed token bucket)
    CREATE TABLE IF NOT EXISTS rate_limits (
      bucket TEXT PRIMARY KEY,
      tokens REAL NOT NULL,
      updated_at REAL NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_chatlog_sub ON ai_chat_log (subscriber_id, created_at);
    CREATE INDEX IF NOT EXISTS idx_stories_sub ON ai_stories (subscriber_id, created_at);
  `);

  // Additive column migrations — guarded so re-runs and older DBs upgrade safely.
  addColumn(db, 'subscriptions', 'phone', 'TEXT');
  db.exec('CREATE INDEX IF NOT EXISTS idx_subscriptions_phone ON subscriptions (phone)');
  addColumn(db, 'settings', 'age_band', "TEXT");
  addColumn(db, 'settings', 'ai_enabled', 'INTEGER DEFAULT 0');
  addColumn(db, 'settings', 'ai_daily_limit', 'INTEGER DEFAULT 20');
  addColumn(db, 'settings', 'ai_features', "TEXT DEFAULT 'all'");
  addColumn(db, 'settings', 'voice_enabled', 'INTEGER DEFAULT 1');
  addColumn(db, 'settings', 'mascot_skin', "TEXT DEFAULT 'classic'");
  addColumn(db, 'progress', 'level_id', 'TEXT');
  addColumn(db, 'progress', 'correct_count', 'INTEGER DEFAULT 0');
  addColumn(db, 'progress', 'total_count', 'INTEGER DEFAULT 0');
}

/** Add a column only if it doesn't already exist (safe on every boot). */
function addColumn(db: Database.Database, table: string, column: string, decl: string) {
  const cols = db.prepare(`PRAGMA table_info(${table})`).all() as { name: string }[];
  if (!cols.some((c) => c.name === column)) {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${decl}`);
  }
}

export function getDb(): Database.Database {
  if (_db) return _db;

  const dbPath = resolveDbPath();
  const dir = path.dirname(dbPath);
  if (!fs.existsSync(dir)) {
    fs.mkdirSync(dir, { recursive: true });
  }

  const db = new Database(dbPath);
  migrate(db);
  _db = db;
  return db;
}