// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
// KA ID — base d'utilisateurs partagée du groupe (SQLite WAL).
import Database from "better-sqlite3";
import path from "path";
import fs from "fs";

function open() {
  const dbPath =
    process.env.KA_DB_PATH ?? path.join(process.cwd(), "data", "ka-id.db");
  fs.mkdirSync(path.dirname(dbPath), { recursive: true });
  const db = new Database(dbPath);
  db.pragma("journal_mode = WAL");
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT UNIQUE NOT NULL,
      name TEXT NOT NULL,
      avatar_url TEXT,
      password_hash TEXT,
      google_sub TEXT UNIQUE,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);
  // KA ID — identifiant membre du groupe (« ka- » + 10 chiffres), créé ICI,
  // au hub : les plateformes (Lou·Ka…) le reçoivent via le jeton SSO.
  try {
    db.exec("ALTER TABLE users ADD COLUMN ka_id TEXT");
  } catch {
    /* colonne déjà présente */
  }
  try {
    db.exec("ALTER TABLE users ADD COLUMN last_login TEXT");
  } catch {
    /* colonne déjà présente */
  }
  // Sign in with Apple (2026-08-13) — même rôle que google_sub.
  try {
    db.exec("ALTER TABLE users ADD COLUMN apple_sub TEXT");
  } catch {
    /* colonne déjà présente */
  }
  db.exec(
    "CREATE UNIQUE INDEX IF NOT EXISTS users_apple_sub ON users(apple_sub)",
  );
  try {
    db.exec("ALTER TABLE users ADD COLUMN role TEXT");
  } catch {
    /* colonne déjà présente */
  }
  // Profil enrichi (2026-08-13) — saisi sur le hub, LA source de vérité
  // affichée par toutes les plateformes du groupe.
  for (const col of [
    "bio TEXT",
    "city TEXT",
    "phone TEXT",
    "website TEXT",
    "job_title TEXT",
    "company TEXT",
    "birth_date TEXT",
    "socials TEXT",
    "public INTEGER DEFAULT 0",
  ]) {
    try {
      db.exec(`ALTER TABLE users ADD COLUMN ${col}`);
    } catch {
      /* colonne déjà présente */
    }
  }
  db.exec("CREATE UNIQUE INDEX IF NOT EXISTS users_ka_id ON users(ka_id)");
  // rattrapage : tout compte existant sans KA ID en reçoit un
  const missing = db
    .prepare("SELECT id FROM users WHERE ka_id IS NULL")
    .all() as { id: number }[];
  for (const row of missing) assignKaId(db, row.id);
  // Favoris unifiés « Mon univers Ka » (2026-08-13) — le hub est LE magasin
  // central : les plateformes poussent/lisent via /api/sso/favorites.
  db.exec(`
    CREATE TABLE IF NOT EXISTS favorites (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      app TEXT NOT NULL,
      item_id TEXT NOT NULL,
      title TEXT NOT NULL,
      subtitle TEXT,
      price_label TEXT,
      image_url TEXT,
      url TEXT,
      meta TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE(user_id, app, item_id)
    );
    CREATE INDEX IF NOT EXISTS favorites_user ON favorites(user_id, app);
  `);
  return db;
}

function randomKaId(): string {
  let digits = "";
  for (let i = 0; i < 10; i++)
    digits += Math.floor(Math.random() * 10).toString();
  return `ka-${digits}`;
}

function assignKaId(d: Database.Database, id: number): string {
  for (;;) {
    const kid = randomKaId();
    try {
      d.prepare("UPDATE users SET ka_id = ? WHERE id = ?").run(kid, id);
      return kid;
    } catch {
      /* collision (1 chance sur 10 milliards) : on retente */
    }
  }
}

/** KA ID d'un compte — l'attribue s'il manque (source de vérité du groupe). */
export function ensureKaId(id: number): string {
  const row = db.prepare("SELECT ka_id FROM users WHERE id = ?").get(id) as
    | { ka_id: string | null }
    | undefined;
  if (row?.ka_id) return row.ka_id;
  return assignKaId(db, id);
}

// Singleton (survit au rechargement à chaud en dev).
const g = globalThis as unknown as { __kaDb?: Database.Database };
export const db: Database.Database = g.__kaDb ?? (g.__kaDb = open());

export type UserRow = {
  id: number;
  email: string;
  name: string;
  avatar_url: string | null;
  password_hash: string | null;
  google_sub: string | null;
  apple_sub: string | null;
  ka_id: string | null;
  created_at: string;
  last_login: string | null;
  role: string | null;
  bio: string | null;
  city: string | null;
  phone: string | null;
  website: string | null;
  job_title: string | null;
  company: string | null;
  birth_date: string | null;
  socials: string | null; // JSON {instagram, facebook, x, linkedin, tiktok, youtube}
  public: number;
};

