import { drizzle, type MySql2Database } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { and, asc, desc, eq, gte, inArray, like, lt, or, sql } from "drizzle-orm";
import crypto from "node:crypto";
import { childLogger } from "./_core/logger.js";
import {
  users,
  subscriptions,
  clinics,
  queueEntries,
  analyticsEvents,
  whatsappCountryCodes,
  whatsappLogs,
  clinicMembers,
  appConfig,
  type User,
  type Subscription,
  type Clinic,
  type QueueEntry,
  type AnalyticsEvent,
  type ClinicMember,
  type AppConfig,
  type InsertUser,
  type InsertClinic,
  type InsertQueueEntry,
  type InsertWhatsappLog,
  type InsertClinicMember,
} from "./schema.js";

// ─── Connection pool (singleton) ─────────────────────────────────────────────
let pool: mysql.Pool | null = null;
let dbInstance: MySql2Database<{
  users: typeof users;
  subscriptions: typeof subscriptions;
  clinics: typeof clinics;
  queueEntries: typeof queueEntries;
  analyticsEvents: typeof analyticsEvents;
  whatsappCountryCodes: typeof whatsappCountryCodes;
  whatsappLogs: typeof whatsappLogs;
  clinicMembers: typeof clinicMembers;
  appConfig: typeof appConfig;
}> | null = null;

export async function getDb() {
  if (dbInstance) return dbInstance;

  const url = process.env.DATABASE_URL;
  if (!url) {
    throw new Error("DATABASE_URL is not set");
  }

  pool = mysql.createPool({
    uri: url,
    connectionLimit: 10,
    waitForConnections: true,
    enableKeepAlive: true,
    keepAliveInitialDelay: 10_000,
  });

  dbInstance = drizzle(pool, {
    schema: { users, subscriptions, clinics, queueEntries, analyticsEvents, whatsappCountryCodes, whatsappLogs, clinicMembers, appConfig },
    mode: "default",
  });

  return dbInstance;
}

export async function closeDb() {
  if (pool) {
    await pool.end();
    pool = null;
    dbInstance = null;
  }
}

/**
 * Ping the database with a trivial query. Used by health/readiness probes.
 * Returns the latency in ms on success, or an error message on failure.
 */
export async function pingDb(): Promise<{ ok: true; latencyMs: number } | { ok: false; error: string }> {
  const start = Date.now();
  try {
    const db = await getDb();
    await db.execute(sql`SELECT 1`);
    return { ok: true, latencyMs: Date.now() - start };
  } catch (err) {
    const message = err instanceof Error ? err.message : String(err);
    return { ok: false, error: message };
  }
}

// ─── Users ───────────────────────────────────────────────────────────────────
export async function getUserByEmail(email: string): Promise<User | null> {
  const db = await getDb();
  const rows = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return rows[0] ?? null;
}

export async function getUserById(id: number): Promise<User | null> {
  const db = await getDb();
  const rows = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return rows[0] ?? null;
}

export async function getUserByOpenId(openId: string): Promise<User | null> {
  const db = await getDb();
  const rows = await db.select().from(users).where(eq(users.openId, openId)).limit(1);
  return rows[0] ?? null;
}

export async function createUser(data: InsertUser): Promise<User> {
  const db = await getDb();
  const [result] = await db.insert(users).values(data);
  const id = (result as { insertId: number }).insertId;
  const created = await getUserById(id);
  if (!created) throw new Error("Failed to create user");
  return created;
}

export async function upsertUser(data: InsertUser): Promise<User> {
  const existing = await getUserByEmail(data.email);
  if (existing) {
    const db = await getDb();
    await db
      .update(users)
      .set({ ...data, lastSignedIn: new Date() })
      .where(eq(users.id, existing.id));
    const refreshed = await getUserById(existing.id);
    if (!refreshed) throw new Error("Failed to refresh user");
    return refreshed;
  }
  return createUser(data);
}

export async function touchUserLogin(userId: number): Promise<void> {
  const db = await getDb();
  await db.update(users).set({ lastSignedIn: new Date() }).where(eq(users.id, userId));
}

export async function setUserResetToken(
  userId: number,
  resetToken: string | null,
  resetTokenExpiry: Date | null
): Promise<void> {
  const db = await getDb();
  await db.update(users).set({ resetToken, resetTokenExpiry }).where(eq(users.id, userId));
}

