import { asc, count, eq } from "drizzle-orm";
import type { ChatGPTUser } from "../app/chatgpt-auth";
import { getDb } from ".";
import { members, reminderRules } from "./schema";

export type PortalProfile = typeof members.$inferSelect;

function splitDisplayName(displayName: string) {
  const cleaned = displayName.includes("@") ? "Membre du bureau" : displayName.trim();
  const parts = cleaned.split(/\s+/).filter(Boolean);
  return {
    firstName: parts[0] ?? "Membre",
    lastName: parts.slice(1).join(" ") || "du bureau",
  };
}

export async function findProfileByEmail(email: string) {
  const db = await getDb();
  const [profile] = await db
    .select()
    .from(members)
    .where(eq(members.email, email.trim().toLowerCase()))
    .limit(1);
  return profile ?? null;
}

export async function getOrBootstrapOwner(user: ChatGPTUser) {
  const email = user.email.trim().toLowerCase();
  const existing = await findProfileByEmail(email);
  if (existing) return existing;

  const db = await getDb();
  const [{ total }] = await db.select({ total: count() }).from(members);
  if (total > 0) return null;

  const { firstName, lastName } = splitDisplayName(user.fullName ?? user.displayName);
  try {
    const [profile] = await db
      .insert(members)
      .values({
        lotReference: "BUREAU-001",
        firstName,
        lastName,
        email,
        avenue: "Bureau ASL",
        role: "bureau",
        accessStatus: "active",
        notes: "Compte administrateur initial créé lors de l’activation de la base.",
      })
      .returning();
    await ensureDefaultReminderRules();
    return profile;
  } catch {
    return findProfileByEmail(email);
  }
}

export async function ensureDefaultReminderRules() {
  const db = await getDb();
  const existing = await db
    .select({ label: reminderRules.label })
    .from(reminderRules)
    .orderBy(asc(reminderRules.id));
  const labels = new Set(existing.map((item) => item.label));
  const defaults: Array<typeof reminderRules.$inferInsert> = [
    { label: "Rappel 30 jours avant", triggerType: "before_due", daysOffset: 30, channel: "in_app", audience: "unpaid", enabled: true },
    { label: "Rappel 7 jours avant", triggerType: "before_due", daysOffset: 7, channel: "in_app", audience: "unpaid", enabled: true },
    { label: "Relance 7 jours après", triggerType: "after_due", daysOffset: 7, channel: "in_app", audience: "overdue", enabled: true },
    { label: "Relance e-mail après échéance", triggerType: "after_due", daysOffset: 7, channel: "email", audience: "overdue", enabled: false },
  ];
  const missing = defaults.filter((rule) => !labels.has(rule.label));
  if (missing.length) await db.insert(reminderRules).values(missing);
  return db.select().from(reminderRules).orderBy(asc(reminderRules.id));
}