export const SOCIAL_KEYS = [
  "instagram",
  "facebook",
  "x",
  "linkedin",
  "tiktok",
  "youtube",
] as const;

export function parseSocials(raw: string | null): Record<string, string> {
  try {
    const obj = JSON.parse(raw ?? "{}") as Record<string, unknown>;
    const out: Record<string, string> = {};
    for (const k of SOCIAL_KEYS)
      if (typeof obj[k] === "string" && obj[k]) out[k] = obj[k] as string;
    return out;
  } catch {
    return {};
  }
}

export function ageFromBirthDate(birth: string | null): number | null {
  if (!birth || !/^\d{4}-\d{2}-\d{2}$/.test(birth)) return null;
  const b = new Date(birth + "T00:00:00Z");
  if (Number.isNaN(b.getTime())) return null;
  const now = new Date();
  let age = now.getUTCFullYear() - b.getUTCFullYear();
  const m = now.getUTCMonth() - b.getUTCMonth();
  if (m < 0 || (m === 0 && now.getUTCDate() < b.getUTCDate())) age--;
  return age >= 0 && age < 130 ? age : null;
}

/** Horodate la connexion (appelé à chaque login/register/SSO). */
export function touchLastLogin(id: number): void {
  db.prepare(
    "UPDATE users SET last_login = datetime('now') WHERE id = ?",
  ).run(id);
}

export function findUserByEmail(email: string): UserRow | undefined {
  return db
    .prepare("SELECT * FROM users WHERE email = ?")
    .get(email.toLowerCase()) as UserRow | undefined;
}

export function findUserById(id: number): UserRow | undefined {
  return db.prepare("SELECT * FROM users WHERE id = ?").get(id) as
    | UserRow
    | undefined;
}

export function findUserByGoogleSub(sub: string): UserRow | undefined {
  return db.prepare("SELECT * FROM users WHERE google_sub = ?").get(sub) as
    | UserRow
    | undefined;
}

export function findUserByAppleSub(sub: string): UserRow | undefined {
  return db.prepare("SELECT * FROM users WHERE apple_sub = ?").get(sub) as
    | UserRow
    | undefined;
}

export function findUserByKaId(kaId: string): UserRow | undefined {
  return db.prepare("SELECT * FROM users WHERE ka_id = ?").get(kaId) as
    | UserRow
    | undefined;
}

/* ---------- Favoris unifiés ---------- */

export type FavoriteRow = {
  id: number;
  user_id: number;
  app: string;
  item_id: string;
  title: string;
  subtitle: string | null;
  price_label: string | null;
  image_url: string | null;
  url: string | null;
  meta: string | null;
  created_at: string;
  updated_at: string;
};

export function upsertFavorite(
  userId: number,
  app: string,
  item: {
    item_id: string;
    title: string;
    subtitle?: string;
    price_label?: string;
    image_url?: string;
    url?: string;
    meta?: unknown;
  },
): void {
  db.prepare(
    `INSERT INTO favorites (user_id, app, item_id, title, subtitle,
                            price_label, image_url, url, meta)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
     ON CONFLICT(user_id, app, item_id) DO UPDATE SET
       title = excluded.title, subtitle = excluded.subtitle,
       price_label = excluded.price_label, image_url = excluded.image_url,
       url = excluded.url, meta = excluded.meta,
       updated_at = datetime('now')`,
  ).run(
    userId,
    app,
    item.item_id,
    item.title,
    item.subtitle ?? null,
    item.price_label ?? null,
    item.image_url ?? null,
    item.url ?? null,
    item.meta != null ? JSON.stringify(item.meta) : null,
  );
}

export function removeFavorite(
  userId: number,
  app: string,
  itemId: string,
): void {
  db.prepare(
    "DELETE FROM favorites WHERE user_id = ? AND app = ? AND item_id = ?",
  ).run(userId, app, itemId);
}

export function favoritesOf(userId: number, app?: string): FavoriteRow[] {
  return (
    app
      ? db.prepare(
          "SELECT * FROM favorites WHERE user_id = ? AND app = ? ORDER BY created_at DESC",
        ).all(userId, app)
      : db.prepare(
          "SELECT * FROM favorites WHERE user_id = ? ORDER BY created_at DESC",
        ).all(userId)
  ) as FavoriteRow[];
}