export async function getUserByResetToken(token: string): Promise<User | null> {
  const db = await getDb();
  const rows = await db.select().from(users).where(eq(users.resetToken, token)).limit(1);
  return rows[0] ?? null;
}

export async function updateUserPassword(userId: number, passwordHash: string): Promise<void> {
  const db = await getDb();
  await db
    .update(users)
    .set({ passwordHash, resetToken: null, resetTokenExpiry: null })
    .where(eq(users.id, userId));
}

// ─── Subscriptions ───────────────────────────────────────────────────────────
const TRIAL_DAYS = 30;

export async function createTrialSubscription(userId: number): Promise<Subscription> {
  const db = await getDb();
  const trialStart = new Date();
  const trialEnd = new Date(trialStart.getTime() + TRIAL_DAYS * 24 * 60 * 60 * 1000);
  await db.insert(subscriptions).values({
    userId,
    plan: "trial",
    status: "trialing",
    trialStartedAt: trialStart,
    trialEndsAt: trialEnd,
  });
  const sub = await getSubscription(userId);
  if (!sub) throw new Error("Failed to create trial subscription");
  return sub;
}

export async function getSubscription(userId: number): Promise<Subscription | null> {
  const db = await getDb();
  const rows = await db
    .select()
    .from(subscriptions)
    .where(eq(subscriptions.userId, userId))
    .orderBy(desc(subscriptions.createdAt))
    .limit(1);
  return rows[0] ?? null;
}

export async function updateSubscription(
  userId: number,
  patch: Partial<typeof subscriptions.$inferInsert>
): Promise<void> {
  const db = await getDb();
  await db.update(subscriptions).set(patch).where(eq(subscriptions.userId, userId));
}

export async function isSubscriptionActive(userId: number): Promise<boolean> {
  const sub = await getSubscription(userId);
  if (!sub) return false;
  const now = Date.now();
  if (sub.status === "canceled" || sub.status === "expired") return false;
  if (sub.status === "trialing") {
    return sub.trialEndsAt.getTime() > now;
  }
  if (sub.status === "active") {
    if (!sub.currentPeriodEnd) return true;
    return sub.currentPeriodEnd.getTime() > now;
  }
  return false;
}

// ─── Clinics ─────────────────────────────────────────────────────────────────
function generateQrToken(): string {
  return crypto.randomBytes(24).toString("hex");
}

function computeQrExpiry(rotationMinutes: number | null | undefined): Date | null {
  if (!rotationMinutes || rotationMinutes <= 0) return null;
  return new Date(Date.now() + rotationMinutes * 60 * 1000);
}

export async function getClinics(userId: number): Promise<Clinic[]> {
  const db = await getDb();
  return db
    .select()
    .from(clinics)
    .where(eq(clinics.userId, userId))
    .orderBy(desc(clinics.createdAt));
}

export async function getClinicById(id: number): Promise<Clinic | null> {
  const db = await getDb();
  const rows = await db.select().from(clinics).where(eq(clinics.id, id)).limit(1);
  return rows[0] ?? null;
}

export async function getClinicByQrToken(token: string): Promise<Clinic | null> {
  const db = await getDb();
  const rows = await db.select().from(clinics).where(eq(clinics.qrToken, token)).limit(1);
  return rows[0] ?? null;
}

export async function createClinic(
  userId: number,
  data: Omit<InsertClinic, "userId" | "qrToken" | "qrTokenExpiresAt">
): Promise<{ insertId: number; qrToken: string }> {
  const db = await getDb();
  const qrToken = generateQrToken();
  const qrTokenExpiresAt = computeQrExpiry(data.qrRotationMinutes ?? 30);
  const [result] = await db.insert(clinics).values({
    ...data,
    userId,
    qrToken,
    qrTokenExpiresAt,
  });
  const insertId = (result as { insertId: number }).insertId;
  return { insertId, qrToken };
}

export async function updateClinic(
  id: number,
  patch: Partial<typeof clinics.$inferInsert>
): Promise<void> {
  const db = await getDb();
  await db.update(clinics).set(patch).where(eq(clinics.id, id));
}

export async function deleteClinic(id: number): Promise<void> {
  const db = await getDb();
  await db.delete(queueEntries).where(eq(queueEntries.clinicId, id));
  await db.delete(analyticsEvents).where(eq(analyticsEvents.clinicId, id));
  await db.delete(clinics).where(eq(clinics.id, id));
}

