import {
  pgTable,
  serial,
  integer,
  varchar,
  text,
  timestamp,
  date,
  time,
  jsonb,
  boolean,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { usersTable } from "./users";

export const executiveMeetingsTable = pgTable(
  "executive_meetings",
  {
    id: serial("id").primaryKey(),
    dailyNumber: integer("daily_number").notNull(),
    // titleAr/En hold sanitized rich-text HTML (Tiptap output) so they need
    // unbounded length. Plain-text rows from before the widening still
    // render correctly because they contain no HTML tags.
    titleAr: text("title_ar").notNull(),
    titleEn: text("title_en").notNull().default(""),
    meetingDate: date("meeting_date").notNull(),
    startTime: time("start_time", { withTimezone: false }),
    endTime: time("end_time", { withTimezone: false }),
    location: varchar("location", { length: 300 }),
    meetingUrl: text("meeting_url"),
    platform: varchar("platform", { length: 32 }).notNull().default("none"),
    status: varchar("status", { length: 32 }).notNull().default("scheduled"),
    isHighlighted: integer("is_highlighted").notNull().default(0),
    notes: text("notes"),
    attachments: jsonb("attachments").default([]),
    // Optional cell-merge overlay for the schedule grid. When set, the
    // schedule renders one merged cell spanning columns
    // [mergeStartColumn..mergeEndColumn] and shows mergeText (sanitized
    // HTML) instead of the original column values. The original
    // titleAr/En + attendees + start/endTime stay untouched in the DB,
    // so clearing the merge restores the original cells exactly.
    // Allowed values for the column ids match the frontend ColumnId
    // enum: "number" | "meeting" | "attendees" | "time".
    mergeStartColumn: varchar("merge_start_column", { length: 32 }),
    mergeEndColumn: varchar("merge_end_column", { length: 32 }),
    mergeText: text("merge_text"),
    createdBy: integer("created_by").references(() => usersTable.id, {
      onDelete: "set null",
    }),
    updatedBy: integer("updated_by").references(() => usersTable.id, {
      onDelete: "set null",
    }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (t) => ({
    dateIdx: index("executive_meetings_date_idx").on(t.meetingDate),
    dateNumberUnique: uniqueIndex("executive_meetings_date_number_unique").on(
      t.meetingDate,
      t.dailyNumber,
    ),
  }),
);

export const executiveMeetingAttendeesTable = pgTable(
  "executive_meeting_attendees",
  {
    id: serial("id").primaryKey(),
    meetingId: integer("meeting_id")
      .notNull()
      .references(() => executiveMeetingsTable.id, { onDelete: "cascade" }),
    // attendee name holds sanitized rich-text HTML so it needs unbounded
    // length. Plain-text rows still render correctly (no tags = no parse).
    // For `kind = "subheading"` rows, this column stores the user-written
    // section label (free text, single value rendered as-is in AR and EN).
    name: text("name").notNull(),
    title: varchar("title", { length: 200 }),
    attendanceType: varchar("attendance_type", { length: 32 })
      .notNull()
      .default("internal"),
    sortOrder: integer("sort_order").notNull().default(0),
    // "person"      — a real attendee row (default; matches all pre-existing
    //                 data after backfill).
    // "subheading"  — a user-defined section label inserted between attendees
    //                 inside the same attendance-type group. Numbering, count,
    //                 and virtual-platform extraction MUST ignore these rows.
    kind: varchar("kind", { length: 16 }).notNull().default("person"),
  },
  (t) => ({
    meetingIdx: index("executive_meeting_attendees_meeting_idx").on(t.meetingId),
  }),
);

export const executiveMeetingRequestsTable = pgTable(
  "executive_meeting_requests",
  {
    id: serial("id").primaryKey(),
    meetingId: integer("meeting_id").references(
      () => executiveMeetingsTable.id,
      { onDelete: "cascade" },
    ),
    requestedBy: integer("requested_by").references(() => usersTable.id, {
      onDelete: "set null",
    }),
    requestType: varchar("request_type", { length: 64 }).notNull(),
    requestDetails: jsonb("request_details"),
    status: varchar("status", { length: 32 }).notNull().default("new"),
    reviewedBy: integer("reviewed_by").references(() => usersTable.id, {
      onDelete: "set null",
    }),
    reviewDecision: varchar("review_decision", { length: 32 }),
    reviewNotes: text("review_notes"),
    assignedTo: integer("assigned_to").references(() => usersTable.id, {
      onDelete: "set null",
    }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
);

export const executiveMeetingTasksTable = pgTable("executive_meeting_tasks", {
  id: serial("id").primaryKey(),
  requestId: integer("request_id").references(() => executiveMeetingRequestsTable.id, {
    onDelete: "cascade",
  }),
  meetingId: integer("meeting_id").references(() => executiveMeetingsTable.id, {
    onDelete: "cascade",
  }),
  assignedTo: integer("assigned_to").references(() => usersTable.id, {
    onDelete: "set null",
  }),
  taskType: varchar("task_type", { length: 64 }).notNull(),
  status: varchar("status", { length: 32 }).notNull().default("pending"),
  dueAt: timestamp("due_at", { withTimezone: true }),
  completedAt: timestamp("completed_at", { withTimezone: true }),
  notes: text("notes"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
});

export const executiveMeetingNotificationsTable = pgTable(
  "executive_meeting_notifications",
  {
    id: serial("id").primaryKey(),
    meetingId: integer("meeting_id").references(() => executiveMeetingsTable.id, {
      onDelete: "cascade",
    }),
    userId: integer("user_id").references(() => usersTable.id, { onDelete: "cascade" }),
    notificationType: varchar("notification_type", { length: 64 }).notNull(),
    scheduledAt: timestamp("scheduled_at", { withTimezone: true }),
    sentAt: timestamp("sent_at", { withTimezone: true }),
    status: varchar("status", { length: 32 }).notNull().default("pending"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
);

/**
 * Per-user notification preferences for the Executive Meetings module.
 *
 * Each row toggles a single (userId, notificationType) pair on or off
 * for the in-app bell channel and the email channel independently.
 *
 * Default behaviour when no row exists: BOTH channels ON. This keeps the
 * legacy "fan out to everyone, every time" semantics intact for users
 * who never visit the preferences UI. Only an explicit opt-out (row
 * present + flag set to false) suppresses delivery.
 *
 * notificationType mirrors the keys passed to
 * `recordExecutiveMeetingNotifications` / `sendExecutiveMeetingEmail`:
 *   - meeting_created
 *   - request_submitted
 *   - request_approved
 *   - request_rejected
 *   - request_needs_edit
 *   - task_assigned
 *   - task_completed
 */
export const executiveMeetingNotificationPrefsTable = pgTable(
  "executive_meeting_notification_prefs",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    notificationType: varchar("notification_type", { length: 64 }).notNull(),
    inApp: boolean("in_app").notNull().default(true),
    email: boolean("email").notNull().default(true),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (t) => ({
    userTypeUnique: uniqueIndex(
      "executive_meeting_notification_prefs_user_type_unique",
    ).on(t.userId, t.notificationType),
  }),
);

export const executiveMeetingAuditLogsTable = pgTable(
  "executive_meeting_audit_logs",
  {
    id: serial("id").primaryKey(),
    action: varchar("action", { length: 100 }).notNull(),
    entityType: varchar("entity_type", { length: 50 }).notNull(),
    entityId: integer("entity_id"),
    oldValue: jsonb("old_value"),
    newValue: jsonb("new_value"),
    performedBy: integer("performed_by").references(() => usersTable.id, {
      onDelete: "set null",
    }),
    performedAt: timestamp("performed_at", { withTimezone: true }).notNull().defaultNow(),
  },
);

export const executiveMeetingPdfArchivesTable = pgTable(
  "executive_meeting_pdf_archives",
  {
    id: serial("id").primaryKey(),
    archiveDate: date("archive_date").notNull(),
    // `filePath` doubles as the storage URL for the archived PDF. When the
    // PDF was uploaded to object storage the value is a `/objects/<id>`
    // path that resolves through GET /api/storage/objects/*. Older rows
    // (browser-print snapshots) use the synthetic `print:<date>` form.
    filePath: text("file_path").notNull(),
    version: integer("version").notNull().default(1),
    // Bytes of the generated PDF on disk. Nullable because legacy rows
    // (created before the server-side generator) never had a real file.
    byteSize: integer("byte_size"),
    generatedBy: integer("generated_by").references(() => usersTable.id, {
      onDelete: "set null",
    }),
    generatedAt: timestamp("generated_at", { withTimezone: true }).notNull().defaultNow(),
  },
);

export const executiveMeetingFontSettingsTable = pgTable(
  "executive_meeting_font_settings",
  {
    id: serial("id").primaryKey(),
    scope: varchar("scope", { length: 16 }).notNull().default("global"),
    userId: integer("user_id").references(() => usersTable.id, { onDelete: "cascade" }),
    fontFamily: varchar("font_family", { length: 100 }).notNull().default("system"),
    fontSize: integer("font_size").notNull().default(14),
    fontWeight: varchar("font_weight", { length: 16 }).notNull().default("regular"),
    alignment: varchar("alignment", { length: 16 }).notNull().default("start"),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
);

export type ExecutiveMeeting = typeof executiveMeetingsTable.$inferSelect;
export type ExecutiveMeetingAttendee = typeof executiveMeetingAttendeesTable.$inferSelect;
export type ExecutiveMeetingRequest = typeof executiveMeetingRequestsTable.$inferSelect;
export type ExecutiveMeetingTask = typeof executiveMeetingTasksTable.$inferSelect;
