import {
  int,
  mysqlEnum,
  mysqlTable,
  text,
  timestamp,
  tinyint,
  varchar,
  boolean,
  json,
  index,
  uniqueIndex,
} from "drizzle-orm/mysql-core";

// ─── Users (médecins) ────────────────────────────────────────────────────────
export const users = mysqlTable(
  "users",
  {
    id: int("id").autoincrement().primaryKey(),
    email: varchar("email", { length: 320 }).notNull(),
    passwordHash: varchar("passwordHash", { length: 255 }).notNull(),
    name: text("name"),
    openId: varchar("openId", { length: 64 }),
    loginMethod: varchar("loginMethod", { length: 64 }).default("password").notNull(),
    role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
    disabled: boolean("disabled").default(false).notNull(),
    resetToken: varchar("resetToken", { length: 255 }),
    resetTokenExpiry: timestamp("resetTokenExpiry"),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
    lastSignedIn: timestamp("lastSignedIn").defaultNow().notNull(),
  },
  (table) => ({
    emailIdx: uniqueIndex("users_email_idx").on(table.email),
    openIdIdx: index("users_openId_idx").on(table.openId),
  })
);

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

// ─── Subscriptions ───────────────────────────────────────────────────────────
export const subscriptions = mysqlTable(
  "subscriptions",
  {
    id: int("id").autoincrement().primaryKey(),
    userId: int("userId").notNull(),
    stripeCustomerId: varchar("stripeCustomerId", { length: 128 }),
    stripeSubscriptionId: varchar("stripeSubscriptionId", { length: 128 }),
    stripePriceId: varchar("stripePriceId", { length: 128 }),
    plan: mysqlEnum("plan", ["trial", "basic", "pro"]).default("trial").notNull(),
    status: mysqlEnum("status", ["trialing", "active", "past_due", "canceled", "expired"])
      .default("trialing")
      .notNull(),
    trialStartedAt: timestamp("trialStartedAt").defaultNow().notNull(),
    trialEndsAt: timestamp("trialEndsAt").notNull(),
    currentPeriodStart: timestamp("currentPeriodStart"),
    currentPeriodEnd: timestamp("currentPeriodEnd"),
    canceledAt: timestamp("canceledAt"),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  },
  (table) => ({
    userIdx: index("subscriptions_userId_idx").on(table.userId),
  })
);

export type Subscription = typeof subscriptions.$inferSelect;
export type InsertSubscription = typeof subscriptions.$inferInsert;

// ─── Clinics (cabinets médicaux) ─────────────────────────────────────────────
export const clinics = mysqlTable(
  "clinics",
  {
    id: int("id").autoincrement().primaryKey(),
    userId: int("userId").notNull(),
    name: varchar("name", { length: 255 }).notNull(),
    address: text("address"),
    phone: varchar("phone", { length: 32 }),
    color: varchar("color", { length: 16 }).default("#10b981"),
    // Numéro WhatsApp Business du cabinet
    whatsappPhone: varchar("whatsappPhone", { length: 32 }),
    isActive: boolean("isActive").default(true).notNull(),
    // QR code token rotatif anti-triche
    qrToken: varchar("qrToken", { length: 64 }).notNull(),
    qrTokenExpiresAt: timestamp("qrTokenExpiresAt"),
    qrRotationMinutes: int("qrRotationMinutes").default(1440),
    // Paramètres file d'attente
    avgConsultationMinutes: int("avgConsultationMinutes").default(15),
    maxQueueSize: int("maxQueueSize").default(50),
    isQueueOpen: boolean("isQueueOpen").default(false).notNull(),
    currentTicketNumber: int("currentTicketNumber").default(0).notNull(),
    // Timer absent automatique : marque absent après N minutes sans réponse (0 = désactivé)
    autoAbsentMinutes: int("autoAbsentMinutes").default(0).notNull(),
    // Paramètres enrichis cabinet
    welcomeMessage: text("welcomeMessage"),
    // Horaires d'ouverture JSON : { monday: { open, close, closed }, ... }
    openingHours: json("openingHours"),
    // Langue de l'interface patient : "fr" | "en" | "ar" | "pt" | "es"
    patientLanguage: varchar("patientLanguage", { length: 8 }).default("fr"),
    // Templates WhatsApp personnalisables (null = template par défaut)
    whatsappTemplateJoined: text("whatsappTemplateJoined"),
    whatsappTemplateSoon: text("whatsappTemplateSoon"),
    whatsappTemplateCalled: text("whatsappTemplateCalled"),
    whatsappTemplateWithdrawn: text("whatsappTemplateWithdrawn"),
    // Activation des notifications SMS Twilio (opt-in par cabinet)
    // Migration: ALTER TABLE clinics ADD COLUMN smsEnabled TINYINT(1) NOT NULL DEFAULT 0
    smsEnabled: boolean("smsEnabled").default(false).notNull(),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  },
  (table) => ({
    userIdx: index("clinics_userId_idx").on(table.userId),
    qrTokenIdx: uniqueIndex("clinics_qrToken_idx").on(table.qrToken),
  })
);