export async function rotateQrToken(clinicId: number): Promise<{ qrToken: string; qrTokenExpiresAt: Date | null }> {
  const db = await getDb();
  const clinic = await getClinicById(clinicId);
  if (!clinic) throw new Error("Clinic not found");
  const qrToken = generateQrToken();
  const qrTokenExpiresAt = computeQrExpiry(clinic.qrRotationMinutes);
  await db
    .update(clinics)
    .set({ qrToken, qrTokenExpiresAt })
    .where(eq(clinics.id, clinicId));
  return { qrToken, qrTokenExpiresAt };
}

export async function ensureFreshQrToken(clinic: Clinic): Promise<Clinic> {
  if (!clinic.qrRotationMinutes || clinic.qrRotationMinutes <= 0) return clinic;
  if (clinic.qrTokenExpiresAt && clinic.qrTokenExpiresAt.getTime() > Date.now()) return clinic;
  await rotateQrToken(clinic.id);
  const refreshed = await getClinicById(clinic.id);
  return refreshed ?? clinic;
}

// ─── Queue ───────────────────────────────────────────────────────────────────
type QueueStatus = (typeof queueEntries.$inferSelect)["status"];
const ACTIVE_STATUSES: QueueStatus[] = ["waiting", "called", "in_consultation"];

export async function getActiveQueue(clinicId: number): Promise<QueueEntry[]> {
  const db = await getDb();
  return db
    .select()
    .from(queueEntries)
    .where(
      and(
        eq(queueEntries.clinicId, clinicId),
        inArray(queueEntries.status, ACTIVE_STATUSES)
      )
    )
    .orderBy(queueEntries.position);
}

export async function getAllQueueEntries(clinicId: number): Promise<QueueEntry[]> {
  const db = await getDb();
  return db
    .select()
    .from(queueEntries)
    .where(eq(queueEntries.clinicId, clinicId))
    .orderBy(queueEntries.position);
}

export async function getQueueEntry(id: number): Promise<QueueEntry | null> {
  const db = await getDb();
  const rows = await db.select().from(queueEntries).where(eq(queueEntries.id, id)).limit(1);
  return rows[0] ?? null;
}

export async function getQueueEntryByToken(token: string): Promise<QueueEntry | null> {
  const db = await getDb();
  const rows = await db
    .select()
    .from(queueEntries)
    .where(eq(queueEntries.patientToken, token))
    .orderBy(desc(queueEntries.createdAt))
    .limit(1);
  return rows[0] ?? null;
}

export async function addToQueue(input: {
  clinicId: number;
  patientName?: string | null;
  patientPhone?: string | null;
  whatsappPhone?: string | null;
  visitReason?:
    | "consultation"
    | "urgence"
    | "certificat_scolaire"
    | "certificat_sportif"
    | "arret_travail"
    | "administratif"
    | "autre"
    | null;
  visitNote?: string | null;
  isPrinted?: boolean;
}): Promise<{ entry: QueueEntry; ticketNumber: number; patientToken: string }> {
  const db = await getDb();
  const clinic = await getClinicById(input.clinicId);
  if (!clinic) throw new Error("Clinic not found");
  if (!clinic.isQueueOpen) throw new Error("Queue is closed");

  const active = await getActiveQueue(input.clinicId);
  if (clinic.maxQueueSize && active.length >= clinic.maxQueueSize) {
    throw new Error("Queue is full");
  }

  const ticketNumber = (clinic.currentTicketNumber ?? 0) + 1;
  const patientToken = crypto.randomBytes(24).toString("hex");
  const position = active.length + 1;
  const estimatedWaitMinutes = (clinic.avgConsultationMinutes ?? 15) * (position - 1);

  const insertValues: InsertQueueEntry = {
    clinicId: input.clinicId,
    ticketNumber,
    patientToken,
    patientName: input.patientName ?? null,
    patientPhone: input.patientPhone ?? null,
    whatsappPhone: input.whatsappPhone ?? null,
    visitReason: input.visitReason ?? "consultation",
    visitNote: input.visitNote ?? null,
    status: "waiting",
    position,
    estimatedWaitMinutes,
    isPrinted: input.isPrinted ?? false,
  };

  const [result] = await db.insert(queueEntries).values(insertValues);
  const insertId = (result as { insertId: number }).insertId;

  await db
    .update(clinics)
    .set({ currentTicketNumber: ticketNumber })
    .where(eq(clinics.id, input.clinicId));

  const entry = await getQueueEntry(insertId);
  if (!entry) throw new Error("Failed to create queue entry");
  return { entry, ticketNumber, patientToken };
}

