/**
 * Data-access helpers on top of the SQLite database.
 * Keeps SQL in one place so API routes stay readable. Server-only.
 */
import { getDb } from './db';

export type SubscriptionStatus = 'REGISTERED' | 'UNREGISTERED';

export interface SubscriptionRow {
  id: number;
  subscriber_id: string;
  status: SubscriptionStatus;
  subscribed_at: string | null;
  updated_at: string;
}

export interface SettingsRow {
  subscriber_id: string;
  parent_pin_hash: string | null;
  daily_limit_minutes: number | null;
  enabled_subjects: string; // JSON array string OR 'all'
  age_band: string | null;
  ai_enabled: number; // 0/1
  ai_daily_limit: number | null;
  ai_features: string; // JSON array OR 'all'
  voice_enabled: number; // 0/1
  mascot_skin: string;
  updated_at: string;
}

export interface ProgressRow {
  id: number;
  subscriber_id: string;
  subject_id: string;
  lesson_id: string;
  stars: number;
  duration_seconds: number;
  completed_at: string | null;
}

/* --------------------------- subscriptions --------------------------- */

export function getSubscription(subscriberId: string): SubscriptionRow | undefined {
  return getDb()
    .prepare('SELECT * FROM subscriptions WHERE subscriber_id = ?')
    .get(subscriberId) as SubscriptionRow | undefined;
}

export function isRegistered(subscriberId: string): boolean {
  const row = getSubscription(subscriberId);
  return row?.status === 'REGISTERED';
}

export function upsertSubscription(
  subscriberId: string,
  status: SubscriptionStatus,
  phone?: string,
) {
  const db = getDb();
  const now = new Date().toISOString();
  const subscribedAt = status === 'REGISTERED' ? now : null;
  db.prepare(
    `INSERT INTO subscriptions (subscriber_id, status, subscribed_at, phone, updated_at)
     VALUES (@subscriber_id, @status, @subscribed_at, @phone, @updated_at)
     ON CONFLICT(subscriber_id) DO UPDATE SET
       status = excluded.status,
       subscribed_at = CASE
         WHEN excluded.status = 'REGISTERED' AND subscriptions.subscribed_at IS NULL
           THEN excluded.subscribed_at
         ELSE subscriptions.subscribed_at
       END,
       -- keep an existing phone if this update didn't carry one (e.g. bdapps webhook)
       phone = COALESCE(excluded.phone, subscriptions.phone),
       updated_at = excluded.updated_at`,
  ).run({
    subscriber_id: subscriberId,
    status,
    subscribed_at: subscribedAt,
    phone: phone ?? null,
    updated_at: now,
  });
}

/**
 * Re-login lookup: bdapps returns a MASKED subscriberId at OTP verify that we
 * store as the row key, so we can't re-derive it from the phone. Instead we
 * match on the phone captured at subscribe time and return the stored id.
 */
export function findRegisteredSubscriberIdByPhone(phone: string): string | null {
  const row = getDb()
    .prepare(
      `SELECT subscriber_id FROM subscriptions
       WHERE phone = ? AND status = 'REGISTERED'
       ORDER BY updated_at DESC LIMIT 1`,
    )
    .get(phone) as { subscriber_id: string } | undefined;
  return row?.subscriber_id ?? null;
}

/* ------------------------------- otp -------------------------------- */

export function saveOtpSession(phone: string, referenceNo: string) {
  getDb()
    .prepare('INSERT INTO otp_sessions (phone, reference_no) VALUES (?, ?)')
    .run(phone, referenceNo);
}

export function findOtpByReference(referenceNo: string):
  | { id: number; phone: string; reference_no: string }
  | undefined {
  return getDb()
    .prepare('SELECT id, phone, reference_no FROM otp_sessions WHERE reference_no = ? ORDER BY id DESC LIMIT 1')
    .get(referenceNo) as { id: number; phone: string; reference_no: string } | undefined;
}

/* ----------------------------- progress ----------------------------- */

export function recordProgress(row: {
  subscriberId: string;
  subjectId: string;
  lessonId: string;
  stars: number;
  durationSeconds: number;
  levelId?: string;
  correctCount?: number;
  totalCount?: number;
}) {
  getDb()
    .prepare(
      `INSERT INTO progress
         (subscriber_id, subject_id, lesson_id, stars, duration_seconds, level_id, correct_count, total_count, completed_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)`,
    )
    .run(
      row.subscriberId,
      row.subjectId,
      row.lessonId,
      row.stars,
      row.durationSeconds,
      row.levelId ?? null,
      row.correctCount ?? 0,
      row.totalCount ?? 0,
    );
}

