spb/groupe-ka Public

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2// KA ID — base d'utilisateurs partagée du groupe (SQLite WAL).3import Database from "better-sqlite3";4import path from "path";5import fs from "fs";67function open() {8  const dbPath =9    process.env.KA_DB_PATH ?? path.join(process.cwd(), "data", "ka-id.db");10  fs.mkdirSync(path.dirname(dbPath), { recursive: true });11  const db = new Database(dbPath);12  db.pragma("journal_mode = WAL");13  db.exec(`14    CREATE TABLE IF NOT EXISTS users (15      id INTEGER PRIMARY KEY AUTOINCREMENT,16      email TEXT UNIQUE NOT NULL,17      name TEXT NOT NULL,18      avatar_url TEXT,19      password_hash TEXT,20      google_sub TEXT UNIQUE,21      created_at TEXT NOT NULL DEFAULT (datetime('now'))22    );23  `);24  // KA ID — identifiant membre du groupe (« ka- » + 10 chiffres), créé ICI,25  // au hub : les plateformes (Lou·Ka…) le reçoivent via le jeton SSO.26  try {27    db.exec("ALTER TABLE users ADD COLUMN ka_id TEXT");28  } catch {29    /* colonne déjà présente */30  }31  try {32    db.exec("ALTER TABLE users ADD COLUMN last_login TEXT");33  } catch {34    /* colonne déjà présente */35  }36  // Sign in with Apple (2026-08-13) — même rôle que google_sub.37  try {38    db.exec("ALTER TABLE users ADD COLUMN apple_sub TEXT");39  } catch {40    /* colonne déjà présente */41  }42  db.exec(43    "CREATE UNIQUE INDEX IF NOT EXISTS users_apple_sub ON users(apple_sub)",44  );45  try {46    db.exec("ALTER TABLE users ADD COLUMN role TEXT");47  } catch {48    /* colonne déjà présente */49  }50  // Profil enrichi (2026-08-13) — saisi sur le hub, LA source de vérité51  // affichée par toutes les plateformes du groupe.52  for (const col of [53    "bio TEXT",54    "city TEXT",55    "phone TEXT",56    "website TEXT",57    "job_title TEXT",58    "company TEXT",59    "birth_date TEXT",60    "socials TEXT",61    "public INTEGER DEFAULT 0",62  ]) {63    try {64      db.exec(`ALTER TABLE users ADD COLUMN ${col}`);65    } catch {66      /* colonne déjà présente */67    }68  }69  db.exec("CREATE UNIQUE INDEX IF NOT EXISTS users_ka_id ON users(ka_id)");70  // rattrapage : tout compte existant sans KA ID en reçoit un71  const missing = db72    .prepare("SELECT id FROM users WHERE ka_id IS NULL")73    .all() as { id: number }[];74  for (const row of missing) assignKaId(db, row.id);75  // Favoris unifiés « Mon univers Ka » (2026-08-13) — le hub est LE magasin76  // central : les plateformes poussent/lisent via /api/sso/favorites.77  db.exec(`78    CREATE TABLE IF NOT EXISTS favorites (79      id INTEGER PRIMARY KEY AUTOINCREMENT,80      user_id INTEGER NOT NULL,81      app TEXT NOT NULL,82      item_id TEXT NOT NULL,83      title TEXT NOT NULL,84      subtitle TEXT,85      price_label TEXT,86      image_url TEXT,87      url TEXT,88      meta TEXT,89      created_at TEXT NOT NULL DEFAULT (datetime('now')),90      updated_at TEXT NOT NULL DEFAULT (datetime('now')),91      UNIQUE(user_id, app, item_id)92    );93    CREATE INDEX IF NOT EXISTS favorites_user ON favorites(user_id, app);94  `);95  return db;96}9798function randomKaId(): string {99  let digits = "";100  for (let i = 0; i < 10; i++)101    digits += Math.floor(Math.random() * 10).toString();102  return `ka-${digits}`;103}104105function assignKaId(d: Database.Database, id: number): string {106  for (;;) {107    const kid = randomKaId();108    try {109      d.prepare("UPDATE users SET ka_id = ? WHERE id = ?").run(kid, id);110      return kid;111    } catch {112      /* collision (1 chance sur 10 milliards) : on retente */113    }114  }115}116117/** KA ID d'un compte — l'attribue s'il manque (source de vérité du groupe). */118export function ensureKaId(id: number): string {119  const row = db.prepare("SELECT ka_id FROM users WHERE id = ?").get(id) as120    | { ka_id: string | null }121    | undefined;122  if (row?.ka_id) return row.ka_id;123  return assignKaId(db, id);124}125126// Singleton (survit au rechargement à chaud en dev).127const g = globalThis as unknown as { __kaDb?: Database.Database };128export const db: Database.Database = g.__kaDb ?? (g.__kaDb = open());129130export type UserRow = {131  id: number;132  email: string;133  name: string;134  avatar_url: string | null;135  password_hash: string | null;136  google_sub: string | null;137  apple_sub: string | null;138  ka_id: string | null;139  created_at: string;140  last_login: string | null;141  role: string | null;142  bio: string | null;143  city: string | null;144  phone: string | null;145  website: string | null;146  job_title: string | null;147  company: string | null;148  birth_date: string | null;149  socials: string | null; // JSON {instagram, facebook, x, linkedin, tiktok, youtube}150  public: number;151};152153export const SOCIAL_KEYS = [154  "instagram",155  "facebook",156  "x",157  "linkedin",158  "tiktok",159  "youtube",160] as const;161162export function parseSocials(raw: string | null): Record<string, string> {163  try {164    const obj = JSON.parse(raw ?? "{}") as Record<string, unknown>;165    const out: Record<string, string> = {};166    for (const k of SOCIAL_KEYS)167      if (typeof obj[k] === "string" && obj[k]) out[k] = obj[k] as string;168    return out;169  } catch {170    return {};171  }172}173174export function ageFromBirthDate(birth: string | null): number | null {175  if (!birth || !/^\d{4}-\d{2}-\d{2}$/.test(birth)) return null;176  const b = new Date(birth + "T00:00:00Z");177  if (Number.isNaN(b.getTime())) return null;178  const now = new Date();179  let age = now.getUTCFullYear() - b.getUTCFullYear();180  const m = now.getUTCMonth() - b.getUTCMonth();181  if (m < 0 || (m === 0 && now.getUTCDate() < b.getUTCDate())) age--;182  return age >= 0 && age < 130 ? age : null;183}184185/** Horodate la connexion (appelé à chaque login/register/SSO). */186export function touchLastLogin(id: number): void {187  db.prepare(188    "UPDATE users SET last_login = datetime('now') WHERE id = ?",189  ).run(id);190}191192export function findUserByEmail(email: string): UserRow | undefined {193  return db194    .prepare("SELECT * FROM users WHERE email = ?")195    .get(email.toLowerCase()) as UserRow | undefined;196}197198export function findUserById(id: number): UserRow | undefined {199  return db.prepare("SELECT * FROM users WHERE id = ?").get(id) as200    | UserRow201    | undefined;202}203204export function findUserByGoogleSub(sub: string): UserRow | undefined {205  return db.prepare("SELECT * FROM users WHERE google_sub = ?").get(sub) as206    | UserRow207    | undefined;208}209210export function findUserByAppleSub(sub: string): UserRow | undefined {211  return db.prepare("SELECT * FROM users WHERE apple_sub = ?").get(sub) as212    | UserRow213    | undefined;214}215216export function findUserByKaId(kaId: string): UserRow | undefined {217  return db.prepare("SELECT * FROM users WHERE ka_id = ?").get(kaId) as218    | UserRow219    | undefined;220}221222/* ---------- Favoris unifiés ---------- */223224export type FavoriteRow = {225  id: number;226  user_id: number;227  app: string;228  item_id: string;229  title: string;230  subtitle: string | null;231  price_label: string | null;232  image_url: string | null;233  url: string | null;234  meta: string | null;235  created_at: string;236  updated_at: string;237};238239export function upsertFavorite(240  userId: number,241  app: string,242  item: {243    item_id: string;244    title: string;245    subtitle?: string;246    price_label?: string;247    image_url?: string;248    url?: string;249    meta?: unknown;250  },251): void {252  db.prepare(253    `INSERT INTO favorites (user_id, app, item_id, title, subtitle,254                            price_label, image_url, url, meta)255     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)256     ON CONFLICT(user_id, app, item_id) DO UPDATE SET257       title = excluded.title, subtitle = excluded.subtitle,258       price_label = excluded.price_label, image_url = excluded.image_url,259       url = excluded.url, meta = excluded.meta,260       updated_at = datetime('now')`,261  ).run(262    userId,263    app,264    item.item_id,265    item.title,266    item.subtitle ?? null,267    item.price_label ?? null,268    item.image_url ?? null,269    item.url ?? null,270    item.meta != null ? JSON.stringify(item.meta) : null,271  );272}273274export function removeFavorite(275  userId: number,276  app: string,277  itemId: string,278): void {279  db.prepare(280    "DELETE FROM favorites WHERE user_id = ? AND app = ? AND item_id = ?",281  ).run(userId, app, itemId);282}283284export function favoritesOf(userId: number, app?: string): FavoriteRow[] {285  return (286    app287      ? db.prepare(288          "SELECT * FROM favorites WHERE user_id = ? AND app = ? ORDER BY created_at DESC",289        ).all(userId, app)290      : db.prepare(291          "SELECT * FROM favorites WHERE user_id = ? ORDER BY created_at DESC",292        ).all(userId)293  ) as FavoriteRow[];294}295