export async function updateQueueEntry(
  id: number,
  patch: Partial<typeof queueEntries.$inferInsert>
): Promise<void> {
  const db = await getDb();
  await db.update(queueEntries).set(patch).where(eq(queueEntries.id, id));
}

export async function reorderQueue(clinicId: number): Promise<QueueEntry[]> {
  const db = await getDb();
  const active = await db
    .select()
    .from(queueEntries)
    .where(
      and(
        eq(queueEntries.clinicId, clinicId),
        eq(queueEntries.status, "waiting")
      )
    )
    .orderBy(queueEntries.position, queueEntries.joinedAt);

  const clinic = await getClinicById(clinicId);
  const avg = clinic?.avgConsultationMinutes ?? 15;

  for (let i = 0; i < active.length; i++) {
    const entry = active[i];
    const newPosition = i + 1;
    const newWait = avg * (newPosition - 1);
    if (entry.position !== newPosition || entry.estimatedWaitMinutes !== newWait) {
      await db
        .update(queueEntries)
        .set({ position: newPosition, estimatedWaitMinutes: newWait })
        .where(eq(queueEntries.id, entry.id));
    }
  }

  return getActiveQueue(clinicId);
}

export async function setQueueOrder(
  clinicId: number,
  orderedIds: number[]
): Promise<QueueEntry[]> {
  const db = await getDb();
  const clinic = await getClinicById(clinicId);
  const avg = clinic?.avgConsultationMinutes ?? 15;
  for (let i = 0; i < orderedIds.length; i++) {
    const id = orderedIds[i];
    const newPosition = i + 1;
    const newWait = avg * (newPosition - 1);
    await db
      .update(queueEntries)
      .set({ position: newPosition, estimatedWaitMinutes: newWait })
      .where(and(eq(queueEntries.id, id), eq(queueEntries.clinicId, clinicId)));
  }
  return getActiveQueue(clinicId);
}

export async function resetQueue(clinicId: number): Promise<void> {
  const db = await getDb();
  await db
    .update(queueEntries)
    .set({ status: "canceled" })
    .where(
      and(
        eq(queueEntries.clinicId, clinicId),
        inArray(queueEntries.status, ACTIVE_STATUSES)
      )
    );
  await db
    .update(clinics)
    .set({ currentTicketNumber: 0 })
    .where(eq(clinics.id, clinicId));
}

// ─── Analytics ───────────────────────────────────────────────────────────────
export async function logAnalyticsEvent(
  data: typeof analyticsEvents.$inferInsert
): Promise<void> {
  const db = await getDb();
  const now = new Date();
  await db.insert(analyticsEvents).values({
    ...data,
    hourOfDay: data.hourOfDay ?? now.getHours(),
    dayOfWeek: data.dayOfWeek ?? now.getDay(),
  });
}

export async function getAnalytics(
  userId: number,
  options: { days?: number; clinicId?: number } = {}
): Promise<AnalyticsEvent[]> {
  const db = await getDb();
  const days = options.days ?? 30;
  const since = new Date(Date.now() - days * 24 * 60 * 60 * 1000);

  const userClinics = await getClinics(userId);
  if (userClinics.length === 0) return [];

  const clinicIds = options.clinicId
    ? userClinics.filter((c) => c.id === options.clinicId).map((c) => c.id)
    : userClinics.map((c) => c.id);

  if (clinicIds.length === 0) return [];

  return db
    .select()
    .from(analyticsEvents)
    .where(
      and(
        inArray(analyticsEvents.clinicId, clinicIds),
        gte(analyticsEvents.createdAt, since)
      )
    )
    .orderBy(desc(analyticsEvents.createdAt));
}

export async function getAnalyticsForClinic(
  clinicId: number,
  days = 30
): Promise<AnalyticsEvent[]> {
  const db = await getDb();
  const since = new Date(Date.now() - days * 24 * 60 * 60 * 1000);
  return db
    .select()
    .from(analyticsEvents)
    .where(
      and(
        eq(analyticsEvents.clinicId, clinicId),
        gte(analyticsEvents.createdAt, since)
      )
    )
    .orderBy(desc(analyticsEvents.createdAt));
}