export type Clinic = typeof clinics.$inferSelect;
export type InsertClinic = typeof clinics.$inferInsert;

// ─── Queue Entries (patients en file) ────────────────────────────────────────
export const queueEntries = mysqlTable(
  "queue_entries",
  {
    id: int("id").autoincrement().primaryKey(),
    clinicId: int("clinicId").notNull(),
    ticketNumber: int("ticketNumber").notNull(),
    // Identifiant de session anonyme du patient
    patientToken: varchar("patientToken", { length: 64 }).notNull(),
    patientName: varchar("patientName", { length: 128 }),
    patientPhone: varchar("patientPhone", { length: 32 }),
    status: mysqlEnum("status", [
      "waiting",
      "called",
      "in_consultation",
      "done",
      "absent",
      "canceled",
    ])
      .default("waiting")
      .notNull(),
    position: int("position").notNull(),
    joinedAt: timestamp("joinedAt").defaultNow().notNull(),
    calledAt: timestamp("calledAt"),
    consultationStartAt: timestamp("consultationStartAt"),
    consultationEndAt: timestamp("consultationEndAt"),
    estimatedWaitMinutes: int("estimatedWaitMinutes"),
    notificationSent: boolean("notificationSent").default(false).notNull(),
    // Motif de visite patient
    visitReason: mysqlEnum("visitReason", [
      "consultation",
      "urgence",
      "certificat_scolaire",
      "certificat_sportif",
      "arret_travail",
      "administratif",
      "autre",
    ]).default("consultation"),
    visitNote: text("visitNote"),
    // Praticien assigné (multi-praticiens par cabinet)
    practitionerId: int("practitionerId"),
    // Timing consultation supplémentaire (pour stats)
    consultationStartedAt: timestamp("consultationStartedAt"),
    // Notifications WhatsApp
    whatsappPhone: varchar("whatsappPhone", { length: 32 }),
    whatsappSentJoined: boolean("whatsappSentJoined").default(false).notNull(),
    whatsappSentSoon: boolean("whatsappSentSoon").default(false).notNull(),
    whatsappSentCalled: boolean("whatsappSentCalled").default(false).notNull(),
    // Pour l'impression de ticket
    isPrinted: boolean("isPrinted").default(false).notNull(),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  },
  (table) => ({
    clinicIdx: index("queue_clinicId_idx").on(table.clinicId),
    statusIdx: index("queue_status_idx").on(table.status),
    tokenIdx: index("queue_patientToken_idx").on(table.patientToken),
  })
);

export type QueueEntry = typeof queueEntries.$inferSelect;
export type InsertQueueEntry = typeof queueEntries.$inferInsert;

// ─── Analytics Events ─────────────────────────────────────────────────────────
export const analyticsEvents = mysqlTable(
  "analytics_events",
  {
    id: int("id").autoincrement().primaryKey(),
    clinicId: int("clinicId").notNull(),
    eventType: mysqlEnum("eventType", [
      "patient_joined",
      "patient_called",
      "patient_done",
      "patient_absent",
      "queue_opened",
      "queue_closed",
    ]).notNull(),
    ticketNumber: int("ticketNumber"),
    waitMinutes: int("waitMinutes"),
    consultationMinutes: int("consultationMinutes"),
    queueSizeAtEvent: int("queueSizeAtEvent"),
    hourOfDay: int("hourOfDay"),
    dayOfWeek: int("dayOfWeek"),
    metadata: json("metadata"),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
  },
  (table) => ({
    clinicIdx: index("analytics_clinicId_idx").on(table.clinicId),
    createdIdx: index("analytics_createdAt_idx").on(table.createdAt),
  })
);