/** Best stars per completed level_id, for progression + level map. */
export function getLevelCompletion(
  subscriberId: string,
  subjectId: string,
): Map<string, { bestStars: number }> {
  const rows = getDb()
    .prepare(
      `SELECT level_id AS levelId, MAX(stars) AS bestStars
       FROM progress
       WHERE subscriber_id = ? AND subject_id = ? AND level_id IS NOT NULL
       GROUP BY level_id`,
    )
    .all(subscriberId, subjectId) as { levelId: string; bestStars: number }[];
  return new Map(rows.map((r) => [r.levelId, { bestStars: r.bestStars }]));
}

export function getProgressRows(subscriberId: string): ProgressRow[] {
  return getDb()
    .prepare('SELECT * FROM progress WHERE subscriber_id = ? ORDER BY completed_at DESC')
    .all(subscriberId) as ProgressRow[];
}

export interface SubjectProgress {
  subjectId: string;
  lessonsCompleted: number;
  totalStars: number;
  totalSeconds: number;
}

export function getSubjectProgress(subscriberId: string): SubjectProgress[] {
  return getDb()
    .prepare(
      `SELECT subject_id AS subjectId,
              COUNT(DISTINCT lesson_id) AS lessonsCompleted,
              COALESCE(SUM(stars), 0) AS totalStars,
              COALESCE(SUM(duration_seconds), 0) AS totalSeconds
       FROM progress
       WHERE subscriber_id = ?
       GROUP BY subject_id`,
    )
    .all(subscriberId) as SubjectProgress[];
}

/** Total stars across every subject. */
export function getTotalStars(subscriberId: string): number {
  const row = getDb()
    .prepare('SELECT COALESCE(SUM(stars), 0) AS total FROM progress WHERE subscriber_id = ?')
    .get(subscriberId) as { total: number };
  return row.total;
}

/** Seconds spent since local midnight today (used for daily screen-time limit). */
export function getSecondsToday(subscriberId: string): number {
  const row = getDb()
    .prepare(
      `SELECT COALESCE(SUM(duration_seconds), 0) AS total
       FROM progress
       WHERE subscriber_id = ?
         AND date(completed_at, 'localtime') = date('now', 'localtime')`,
    )
    .get(subscriberId) as { total: number };
  return row.total;
}

/** Per-weekday seconds for the current week (Sun..Sat), for the parent chart. */
export function getWeeklyActivity(subscriberId: string): { day: number; seconds: number }[] {
  const rows = getDb()
    .prepare(
      `SELECT CAST(strftime('%w', completed_at, 'localtime') AS INTEGER) AS day,
              COALESCE(SUM(duration_seconds), 0) AS seconds
       FROM progress
       WHERE subscriber_id = ?
         AND completed_at >= datetime('now', '-6 days', 'localtime')
       GROUP BY day`,
    )
    .all(subscriberId) as { day: number; seconds: number }[];
  const map = new Map(rows.map((r) => [r.day, r.seconds]));
  return Array.from({ length: 7 }, (_, day) => ({ day, seconds: map.get(day) ?? 0 }));
}

/** Current day-streak: consecutive days (ending today or yesterday) with activity. */
export function getStreak(subscriberId: string): number {
  const days = getDb()
    .prepare(
      `SELECT DISTINCT date(completed_at, 'localtime') AS d
       FROM progress WHERE subscriber_id = ? ORDER BY d DESC`,
    )
    .all(subscriberId) as { d: string }[];
  if (days.length === 0) return 0;

  const set = new Set(days.map((r) => r.d));
  const today = new Date();
  const fmt = (dt: Date) => dt.toISOString().slice(0, 10);

  // Streak may end today or yesterday and still count as "current".
  let cursor = new Date(today);
  if (!set.has(fmt(cursor))) {
    cursor.setDate(cursor.getDate() - 1);
    if (!set.has(fmt(cursor))) return 0;
  }
  let streak = 0;
  while (set.has(fmt(cursor))) {
    streak += 1;
    cursor.setDate(cursor.getDate() - 1);
  }
  return streak;
}

/* ----------------------------- settings ----------------------------- */

export function getSettings(subscriberId: string): SettingsRow | undefined {
  return getDb()
    .prepare('SELECT * FROM settings WHERE subscriber_id = ?')
    .get(subscriberId) as SettingsRow | undefined;
}

export function ensureSettings(subscriberId: string): SettingsRow {
  const existing = getSettings(subscriberId);
  if (existing) return existing;
  getDb()
    .prepare('INSERT OR IGNORE INTO settings (subscriber_id) VALUES (?)')
    .run(subscriberId);
  return getSettings(subscriberId)!;
}

export function setParentPin(subscriberId: string, pinHash: string) {
  ensureSettings(subscriberId);
  getDb()
    .prepare(
      `UPDATE settings SET parent_pin_hash = ?, updated_at = CURRENT_TIMESTAMP
       WHERE subscriber_id = ?`,
    )
    .run(pinHash, subscriberId);
}