// ─── WhatsApp helpers ────────────────────────────────────────────────────────
export async function getWaitingEntriesWithPhone(clinicId: number): Promise<QueueEntry[]> {
  const db = await getDb();
  return db
    .select()
    .from(queueEntries)
    .where(and(eq(queueEntries.clinicId, clinicId), eq(queueEntries.status, "waiting")))
    .orderBy(queueEntries.position);
}

/** Masque un numéro de téléphone pour la confidentialité */
export function maskPhone(phone: string): string {
  const cleaned = phone.replace(/[^\d+]/g, "");
  if (cleaned.length <= 4) return "****";
  const visibleStart = cleaned.slice(0, Math.min(4, cleaned.length - 2));
  const visibleEnd = cleaned.slice(-2);
  const hidden = "*".repeat(Math.max(0, cleaned.length - visibleStart.length - visibleEnd.length));
  return `${visibleStart}${hidden}${visibleEnd}`;
}

export async function insertWhatsAppLog(
  data: Omit<InsertWhatsappLog, "id" | "createdAt">
): Promise<void> {
  const db = await getDb();
  try {
    await db.insert(whatsappLogs).values(data);
  } catch (err) {
    childLogger("whatsapp-log").warn({ err }, "failed to insert log");
  }
}

export async function getWhatsAppLogs(
  clinicId: number,
  options: {
    limit?: number;
    offset?: number;
    messageType?: "joined" | "soon" | "called" | "withdrawn" | "test";
    status?: "sent" | "failed";
  } = {}
) {
  const db = await getDb();
  const { limit = 20, offset = 0, messageType, status } = options;
  const conditions = [eq(whatsappLogs.clinicId, clinicId)];
  if (messageType) conditions.push(eq(whatsappLogs.messageType, messageType));
  if (status) conditions.push(eq(whatsappLogs.status, status));

  const [rows, countRows] = await Promise.all([
    db
      .select()
      .from(whatsappLogs)
      .where(and(...conditions))
      .orderBy(desc(whatsappLogs.createdAt))
      .limit(limit)
      .offset(offset),
    db.select({ count: sql<number>`COUNT(*)` }).from(whatsappLogs).where(and(...conditions)),
  ]);

  return { logs: rows, total: countRows[0]?.count ?? 0 };
}

// ─── Consultation history & stats ────────────────────────────────────────────
export async function getConsultationHistory(
  clinicId: number,
  opts: {
    page?: number;
    perPage?: number;
    dateFrom?: Date;
    dateTo?: Date;
    visitReason?: string;
  } = {}
): Promise<{ entries: QueueEntry[]; total: number }> {
  const db = await getDb();
  const { page = 1, perPage = 20, dateFrom, dateTo, visitReason } = opts;
  const offset = (page - 1) * perPage;

  const conditions = [
    eq(queueEntries.clinicId, clinicId),
    inArray(queueEntries.status, ["done", "absent", "canceled"] as const),
  ];
  if (dateFrom) conditions.push(gte(queueEntries.joinedAt, dateFrom));
  if (dateTo) {
    const endOfDay = new Date(dateTo);
    endOfDay.setHours(23, 59, 59, 999);
    conditions.push(lt(queueEntries.joinedAt, endOfDay));
  }
  if (visitReason) {
    conditions.push(
      eq(
        queueEntries.visitReason,
        visitReason as "consultation" | "urgence" | "certificat_scolaire" | "certificat_sportif" | "arret_travail" | "administratif" | "autre"
      )
    );
  }

  const where = and(...conditions);

  const entries = await db
    .select()
    .from(queueEntries)
    .where(where!)
    .orderBy(desc(queueEntries.joinedAt))
    .limit(perPage)
    .offset(offset);

  const countResult = await db
    .select({ count: sql<number>`count(*)` })
    .from(queueEntries)
    .where(where!);
  const total = Number(countResult[0]?.count ?? 0);

  return { entries, total };
}