export type AnalyticsEvent = typeof analyticsEvents.$inferSelect;
export type InsertAnalyticsEvent = typeof analyticsEvents.$inferInsert;

// ─── WhatsApp Country Codes ──────────────────────────────────────────────────
// Indicatifs pays disponibles pour les notifications WhatsApp patients
export const whatsappCountryCodes = mysqlTable(
  "whatsapp_country_codes",
  {
    id: int("id").autoincrement().primaryKey(),
    code: varchar("code", { length: 4 }).notNull(),
    dialCode: varchar("dialCode", { length: 8 }).notNull(),
    nameFr: varchar("nameFr", { length: 128 }).notNull(),
    flag: varchar("flag", { length: 8 }).notNull(),
    enabled: boolean("enabled").default(false).notNull(),
    sortOrder: int("sortOrder").default(100).notNull(),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  },
  (table) => ({
    codeIdx: uniqueIndex("whatsapp_country_codes_code_idx").on(table.code),
  })
);

export type WhatsappCountryCode = typeof whatsappCountryCodes.$inferSelect;
export type InsertWhatsappCountryCode = typeof whatsappCountryCodes.$inferInsert;

// ─── WhatsApp Logs ────────────────────────────────────────────────────────────
// Trace chaque message WhatsApp envoyé par cabinet
export const whatsappLogs = mysqlTable(
  "whatsapp_logs",
  {
    id: int("id").autoincrement().primaryKey(),
    clinicId: int("clinicId").notNull(),
    phoneMasked: varchar("phoneMasked", { length: 32 }).notNull(),
    messageType: mysqlEnum("messageType", ["joined", "soon", "called", "withdrawn", "test"]).notNull(),
    status: mysqlEnum("status", ["sent", "failed"]).notNull(),
    errorMessage: text("errorMessage"),
    messagePreview: varchar("messagePreview", { length: 120 }),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
  },
  (table) => ({
    clinicIdx: index("whatsapp_logs_clinicId_idx").on(table.clinicId),
    createdIdx: index("whatsapp_logs_createdAt_idx").on(table.createdAt),
  })
);

export type WhatsappLog = typeof whatsappLogs.$inferSelect;
export type InsertWhatsappLog = typeof whatsappLogs.$inferInsert;

// ─── Clinic Members (multi-praticiens par cabinet) ───────────────────────────
export const clinicMembers = mysqlTable(
  "clinic_members",
  {
    id: int("id").autoincrement().primaryKey(),
    clinicId: int("clinicId").notNull(),
    userId: int("userId").notNull(),
    role: mysqlEnum("role", ["owner", "practitioner"]).default("practitioner").notNull(),
    color: varchar("color", { length: 7 }).default("#10b981").notNull(),
    displayName: varchar("displayName", { length: 128 }),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
  },
  (table) => ({
    clinicIdx: index("clinic_members_clinicId_idx").on(table.clinicId),
    userIdx: index("clinic_members_userId_idx").on(table.userId),
    uniqueMember: uniqueIndex("clinic_members_unique_idx").on(table.clinicId, table.userId),
  })
);

export type ClinicMember = typeof clinicMembers.$inferSelect;
export type InsertClinicMember = typeof clinicMembers.$inferInsert;

// ─── App Config (intégrations & secrets dynamiques) ──────────────────────────
// Permet de configurer Stripe / Twilio / autres services depuis l'UI admin
// sans redéploiement. Les valeurs marquées comme secrètes sont masquées
// côté API et ne sont jamais renvoyées en clair (sauf via les services
// internes qui les consomment).
export const appConfig = mysqlTable(
  "app_config",
  {
    id: int("id").primaryKey().autoincrement(),
    key: varchar("key", { length: 100 }).notNull(),
    value: text("value").notNull(),
    isSecret: tinyint("is_secret").default(0).notNull(),
    category: varchar("category", { length: 50 }).notNull(),
    description: varchar("description", { length: 255 }),
    updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull(),
  },
  (table) => ({
    keyIdx: uniqueIndex("app_config_key_idx").on(table.key),
    categoryIdx: index("app_config_category_idx").on(table.category),
  })
);

export type AppConfig = typeof appConfig.$inferSelect;
export type InsertAppConfig = typeof appConfig.$inferInsert;