export function updateSettings(
  subscriberId: string,
  fields: { dailyLimitMinutes?: number | null; enabledSubjects?: string },
) {
  ensureSettings(subscriberId);
  const current = getSettings(subscriberId)!;
  const dailyLimit =
    fields.dailyLimitMinutes === undefined ? current.daily_limit_minutes : fields.dailyLimitMinutes;
  const enabled = fields.enabledSubjects ?? current.enabled_subjects;
  getDb()
    .prepare(
      `UPDATE settings
       SET daily_limit_minutes = ?, enabled_subjects = ?, updated_at = CURRENT_TIMESTAMP
       WHERE subscriber_id = ?`,
    )
    .run(dailyLimit, enabled, subscriberId);
}

export function getEnabledSubjects(subscriberId: string): string[] | 'all' {
  const s = getSettings(subscriberId);
  if (!s || !s.enabled_subjects || s.enabled_subjects === 'all') return 'all';
  try {
    const parsed = JSON.parse(s.enabled_subjects);
    return Array.isArray(parsed) ? parsed : 'all';
  } catch {
    return 'all';
  }
}

/* ---------------------------- age band ----------------------------- */

export function getAgeBandFor(subscriberId: string): string | null {
  return getSettings(subscriberId)?.age_band ?? null;
}

export function setAgeBand(subscriberId: string, band: string) {
  ensureSettings(subscriberId);
  getDb()
    .prepare(`UPDATE settings SET age_band = ?, updated_at = CURRENT_TIMESTAMP WHERE subscriber_id = ?`)
    .run(band, subscriberId);
}

export function isVoiceEnabled(subscriberId: string): boolean {
  return (getSettings(subscriberId)?.voice_enabled ?? 1) !== 0;
}

/* -------------------------- AI settings ---------------------------- */

export interface AiSettings {
  enabled: boolean;
  dailyLimit: number;
  features: string[] | 'all';
}

export function getAiSettings(subscriberId: string): AiSettings {
  const s = getSettings(subscriberId);
  let features: string[] | 'all' = 'all';
  if (s?.ai_features && s.ai_features !== 'all') {
    try {
      const parsed = JSON.parse(s.ai_features);
      if (Array.isArray(parsed)) features = parsed;
    } catch {
      /* keep 'all' */
    }
  }
  return {
    enabled: (s?.ai_enabled ?? 0) !== 0,
    dailyLimit: s?.ai_daily_limit ?? 20,
    features,
  };
}

export function aiFeatureEnabled(subscriberId: string, feature: string): boolean {
  const ai = getAiSettings(subscriberId);
  if (!ai.enabled) return false;
  return ai.features === 'all' || ai.features.includes(feature);
}

export function updateAiSettings(
  subscriberId: string,
  fields: { enabled?: boolean; dailyLimit?: number; features?: string[] | 'all'; voiceEnabled?: boolean },
) {
  ensureSettings(subscriberId);
  const s = getSettings(subscriberId)!;
  const enabled = fields.enabled === undefined ? s.ai_enabled : fields.enabled ? 1 : 0;
  const dailyLimit = fields.dailyLimit === undefined ? s.ai_daily_limit : fields.dailyLimit;
  const features =
    fields.features === undefined
      ? s.ai_features
      : fields.features === 'all'
        ? 'all'
        : JSON.stringify(fields.features);
  const voice = fields.voiceEnabled === undefined ? s.voice_enabled : fields.voiceEnabled ? 1 : 0;
  getDb()
    .prepare(
      `UPDATE settings
       SET ai_enabled = ?, ai_daily_limit = ?, ai_features = ?, voice_enabled = ?, updated_at = CURRENT_TIMESTAMP
       WHERE subscriber_id = ?`,
    )
    .run(enabled, dailyLimit, features, voice, subscriberId);
}

/* ------------------------------ coins ------------------------------ */

export function getCoins(subscriberId: string): number {
  const row = getDb().prepare('SELECT balance FROM coins WHERE subscriber_id = ?').get(subscriberId) as
    | { balance: number }
    | undefined;
  return row?.balance ?? 0;
}

export function addCoins(subscriberId: string, amount: number) {
  getDb()
    .prepare(
      `INSERT INTO coins (subscriber_id, balance, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP)
       ON CONFLICT(subscriber_id) DO UPDATE SET balance = balance + ?, updated_at = CURRENT_TIMESTAMP`,
    )
    .run(subscriberId, Math.max(0, amount), Math.max(0, amount));
}