export async function getConsultationStats(
  clinicId: number,
  days = 30
): Promise<{
  totalConsultations: number;
  avgDurationMinutes: number;
  presenceRate: number;
  topReasons: { reason: string; count: number }[];
}> {
  const db = await getDb();
  const since = new Date();
  since.setDate(since.getDate() - days);

  const completed = await db
    .select()
    .from(queueEntries)
    .where(
      and(
        eq(queueEntries.clinicId, clinicId),
        inArray(queueEntries.status, ["done", "absent", "canceled"] as const),
        gte(queueEntries.joinedAt, since)
      )
    );

  const doneEntries = completed.filter((e) => e.status === "done");
  const absentEntries = completed.filter((e) => e.status === "absent");
  const totalConsultations = completed.length;

  const durations = doneEntries
    .filter((e) => e.consultationStartedAt && e.consultationEndAt)
    .map(
      (e) => (e.consultationEndAt!.getTime() - e.consultationStartedAt!.getTime()) / 60000
    );
  const avgDurationMinutes =
    durations.length > 0
      ? Math.round(durations.reduce((s, d) => s + d, 0) / durations.length)
      : 0;

  const presenceRate =
    totalConsultations > 0
      ? Math.round(((totalConsultations - absentEntries.length) / totalConsultations) * 100)
      : 100;

  const reasonCounts: Record<string, number> = {};
  completed.forEach((e) => {
    const r = e.visitReason ?? "consultation";
    reasonCounts[r] = (reasonCounts[r] ?? 0) + 1;
  });
  const topReasons = Object.entries(reasonCounts)
    .map(([reason, count]) => ({ reason, count }))
    .sort((a, b) => b.count - a.count);

  return { totalConsultations, avgDurationMinutes, presenceRate, topReasons };
}

// ─── Admin: users management ─────────────────────────────────────────────────
export async function listAllUsers(opts: {
  page?: number;
  perPage?: number;
  role?: "user" | "admin";
  search?: string;
}): Promise<{ users: User[]; total: number }> {
  const db = await getDb();
  const { page = 1, perPage = 20, role, search } = opts;
  const offset = (page - 1) * perPage;

  const conditions = [] as ReturnType<typeof eq>[];
  if (role) conditions.push(eq(users.role, role));
  if (search) {
    const term = `%${search}%`;
    conditions.push(
      or(like(users.email, term), like(users.name, term)) as ReturnType<typeof eq>
    );
  }
  const where = conditions.length > 0 ? and(...conditions) : undefined;

  const [rows, countRows] = await Promise.all([
    where
      ? db.select().from(users).where(where).orderBy(desc(users.createdAt)).limit(perPage).offset(offset)
      : db.select().from(users).orderBy(desc(users.createdAt)).limit(perPage).offset(offset),
    where
      ? db.select({ count: sql<number>`COUNT(*)` }).from(users).where(where)
      : db.select({ count: sql<number>`COUNT(*)` }).from(users),
  ]);

  return { users: rows, total: Number(countRows[0]?.count ?? 0) };
}

export async function setUserRole(userId: number, role: "user" | "admin"): Promise<void> {
  const db = await getDb();
  await db.update(users).set({ role }).where(eq(users.id, userId));
}

export async function setUserDisabled(userId: number, disabled: boolean): Promise<void> {
  const db = await getDb();
  await db.update(users).set({ disabled }).where(eq(users.id, userId));
}

// ─── Admin: aggregate stats ──────────────────────────────────────────────────
export async function getAdminOverview(): Promise<{
  totalUsers: number;
  totalAdmins: number;
  totalDisabled: number;
  totalClinics: number;
  totalActiveClinics: number;
  totalQueueEntriesToday: number;
  totalQueueEntriesAllTime: number;
}> {
  const db = await getDb();
  const startOfDay = new Date();
  startOfDay.setHours(0, 0, 0, 0);

  const [
    [usersCount],
    [adminsCount],
    [disabledCount],
    [clinicsCount],
    [activeClinicsCount],
    [queueTodayCount],
    [queueAllTimeCount],
  ] = await Promise.all([
    db.select({ count: sql<number>`COUNT(*)` }).from(users),
    db.select({ count: sql<number>`COUNT(*)` }).from(users).where(eq(users.role, "admin")),
    db.select({ count: sql<number>`COUNT(*)` }).from(users).where(eq(users.disabled, true)),
    db.select({ count: sql<number>`COUNT(*)` }).from(clinics),
    db.select({ count: sql<number>`COUNT(*)` }).from(clinics).where(eq(clinics.isActive, true)),
    db
      .select({ count: sql<number>`COUNT(*)` })
      .from(queueEntries)
      .where(gte(queueEntries.joinedAt, startOfDay)),
    db.select({ count: sql<number>`COUNT(*)` }).from(queueEntries),
  ]);

  return {
    totalUsers: Number(usersCount?.count ?? 0),
    totalAdmins: Number(adminsCount?.count ?? 0),
    totalDisabled: Number(disabledCount?.count ?? 0),
    totalClinics: Number(clinicsCount?.count ?? 0),
    totalActiveClinics: Number(activeClinicsCount?.count ?? 0),
    totalQueueEntriesToday: Number(queueTodayCount?.count ?? 0),
    totalQueueEntriesAllTime: Number(queueAllTimeCount?.count ?? 0),
  };
}

export async function listAllClinicsWithStats(): Promise<
  Array<{
    id: number;
    name: string;
    ownerId: number;
    ownerEmail: string | null;
    ownerName: string | null;
    isActive: boolean;
    isQueueOpen: boolean;
    patientCountToday: number;
    createdAt: Date;
  }>
> {
  const db = await getDb();
  const startOfDay = new Date();
  startOfDay.setHours(0, 0, 0, 0);

  const allClinics = await db.select().from(clinics).orderBy(desc(clinics.createdAt));
  const ownerIds = Array.from(new Set(allClinics.map((c) => c.userId)));
  const owners = ownerIds.length
    ? await db.select().from(users).where(inArray(users.id, ownerIds))
    : [];
  const ownerById = new Map(owners.map((u) => [u.id, u]));

  const todayEntries = allClinics.length
    ? await db
        .select({
          clinicId: queueEntries.clinicId,
          count: sql<number>`COUNT(*)`,
        })
        .from(queueEntries)
        .where(
          and(
            inArray(queueEntries.clinicId, allClinics.map((c) => c.id)),
            gte(queueEntries.joinedAt, startOfDay)
          )
        )
        .groupBy(queueEntries.clinicId)
    : [];
  const countByClinic = new Map(todayEntries.map((row) => [row.clinicId, Number(row.count ?? 0)]));

  return allClinics.map((c) => {
    const owner = ownerById.get(c.userId);
    return {
      id: c.id,
      name: c.name,
      ownerId: c.userId,
      ownerEmail: owner?.email ?? null,
      ownerName: owner?.name ?? null,
      isActive: c.isActive,
      isQueueOpen: c.isQueueOpen,
      patientCountToday: countByClinic.get(c.id) ?? 0,
      createdAt: c.createdAt,
    };
  });
}

// ─── Clinic members (multi-practitioner) ─────────────────────────────────────
export async function listClinicMembers(clinicId: number): Promise<
  Array<ClinicMember & { email: string | null; name: string | null }>
> {
  const db = await getDb();
  const members = await db
    .select()
    .from(clinicMembers)
    .where(eq(clinicMembers.clinicId, clinicId))
    .orderBy(asc(clinicMembers.createdAt));
  if (members.length === 0) return [];
  const userIds = members.map((m) => m.userId);
  const userRows = await db.select().from(users).where(inArray(users.id, userIds));
  const byId = new Map(userRows.map((u) => [u.id, u]));
  return members.map((m) => {
    const u = byId.get(m.userId);
    return { ...m, email: u?.email ?? null, name: u?.name ?? null };
  });
}

export async function getClinicMember(
  clinicId: number,
  userId: number
): Promise<ClinicMember | null> {
  const db = await getDb();
  const rows = await db
    .select()
    .from(clinicMembers)
    .where(and(eq(clinicMembers.clinicId, clinicId), eq(clinicMembers.userId, userId)))
    .limit(1);
  return rows[0] ?? null;
}

export async function addClinicMember(data: InsertClinicMember): Promise<ClinicMember> {
  const db = await getDb();
  const [result] = await db.insert(clinicMembers).values(data);
  const insertId = (result as { insertId: number }).insertId;
  const rows = await db.select().from(clinicMembers).where(eq(clinicMembers.id, insertId)).limit(1);
  if (!rows[0]) throw new Error("Failed to add clinic member");
  return rows[0];
}

export async function removeClinicMember(clinicId: number, memberId: number): Promise<void> {
  const db = await getDb();
  await db
    .delete(clinicMembers)
    .where(and(eq(clinicMembers.id, memberId), eq(clinicMembers.clinicId, clinicId)));
}

export async function updateClinicMember(
  memberId: number,
  patch: Partial<InsertClinicMember>
): Promise<void> {
  const db = await getDb();
  await db.update(clinicMembers).set(patch).where(eq(clinicMembers.id, memberId));
}