/** Spend coins; returns true if the balance was sufficient. */
export function spendCoins(subscriberId: string, amount: number): boolean {
  if (getCoins(subscriberId) < amount) return false;
  getDb()
    .prepare(
      `UPDATE coins SET balance = balance - ?, updated_at = CURRENT_TIMESTAMP WHERE subscriber_id = ?`,
    )
    .run(amount, subscriberId);
  return true;
}

/* -------------------------- mascot skin ---------------------------- */

export function getSelectedSkin(subscriberId: string): string {
  return getSettings(subscriberId)?.mascot_skin ?? 'classic';
}

export function setSelectedSkin(subscriberId: string, skinId: string) {
  ensureSettings(subscriberId);
  getDb()
    .prepare(`UPDATE settings SET mascot_skin = ?, updated_at = CURRENT_TIMESTAMP WHERE subscriber_id = ?`)
    .run(skinId, subscriberId);
}

/* ----------------------------- unlocks ----------------------------- */

export function addUnlock(subscriberId: string, itemType: string, itemId: string) {
  getDb()
    .prepare(
      `INSERT OR IGNORE INTO unlocks (subscriber_id, item_type, item_id) VALUES (?, ?, ?)`,
    )
    .run(subscriberId, itemType, itemId);
}

export function getUnlocks(subscriberId: string, itemType?: string): string[] {
  const rows = itemType
    ? (getDb()
        .prepare('SELECT item_id FROM unlocks WHERE subscriber_id = ? AND item_type = ?')
        .all(subscriberId, itemType) as { item_id: string }[])
    : (getDb()
        .prepare('SELECT item_id FROM unlocks WHERE subscriber_id = ?')
        .all(subscriberId) as { item_id: string }[]);
  return rows.map((r) => r.item_id);
}

/* ------------------------- daily challenge ------------------------- */

function today(): string {
  return new Date().toISOString().slice(0, 10);
}

export function isDailyDone(subscriberId: string): boolean {
  const row = getDb()
    .prepare(`SELECT 1 FROM unlocks WHERE subscriber_id = ? AND item_type = 'daily' AND item_id = ?`)
    .get(subscriberId, today());
  return Boolean(row);
}

/** Mark today's challenge done; returns true if this was the first time today. */
export function markDailyDone(subscriberId: string): boolean {
  if (isDailyDone(subscriberId)) return false;
  addUnlock(subscriberId, 'daily', today());
  return true;
}

/* ---------------------------- AI usage ----------------------------- */

export function getAiUsageToday(subscriberId: string): number {
  const row = getDb()
    .prepare('SELECT COALESCE(SUM(count),0) AS total FROM ai_usage WHERE subscriber_id = ? AND day = ?')
    .get(subscriberId, today()) as { total: number };
  return row.total;
}

export function incrementAiUsage(subscriberId: string, feature: string) {
  getDb()
    .prepare(
      `INSERT INTO ai_usage (subscriber_id, day, feature, count) VALUES (?, ?, ?, 1)
       ON CONFLICT(subscriber_id, day, feature) DO UPDATE SET count = count + 1`,
    )
    .run(subscriberId, today(), feature);
}

/* --------------------------- AI chat log --------------------------- */

export function logAiMessage(
  subscriberId: string,
  feature: string,
  role: 'child' | 'ai' | 'system',
  content: string,
  flagged = false,
) {
  getDb()
    .prepare(
      `INSERT INTO ai_chat_log (subscriber_id, feature, role, content, flagged) VALUES (?, ?, ?, ?, ?)`,
    )
    .run(subscriberId, feature, role, content, flagged ? 1 : 0);
}

export interface ChatLogRow {
  id: number;
  feature: string;
  role: string;
  content: string;
  flagged: number;
  created_at: string;
}

export function getRecentChatLog(subscriberId: string, limit = 100): ChatLogRow[] {
  return getDb()
    .prepare(
      `SELECT id, feature, role, content, flagged, created_at
       FROM ai_chat_log WHERE subscriber_id = ? ORDER BY id DESC LIMIT ?`,
    )
    .all(subscriberId, limit) as ChatLogRow[];
}

/* ---------------------------- AI stories --------------------------- */

export function saveStory(subscriberId: string, title: string, body: string): number {
  const info = getDb()
    .prepare('INSERT INTO ai_stories (subscriber_id, title, body) VALUES (?, ?, ?)')
    .run(subscriberId, title, body);
  return Number(info.lastInsertRowid);
}

export interface StoryRow {
  id: number;
  title: string;
  body: string;
  created_at: string;
}

export function getStories(subscriberId: string, limit = 30): StoryRow[] {
  return getDb()
    .prepare(
      'SELECT id, title, body, created_at FROM ai_stories WHERE subscriber_id = ? ORDER BY id DESC LIMIT ?',
    )
    .all(subscriberId, limit) as StoryRow[];
}