// ─── Advanced analytics ──────────────────────────────────────────────────────
export async function getAdvancedAnalytics(
  userId: number,
  options: { days?: number; clinicId?: number } = {}
): Promise<{
  byHour: number[];
  byDayOfWeek: number[];
  noShowRate: number;
  totalServed: number;
  totalAbsent: number;
  totalJoined: number;
  busiestDayOfWeek: number;
  peakHour: number;
  avgWaitByDay: Array<{ date: string; avgWaitMinutes: number; count: number }>;
}> {
  const events = await getAnalytics(userId, options);

  const byHour = new Array(24).fill(0);
  const byDayOfWeek = new Array(7).fill(0);
  let totalServed = 0;
  let totalAbsent = 0;
  let totalJoined = 0;

  // wait-time aggregation by ISO day (YYYY-MM-DD)
  const waitByDay: Map<string, { total: number; count: number }> = new Map();

  for (const ev of events) {
    if (typeof ev.hourOfDay === "number") byHour[ev.hourOfDay] += 1;
    if (typeof ev.dayOfWeek === "number") byDayOfWeek[ev.dayOfWeek] += 1;
    if (ev.eventType === "patient_joined") totalJoined += 1;
    if (ev.eventType === "patient_done") totalServed += 1;
    if (ev.eventType === "patient_absent") totalAbsent += 1;

    if (typeof ev.waitMinutes === "number" && ev.eventType === "patient_called") {
      const d = ev.createdAt instanceof Date ? ev.createdAt : new Date(ev.createdAt);
      const key = d.toISOString().slice(0, 10);
      const cur = waitByDay.get(key) ?? { total: 0, count: 0 };
      cur.total += ev.waitMinutes;
      cur.count += 1;
      waitByDay.set(key, cur);
    }
  }

  const totalCompleted = totalServed + totalAbsent;
  const noShowRate = totalCompleted > 0 ? totalAbsent / totalCompleted : 0;

  const peakHour = byHour.indexOf(Math.max(...byHour));
  const busiestDayOfWeek = byDayOfWeek.indexOf(Math.max(...byDayOfWeek));

  const avgWaitByDay = Array.from(waitByDay.entries())
    .map(([date, { total, count }]) => ({
      date,
      avgWaitMinutes: count ? Math.round(total / count) : 0,
      count,
    }))
    .sort((a, b) => (a.date < b.date ? -1 : 1));

  return {
    byHour,
    byDayOfWeek,
    noShowRate,
    totalServed,
    totalAbsent,
    totalJoined,
    busiestDayOfWeek,
    peakHour,
    avgWaitByDay,
  };
}

// ─── App Config (intégrations dynamiques) ────────────────────────────────────
// Cache en mémoire pour éviter une requête DB par appel. Invalidé sur écriture.
const configCache = new Map<string, string>();
let configCacheLoaded = false;

async function loadConfigCache(): Promise<void> {
  const db = await getDb();
  const rows = await db.select().from(appConfig);
  configCache.clear();
  for (const row of rows) {
    configCache.set(row.key, row.value);
  }
  configCacheLoaded = true;
}

export async function getConfigValue(key: string): Promise<string | null> {
  if (!configCacheLoaded) {
    try {
      await loadConfigCache();
    } catch (err) {
      childLogger("config").warn({ err, key }, "config cache load failed");
      return null;
    }
  }
  return configCache.get(key) ?? null;
}

export async function listAllConfig(): Promise<AppConfig[]> {
  const db = await getDb();
  const rows = await db.select().from(appConfig).orderBy(asc(appConfig.category), asc(appConfig.key));
  return rows;
}

export async function setConfigValue(
  key: string,
  value: string,
  category: string,
  isSecret: boolean,
  description?: string | null
): Promise<void> {
  const db = await getDb();
  const existing = await db
    .select()
    .from(appConfig)
    .where(eq(appConfig.key, key))
    .limit(1);
  if (existing.length > 0) {
    await db
      .update(appConfig)
      .set({
        value,
        category,
        isSecret: isSecret ? 1 : 0,
        description: description ?? existing[0].description,
      })
      .where(eq(appConfig.key, key));
  } else {
    await db.insert(appConfig).values({
      key,
      value,
      category,
      isSecret: isSecret ? 1 : 0,
      description: description ?? null,
    });
  }
  configCache.set(key, value);
}

export async function deleteConfigValue(key: string): Promise<void> {
  const db = await getDb();
  await db.delete(appConfig).where(eq(appConfig.key, key));
  configCache.delete(key);
}

export function invalidateConfigCache(): void {
  configCache.clear();
  configCacheLoaded = false;
}