import { Router, type IRouter } from "express";
import {
  eq,
  asc,
  desc,
  inArray,
  and,
  sql,
  gte,
  lt,
  isNull,
  or,
  type SQL,
} from "drizzle-orm";
import { z, type ZodType } from "zod";
import { db } from "@workspace/db";
import {
  executiveMeetingsTable,
  executiveMeetingAttendeesTable,
  executiveMeetingRequestsTable,
  executiveMeetingTasksTable,
  executiveMeetingNotificationsTable,
  executiveMeetingNotificationPrefsTable,
  executiveMeetingAuditLogsTable,
  executiveMeetingPdfArchivesTable,
  executiveMeetingFontSettingsTable,
  rolesTable,
  usersTable,
  type ExecutiveMeetingAttendee,
} from "@workspace/db";
import {
  requireAuth,
  requireExecutiveAccess,
  getEffectiveRoleIds,
} from "../middlewares/auth";
import { sanitizePlainTextOrNull, sanitizeRichText } from "../lib/sanitize";
import {
  emitExecutiveMeetingsDayChanged,
  emitExecutiveMeetingsDaysChanged,
} from "../lib/realtime";
import { renderSchedulePdf, type PdfFontPrefs } from "../lib/pdf-renderer";
import { ObjectStorageService } from "../lib/objectStorage";
import {
  recordExecutiveMeetingNotifications,
  broadcastExecutiveMeetingNotifications,
  getUserIdsForRoleNames,
  sendExecutiveMeetingEmail,
  getUserDisplay as getUserDisplayForNotify,
  EXECUTIVE_MEETING_NOTIFICATION_TYPES,
} from "../lib/executive-meeting-notify";
import { ExecutiveMeetingsReorderBody } from "@workspace/api-zod";
import type { Request, Response, NextFunction } from "express";

const router: IRouter = Router();

router.param("id", (req, res, next, value) => {
  if (!/^\d+$/.test(String(value))) {
    next("route");
    return;
  }
  next();
});

// ---------- Constants ----------

const PLATFORMS = ["none", "webex", "teams", "zoom", "other"] as const;
const STATUSES = ["scheduled", "cancelled", "completed", "postponed"] as const;
const ATTENDANCE_TYPES = ["internal", "virtual", "external"] as const;
// "person" is the default for backwards compatibility — every existing row
// is backfilled to this kind. "subheading" rows are user-defined section
// labels that live in the same attendees array as persons but must be
// excluded from numbering, attendee counts, and virtual-platform-name
// extraction. They share `attendance_type` and `sort_order` with persons
// so a subheading "belongs" to exactly one attendance-type group.
const ATTENDEE_KINDS = ["person", "subheading"] as const;
// Schedule column ids that the merge-cells overlay can span. Kept in
// sync with the frontend ColumnId enum in
// artifacts/tx-os/src/pages/executive-meetings.tsx.
const MERGE_COLUMNS = ["number", "meeting", "attendees", "time"] as const;
const MERGE_COLUMN_INDEX: Record<(typeof MERGE_COLUMNS)[number], number> = {
  number: 0,
  meeting: 1,
  attendees: 2,
  time: 3,
};
const REQUEST_TYPES = [
  "create",
  "edit",
  "delete",
  "reschedule",
  "add_attendee",
  "remove_attendee",
  "change_location",
  "cancel_meeting",
  "note",
  "highlight",
  "unhighlight",
  "other",
] as const;
const REQUEST_REVIEW_STATUSES = [
  "approved",
  "rejected",
  "needs_edit",
] as const;
const TASK_STATUSES = ["pending", "in_progress", "completed", "cancelled"] as const;
const FONT_FAMILIES = [
  "system",
  "Cairo",
  "Tajawal",
  "Noto Naskh Arabic",
  "Amiri",
] as const;
const FONT_WEIGHTS = ["regular", "bold"] as const;
const FONT_ALIGNMENTS = ["start", "center"] as const;
const FONT_SIZE_MIN = 12;
const FONT_SIZE_MAX = 22;

const MUTATE_ROLES = [
  "admin",
  "executive_office_manager",
  "executive_coord_lead",
] as const;
const APPROVE_ROLES = ["admin", "executive_office_manager"] as const;
const REQUEST_ROLES = [
  "admin",
  "executive_ceo",
  "executive_office_manager",
  "executive_coord_lead",
  "executive_coordinator",
] as const;
const ADMIN_AUDIT_ROLES = [
  "admin",
  "executive_office_manager",
  "executive_coord_lead",
] as const;
const TASK_VIEW_ROLES = [
  "admin",
  "executive_office_manager",
  "executive_coord_lead",
  "executive_coordinator",
] as const;
const TASK_BROAD_VIEW_ROLES = [
  "admin",
  "executive_office_manager",
  "executive_coord_lead",
] as const;
const READ_ROLES = [
  "admin",
  "executive_ceo",
  "executive_office_manager",
  "executive_coord_lead",
  "executive_coordinator",
  "executive_viewer",
] as const;

// ---------- Role helpers (finer-grained, layered on top of requireExecutiveAccess) ----------

async function getRoleNamesForUser(userId: number): Promise<Set<string>> {
  const ids = await getEffectiveRoleIds(userId);
  if (ids.length === 0) return new Set();
  const rows = await db
    .select({ name: rolesTable.name })
    .from(rolesTable)
    .where(inArray(rolesTable.id, ids));
  return new Set(rows.map((r) => r.name));
}

function makeRequireRoles(allowed: ReadonlyArray<string>) {
  return async function (
    req: Request,
    res: Response,
    next: NextFunction,
  ): Promise<void> {
    if (!req.session.userId) {
      res.status(401).json({ error: "Unauthorized", code: "unauthorized" });
      return;
    }
    const names = await getRoleNamesForUser(req.session.userId);
    const ok = allowed.some((r) => names.has(r));
    if (!ok) {
      res.status(403).json({ error: "Forbidden", code: "forbidden" });
      return;
    }
    next();
  };
}

const requireMutate = makeRequireRoles(MUTATE_ROLES);
const requireApprove = makeRequireRoles(APPROVE_ROLES);
const requireRequest = makeRequireRoles(REQUEST_ROLES);
const requireAdminAudit = makeRequireRoles(ADMIN_AUDIT_ROLES);
const requireTaskView = makeRequireRoles(TASK_VIEW_ROLES);

// ---------- Zod schemas ----------

const dateSchema = z.string().regex(/^\d{4}-\d{2}-\d{2}$/, "expected YYYY-MM-DD");
const timeSchema = z
  .string()
  .regex(/^\d{2}:\d{2}(:\d{2})?$/, "expected HH:MM");
const positiveIntSchema = z.number().int().positive();

// titleAr/En and attendee.name accept sanitized rich-text HTML (Tiptap
// output). The byte count grows quickly with inline <span style=...> wrappers,
// so we allow up to ~10k bytes for titles and ~5k for attendee names. The
// sanitizer strips disallowed tags/attrs before persistence.
const attendeeSchema = z.object({
  // For persons this is sanitized rich-text HTML (Tiptap output).
  // For subheadings this is the user's free-text label, also passed
  // through sanitizeRichText to strip any sneaky markup. Both kinds
  // require at least one non-whitespace character; an empty string is
  // a delete-row gesture in the inline editor and must never reach the DB.
  name: z.string().trim().min(1).max(5000),
  title: z.string().trim().max(200).nullable().optional(),
  attendanceType: z.enum(ATTENDANCE_TYPES).default("internal"),
  sortOrder: z.number().int().min(0).optional(),
  kind: z.enum(ATTENDEE_KINDS).default("person"),
});

const meetingBaseFields = {
  titleAr: z.string().trim().min(1).max(10000),
  titleEn: z.string().trim().min(1).max(10000),
  meetingDate: dateSchema,
  dailyNumber: positiveIntSchema.max(1000).nullable().optional(),
  startTime: timeSchema.nullable().optional(),
  endTime: timeSchema.nullable().optional(),
  location: z.string().trim().max(500).nullable().optional(),
  meetingUrl: z.string().trim().max(2000).nullable().optional(),
  platform: z.enum(PLATFORMS).optional(),
  status: z.enum(STATUSES).optional(),
  isHighlighted: z
    .union([z.boolean(), z.number()])
    .transform((v) => (v ? 1 : 0))
    .optional(),
  notes: z.string().trim().max(5000).nullable().optional(),
} as const;

const meetingCreateSchema = z
  .object({
    ...meetingBaseFields,
    attendees: z.array(attendeeSchema).optional(),
  })
  .refine(
    (v) => !v.startTime || !v.endTime || v.startTime <= v.endTime,
    { message: "startTime must be <= endTime", path: ["endTime"] },
  );

// Cell-merge overlay payload accepted by PATCH. The whole `merge` key is
// optional; when present it must either be `null` (clear) or a fully
// specified object describing the column range and the free-text label.
// `mergeText` accepts sanitized rich-text HTML up to ~10k bytes (same
// budget as titleAr/titleEn).
const meetingMergeSchema = z
  .union([
    z.null(),
    z.object({
      mergeStartColumn: z.enum(MERGE_COLUMNS),
      mergeEndColumn: z.enum(MERGE_COLUMNS),
      mergeText: z.string().trim().max(10000),
    }),
  ])
  .refine(
    (v) =>
      v === null ||
      MERGE_COLUMN_INDEX[v.mergeStartColumn] <=
        MERGE_COLUMN_INDEX[v.mergeEndColumn],
    {
      message: "mergeStartColumn must come before mergeEndColumn",
      path: ["mergeEndColumn"],
    },
  );

const meetingPatchSchema = z
  .object({
    titleAr: meetingBaseFields.titleAr.optional(),
    titleEn: meetingBaseFields.titleEn.optional(),
    meetingDate: meetingBaseFields.meetingDate.optional(),
    dailyNumber: meetingBaseFields.dailyNumber,
    startTime: meetingBaseFields.startTime,
    endTime: meetingBaseFields.endTime,
    location: meetingBaseFields.location,
    meetingUrl: meetingBaseFields.meetingUrl,
    platform: meetingBaseFields.platform,
    status: meetingBaseFields.status,
    isHighlighted: meetingBaseFields.isHighlighted,
    notes: meetingBaseFields.notes,
    attendees: z.array(attendeeSchema).optional(),
    merge: meetingMergeSchema.optional(),
  })
  .refine(
    (v) => !v.startTime || !v.endTime || v.startTime <= v.endTime,
    { message: "startTime must be <= endTime", path: ["endTime"] },
  );

const attendeesPutSchema = z.object({
  attendees: z.array(attendeeSchema),
});

const duplicateSchema = z.object({
  targetDate: dateSchema,
});

// Reorder schema lives in @workspace/api-zod (lib/api-zod/src/manual.ts) so
// frontend and server share the same contract for POST /reorder.
const reorderSchema = ExecutiveMeetingsReorderBody;

const dateOnly = z.string().regex(/^\d{4}-\d{2}-\d{2}$/);
const timeHm = z.string().regex(/^\d{2}:\d{2}(:\d{2})?$/);

const detailsByType = {
  create: z
    .object({
      titleAr: z.string().trim().max(300).optional(),
      titleEn: z.string().trim().max(300).optional(),
      meetingDate: dateOnly.optional(),
      startTime: timeHm.optional(),
      endTime: timeHm.optional(),
      location: z.string().trim().max(300).optional(),
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  edit: z
    .object({
      titleAr: z.string().trim().max(300).optional(),
      titleEn: z.string().trim().max(300).optional(),
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  delete: z
    .object({
      reason: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  reschedule: z
    .object({
      meetingDate: dateOnly.optional(),
      startTime: timeHm.optional(),
      endTime: timeHm.optional(),
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown())
    .refine(
      (v) => Boolean(v.meetingDate || v.startTime || v.endTime),
      { message: "reschedule requires meetingDate, startTime, or endTime" },
    ),
  add_attendee: z
    .object({
      name: z.string().trim().min(1).max(200),
      title: z.string().trim().max(200).optional(),
      attendanceType: z.enum(ATTENDANCE_TYPES).optional(),
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  remove_attendee: z
    .object({
      attendeeId: z.number().int().positive(),
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  change_location: z
    .object({
      location: z.string().trim().max(300).optional(),
      meetingUrl: z.string().trim().max(1000).optional(),
      platform: z.enum(PLATFORMS).optional(),
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown())
    .refine(
      (v) => Boolean(v.location || v.meetingUrl || v.platform),
      { message: "change_location requires location, meetingUrl, or platform" },
    ),
  cancel_meeting: z
    .object({
      reason: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  note: z
    .object({
      note: z.string().trim().max(5000).optional(),
      text: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown())
    .refine(
      (v) => Boolean((v.note ?? v.text ?? "").trim()),
      { message: "note text required" },
    ),
  highlight: z
    .object({
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  unhighlight: z
    .object({
      notes: z.string().trim().max(5000).optional(),
    })
    .catchall(z.unknown()),
  other: z.object({}).catchall(z.unknown()),
} as const;

const requestPayloadSchemas = z.discriminatedUnion("requestType", [
  z.object({ requestType: z.literal("create"), requestDetails: detailsByType.create }),
  z.object({ requestType: z.literal("edit"), requestDetails: detailsByType.edit }),
  z.object({ requestType: z.literal("delete"), requestDetails: detailsByType.delete }),
  z.object({ requestType: z.literal("reschedule"), requestDetails: detailsByType.reschedule }),
  z.object({ requestType: z.literal("add_attendee"), requestDetails: detailsByType.add_attendee }),
  z.object({ requestType: z.literal("remove_attendee"), requestDetails: detailsByType.remove_attendee }),
  z.object({ requestType: z.literal("change_location"), requestDetails: detailsByType.change_location }),
  z.object({ requestType: z.literal("cancel_meeting"), requestDetails: detailsByType.cancel_meeting }),
  z.object({ requestType: z.literal("note"), requestDetails: detailsByType.note }),
  z.object({ requestType: z.literal("highlight"), requestDetails: detailsByType.highlight }),
  z.object({ requestType: z.literal("unhighlight"), requestDetails: detailsByType.unhighlight }),
  z.object({ requestType: z.literal("other"), requestDetails: detailsByType.other }),
]);

const requestCreateSchema = z.intersection(
  z.object({ meetingId: positiveIntSchema.nullable().optional() }),
  requestPayloadSchemas,
);

const requestNestedCreateSchema = requestPayloadSchemas;

const requestReviewSchema = z.union([
  z.object({
    action: z.literal("withdraw"),
  }),
  z.object({
    status: z.enum(REQUEST_REVIEW_STATUSES),
    reviewNotes: z.string().trim().max(2000).nullable().optional(),
  }),
]);

const dueAtSchema = z
  .union([
    z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
    z.string().datetime(),
    z.literal(""),
  ])
  .nullable()
  .optional()
  .transform((v) => {
    if (v === "" || v === null || v === undefined) return null;
    return /^\d{4}-\d{2}-\d{2}$/.test(v) ? `${v}T00:00:00.000Z` : v;
  });

const taskCreateSchema = z.object({
  taskType: z.string().trim().min(1).max(64),
  meetingId: positiveIntSchema.nullable().optional(),
  requestId: positiveIntSchema.nullable().optional(),
  assignedTo: positiveIntSchema.nullable().optional(),
  dueAt: dueAtSchema,
  notes: z.string().trim().max(5000).nullable().optional(),
});

const taskPatchSchema = z.object({
  status: z.enum(TASK_STATUSES).optional(),
  taskType: z.string().trim().min(1).max(64).optional(),
  assignedTo: positiveIntSchema.nullable().optional(),
  dueAt: dueAtSchema,
  notes: z.string().trim().max(5000).nullable().optional(),
  meetingId: positiveIntSchema.nullable().optional(),
});

const pdfArchiveCreateSchema = z.object({
  archiveDate: dateSchema,
  filePath: z.string().trim().max(500).optional(),
});

const fontSettingsSchema = z.object({
  scope: z.enum(["user", "global"]).default("user"),
  fontFamily: z.enum(FONT_FAMILIES).default("system"),
  fontSize: z.number().int().min(FONT_SIZE_MIN).max(FONT_SIZE_MAX).default(14),
  fontWeight: z.enum(FONT_WEIGHTS).default("regular"),
  alignment: z.enum(FONT_ALIGNMENTS).default("start"),
});

function parseBody<T>(
  res: Response,
  schema: ZodType<T>,
  body: unknown,
): T | null {
  const r = schema.safeParse(body ?? {});
  if (!r.success) {
    const issue = r.error.issues[0];
    const msg = issue
      ? `${issue.path.join(".") || "body"}: ${issue.message}`
      : "validation";
    res.status(400).json({ error: msg, code: "validation" });
    return null;
  }
  return r.data;
}


type DbExecutor = typeof db | Parameters<Parameters<typeof db.transaction>[0]>[0];
type AuditInsert = typeof executiveMeetingAuditLogsTable.$inferInsert;

async function logAudit(
  executor: DbExecutor,
  opts: {
    action: string;
    entityType: string;
    entityId: number | null;
    oldValue?: unknown;
    newValue?: unknown;
    performedBy: number | null;
  },
): Promise<void> {
  const row: AuditInsert = {
    action: opts.action,
    entityType: opts.entityType,
    entityId: opts.entityId ?? null,
    oldValue: (opts.oldValue ?? null) as AuditInsert["oldValue"],
    newValue: (opts.newValue ?? null) as AuditInsert["newValue"],
    performedBy: opts.performedBy,
  };
  await executor.insert(executiveMeetingAuditLogsTable).values(row);
}

// ---------- Common helpers ----------

async function nextDailyNumber(
  executor: DbExecutor,
  date: string,
): Promise<number> {
  const [row] = await executor
    .select({
      max: sql<number | null>`max(${executiveMeetingsTable.dailyNumber})`,
    })
    .from(executiveMeetingsTable)
    .where(eq(executiveMeetingsTable.meetingDate, date));
  return (row?.max ?? 0) + 1;
}

async function fetchMeetingWithAttendees(id: number) {
  const [meeting] = await db
    .select()
    .from(executiveMeetingsTable)
    .where(eq(executiveMeetingsTable.id, id));
  if (!meeting) return null;
  const attendees = await db
    .select()
    .from(executiveMeetingAttendeesTable)
    .where(eq(executiveMeetingAttendeesTable.meetingId, id))
    .orderBy(asc(executiveMeetingAttendeesTable.sortOrder));
  return { ...meeting, attendees };
}

// =====================================================================
// MEETINGS
// =====================================================================

router.get(
  "/executive-meetings",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const dateRaw = typeof req.query.date === "string" ? req.query.date.trim() : "";
    if (dateRaw && !/^\d{4}-\d{2}-\d{2}$/.test(dateRaw)) {
      res
        .status(400)
        .json({ error: "Invalid 'date' (expected YYYY-MM-DD)", code: "bad_date" });
      return;
    }
    const targetDate = dateRaw || new Date().toISOString().slice(0, 10);
    const meetings = await db
      .select()
      .from(executiveMeetingsTable)
      .where(eq(executiveMeetingsTable.meetingDate, targetDate))
      .orderBy(asc(executiveMeetingsTable.dailyNumber));
    if (meetings.length === 0) {
      res.json({ date: targetDate, meetings: [] });
      return;
    }
    const ids = meetings.map((m) => m.id);
    const attendees = await db
      .select()
      .from(executiveMeetingAttendeesTable)
      .where(inArray(executiveMeetingAttendeesTable.meetingId, ids))
      .orderBy(
        asc(executiveMeetingAttendeesTable.meetingId),
        asc(executiveMeetingAttendeesTable.sortOrder),
      );
    const byMeeting = new Map<number, typeof attendees>();
    for (const a of attendees) {
      const arr = byMeeting.get(a.meetingId) ?? [];
      arr.push(a);
      byMeeting.set(a.meetingId, arr);
    }
    res.json({
      date: targetDate,
      meetings: meetings.map((m) => ({
        ...m,
        attendees: byMeeting.get(m.id) ?? [],
      })),
    });
  },
);

router.get(
  "/executive-meetings/dates",
  requireExecutiveAccess,
  async (_req, res): Promise<void> => {
    const rows = await db
      .selectDistinct({ d: executiveMeetingsTable.meetingDate })
      .from(executiveMeetingsTable)
      .orderBy(asc(executiveMeetingsTable.meetingDate));
    res.json({ dates: rows.map((r) => r.d) });
  },
);

router.get(
  "/executive-meetings/me",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    if (!req.session.userId) {
      res.status(401).json({ error: "Unauthorized", code: "unauthorized" });
      return;
    }
    const names = await getRoleNamesForUser(req.session.userId);
    res.json({
      userId: req.session.userId,
      roles: Array.from(names),
      canRead: READ_ROLES.some((r) => names.has(r)),
      canMutate: MUTATE_ROLES.some((r) => names.has(r)),
      canApprove: APPROVE_ROLES.some((r) => names.has(r)),
      canSubmitRequest: REQUEST_ROLES.some((r) => names.has(r)),
      canViewAudit: ADMIN_AUDIT_ROLES.some((r) => names.has(r)),
      canViewTasks: TASK_VIEW_ROLES.some((r) => names.has(r)),
      canViewAllTasks: TASK_BROAD_VIEW_ROLES.some((r) => names.has(r)),
    });
  },
);

router.get(
  "/executive-meetings/:id",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const m = await fetchMeetingWithAttendees(id);
    if (!m) {
      res.status(404).json({ error: "Meeting not found", code: "not_found" });
      return;
    }
    res.json(m);
  },
);

router.post(
  "/executive-meetings",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const data = parseBody(res, meetingCreateSchema, req.body);
    if (!data) return;
    const userId = req.session.userId!;
    try {
      const inserted = await db.transaction(async (tx) => {
        const dailyNumber =
          data.dailyNumber ?? (await nextDailyNumber(tx, data.meetingDate));
        const meetingValues: typeof executiveMeetingsTable.$inferInsert = {
          titleAr: sanitizeRichText(data.titleAr),
          titleEn: sanitizeRichText(data.titleEn ?? ""),
          meetingDate: data.meetingDate,
          dailyNumber,
          startTime: data.startTime ?? null,
          endTime: data.endTime ?? null,
          location: data.location ?? null,
          meetingUrl: data.meetingUrl ?? null,
          platform: data.platform ?? "none",
          status: data.status ?? "scheduled",
          isHighlighted: data.isHighlighted ?? 0,
          notes: data.notes ?? null,
          createdBy: userId,
          updatedBy: userId,
        };
        const [meeting] = await tx
          .insert(executiveMeetingsTable)
          .values(meetingValues)
          .returning();
        if (!meeting) throw new Error("insert_failed");
        const attendees = data.attendees ?? [];
        if (attendees.length > 0) {
          await tx.insert(executiveMeetingAttendeesTable).values(
            attendees.map((a, idx) => ({
              meetingId: meeting.id,
              name: sanitizeRichText(a.name),
              // title is plain text in the UI, but it gets interpolated
              // into HTML by templates like the print page, so strip
              // any sneaky markup at the API boundary.
              title: sanitizePlainTextOrNull(a.title),
              attendanceType: a.attendanceType,
              sortOrder: a.sortOrder ?? idx,
              kind: a.kind,
            })),
          );
        }
        await logAudit(tx, {
          action: "create",
          entityType: "meeting",
          entityId: meeting.id,
          newValue: { ...meeting, attendees },
          performedBy: userId,
        });
        const approverIds = await getUserIdsForRoleNames(APPROVE_ROLES);
        const recipients = await recordExecutiveMeetingNotifications(tx, {
          recipientUserIds: approverIds,
          meetingId: meeting.id,
          notificationType: "meeting_created",
          titleAr: "اجتماع جديد",
          titleEn: "New executive meeting",
          bodyAr: meeting.titleAr || meeting.titleEn || "",
          bodyEn: meeting.titleEn || meeting.titleAr || "",
          relatedType: "executive_meeting",
          relatedId: meeting.id,
          excludeUserId: userId,
        });
        return { meeting, recipients };
      });
      void broadcastExecutiveMeetingNotifications(
        inserted.recipients,
        "meeting_created",
        inserted.meeting.id,
      );
      // Realtime: every open schedule tab on this day refetches so the
      // newly-created row shows up without a manual refresh.
      void emitExecutiveMeetingsDayChanged(inserted.meeting.meetingDate);
      const full = await fetchMeetingWithAttendees(inserted.meeting.id);
      res.status(201).json(full);
    } catch (err) {
      const msg = err instanceof Error ? err.message : String(err);
      if (msg.includes("executive_meetings_date_number_unique")) {
        res
          .status(409)
          .json({
            error: "Daily number already used for this date",
            code: "duplicate_number",
          });
        return;
      }
      res.status(500).json({ error: "Could not create meeting", code: "create_failed" });
    }
  },
);

router.patch(
  "/executive-meetings/:id",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const existing = await fetchMeetingWithAttendees(id);
    if (!existing) {
      res.status(404).json({ error: "Meeting not found", code: "not_found" });
      return;
    }
    const data = parseBody(res, meetingPatchSchema, req.body);
    if (!data) return;
    const userId = req.session.userId!;
    try {
      await db.transaction(async (tx) => {
        const updateValues: Partial<typeof executiveMeetingsTable.$inferInsert> = {
          updatedBy: userId,
        };
        if (data.titleAr !== undefined)
          updateValues.titleAr = sanitizeRichText(data.titleAr);
        if (data.titleEn !== undefined)
          updateValues.titleEn = sanitizeRichText(data.titleEn ?? "");
        if (data.meetingDate !== undefined) updateValues.meetingDate = data.meetingDate;
        if (data.dailyNumber !== undefined && data.dailyNumber !== null)
          updateValues.dailyNumber = data.dailyNumber;
        if (data.startTime !== undefined) updateValues.startTime = data.startTime;
        if (data.endTime !== undefined) updateValues.endTime = data.endTime;
        if (data.location !== undefined) updateValues.location = data.location;
        if (data.meetingUrl !== undefined) updateValues.meetingUrl = data.meetingUrl;
        if (data.platform !== undefined) updateValues.platform = data.platform;
        if (data.status !== undefined) updateValues.status = data.status;
        if (data.isHighlighted !== undefined)
          updateValues.isHighlighted = data.isHighlighted;
        if (data.notes !== undefined) updateValues.notes = data.notes;
        if (data.merge !== undefined) {
          if (data.merge === null) {
            updateValues.mergeStartColumn = null;
            updateValues.mergeEndColumn = null;
            updateValues.mergeText = null;
          } else {
            updateValues.mergeStartColumn = data.merge.mergeStartColumn;
            updateValues.mergeEndColumn = data.merge.mergeEndColumn;
            updateValues.mergeText = sanitizeRichText(data.merge.mergeText);
          }
        }

        if (Object.keys(updateValues).length > 1) {
          await tx
            .update(executiveMeetingsTable)
            .set(updateValues)
            .where(eq(executiveMeetingsTable.id, id));
        }
        let attendees = existing.attendees;
        if (data.attendees !== undefined) {
          await tx
            .delete(executiveMeetingAttendeesTable)
            .where(eq(executiveMeetingAttendeesTable.meetingId, id));
          if (data.attendees.length > 0) {
            await tx.insert(executiveMeetingAttendeesTable).values(
              data.attendees.map((a, idx) => ({
                meetingId: id,
                name: sanitizeRichText(a.name),
                title: sanitizePlainTextOrNull(a.title),
                attendanceType: a.attendanceType,
                sortOrder: a.sortOrder ?? idx,
                kind: a.kind,
              })),
            );
          }
          attendees = (await db
            .select()
            .from(executiveMeetingAttendeesTable)
            .where(eq(executiveMeetingAttendeesTable.meetingId, id))
            .orderBy(asc(executiveMeetingAttendeesTable.sortOrder)));
        }
        await logAudit(tx, {
          action: "update",
          entityType: "meeting",
          entityId: id,
          oldValue: existing,
          newValue: { ...existing, ...updateValues, attendees },
          performedBy: userId,
        });
      });
      const updated = await fetchMeetingWithAttendees(id);
      // Realtime: when the meeting was rescheduled to a different day we
      // need to refetch BOTH days — viewers on the old day must lose the
      // row, viewers on the new day must gain it. The dedup helper is a
      // no-op when both dates match (the common in-place edit case).
      void emitExecutiveMeetingsDaysChanged([
        existing.meetingDate,
        updated?.meetingDate ?? existing.meetingDate,
      ]);
      res.json(updated);
    } catch (err) {
      const msg = err instanceof Error ? err.message : String(err);
      if (msg.includes("executive_meetings_date_number_unique")) {
        res
          .status(409)
          .json({
            error: "Daily number already used for this date",
            code: "duplicate_number",
          });
        return;
      }
      res.status(500).json({ error: "Could not update meeting", code: "update_failed" });
    }
  },
);

router.delete(
  "/executive-meetings/:id",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const existing = await fetchMeetingWithAttendees(id);
    if (!existing) {
      res.status(404).json({ error: "Meeting not found", code: "not_found" });
      return;
    }
    const userId = req.session.userId!;
    await db.transaction(async (tx) => {
      await tx.delete(executiveMeetingsTable).where(eq(executiveMeetingsTable.id, id));
      await logAudit(tx, {
        action: "delete",
        entityType: "meeting",
        entityId: id,
        oldValue: existing,
        performedBy: userId,
      });
    });
    // Realtime: every open schedule tab on the deleted meeting's day
    // refetches so the row disappears without a manual refresh.
    void emitExecutiveMeetingsDayChanged(existing.meetingDate);
    res.status(204).end();
  },
);

router.put(
  "/executive-meetings/:id/attendees",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const existing = await fetchMeetingWithAttendees(id);
    if (!existing) {
      res.status(404).json({ error: "Meeting not found", code: "not_found" });
      return;
    }
    const data = parseBody(res, attendeesPutSchema, req.body);
    if (!data) return;
    const userId = req.session.userId!;
    await db.transaction(async (tx) => {
      await tx
        .delete(executiveMeetingAttendeesTable)
        .where(eq(executiveMeetingAttendeesTable.meetingId, id));
      if (data.attendees.length > 0) {
        await tx.insert(executiveMeetingAttendeesTable).values(
          data.attendees.map((a, idx) => ({
            meetingId: id,
            name: sanitizeRichText(a.name),
            title: sanitizePlainTextOrNull(a.title),
            attendanceType: a.attendanceType,
            sortOrder: a.sortOrder ?? idx,
            kind: a.kind,
          })),
        );
      }
      await logAudit(tx, {
        action: "replace_attendees",
        entityType: "meeting",
        entityId: id,
        oldValue: { attendees: existing.attendees },
        newValue: { attendees: data.attendees },
        performedBy: userId,
      });
    });
    const updated = await fetchMeetingWithAttendees(id);
    // Realtime: replacing the attendee list is the most common
    // multi-tab edit (drag-to-reorder, inline rename, group move),
    // so make sure every viewer on this day refetches.
    void emitExecutiveMeetingsDayChanged(
      updated?.meetingDate ?? existing.meetingDate,
    );
    res.json(updated);
  },
);

router.post(
  "/executive-meetings/:id/duplicate",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const data = parseBody(res, duplicateSchema, req.body);
    if (!data) return;
    const source = await fetchMeetingWithAttendees(id);
    if (!source) {
      res.status(404).json({ error: "Meeting not found", code: "not_found" });
      return;
    }
    const userId = req.session.userId!;
    try {
      const inserted = await db.transaction(async (tx) => {
        const nextNumber = await nextDailyNumber(tx, data.targetDate);
        const meetingValues: typeof executiveMeetingsTable.$inferInsert = {
          dailyNumber: nextNumber,
          // Re-sanitize on duplicate so a row that was somehow stored with
          // disallowed markup (e.g. raw SQL import, schema regression) does
          // not get cloned into a fresh meeting unchecked.
          titleAr: sanitizeRichText(source.titleAr),
          titleEn: source.titleEn ? sanitizeRichText(source.titleEn) : source.titleEn,
          meetingDate: data.targetDate,
          startTime: source.startTime,
          endTime: source.endTime,
          location: source.location,
          meetingUrl: source.meetingUrl,
          platform: source.platform,
          status: "scheduled",
          isHighlighted: 0,
          notes: source.notes,
          attachments: source.attachments,
          createdBy: userId,
          updatedBy: userId,
        };
        const [meeting] = await tx
          .insert(executiveMeetingsTable)
          .values(meetingValues)
          .returning();
        if (!meeting) throw new Error("duplicate_failed");
        if (source.attendees && source.attendees.length > 0) {
          await tx.insert(executiveMeetingAttendeesTable).values(
            source.attendees.map((a: ExecutiveMeetingAttendee, idx: number) => ({
              meetingId: meeting.id,
              // source already-stored values are sanitized at write time, but
              // re-running keeps a defense-in-depth boundary if anything was
              // imported via raw SQL.
              name: sanitizeRichText(a.name),
              title: sanitizePlainTextOrNull(a.title),
              attendanceType: a.attendanceType,
              sortOrder: a.sortOrder ?? idx,
              // Carry custom subheadings into the duplicated meeting so the
              // user does not lose their group structure on duplicate.
              kind: a.kind,
            })),
          );
        }
        await logAudit(tx, {
          action: "duplicate",
          entityType: "meeting",
          entityId: meeting.id,
          oldValue: { sourceId: source.id, sourceDate: source.meetingDate },
          newValue: meeting,
          performedBy: userId,
        });
        return meeting;
      });
      const full = await fetchMeetingWithAttendees(inserted.id);
      // Realtime: target day gained a new meeting — refetch so every
      // viewer on the duplicate's destination day sees the new row.
      void emitExecutiveMeetingsDayChanged(data.targetDate);
      res.status(201).json(full);
    } catch (err) {
      const msg = err instanceof Error ? err.message : String(err);
      if (msg.includes("executive_meetings_date_number_unique")) {
        res.status(409).json({ error: "Daily number conflict", code: "conflict" });
        return;
      }
      res.status(500).json({ error: msg, code: "server_error" });
    }
  },
);

// =====================================================================
// REORDER (within a single day)
// =====================================================================

router.post(
  "/executive-meetings/reorder",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const data = parseBody(res, reorderSchema, req.body);
    if (!data) return;
    const userId = req.session.userId!;
    const dedup = new Set(data.orderedIds);
    if (dedup.size !== data.orderedIds.length) {
      res
        .status(400)
        .json({ error: "Duplicate ids in orderedIds", code: "duplicate_ids" });
      return;
    }
    try {
      await db.transaction(async (tx) => {
        const allRows = await tx
          .select()
          .from(executiveMeetingsTable)
          .where(eq(executiveMeetingsTable.meetingDate, data.meetingDate));
        if (allRows.length !== data.orderedIds.length) {
          throw Object.assign(new Error("incomplete_day_reorder"), {
            httpStatus: 400,
            code: "incomplete_day",
          });
        }
        const dayIds = new Set(allRows.map((r) => r.id));
        for (const id of data.orderedIds) {
          if (!dayIds.has(id)) {
            throw Object.assign(new Error("invalid_or_cross_day_id"), {
              httpStatus: 400,
              code: "invalid_ids",
            });
          }
        }
        const slots = allRows
          .slice()
          .sort((a, b) => {
            const aHasTime = a.startTime != null;
            const bHasTime = b.startTime != null;
            if (aHasTime && bHasTime) {
              if (a.startTime! < b.startTime!) return -1;
              if (a.startTime! > b.startTime!) return 1;
              return a.dailyNumber - b.dailyNumber;
            }
            if (aHasTime) return -1;
            if (bHasTime) return 1;
            return a.dailyNumber - b.dailyNumber;
          })
          .map((r) => ({ startTime: r.startTime, endTime: r.endTime }));
        for (let i = 0; i < data.orderedIds.length; i++) {
          const id = data.orderedIds[i]!;
          await tx
            .update(executiveMeetingsTable)
            .set({ dailyNumber: -(i + 1), updatedBy: userId })
            .where(eq(executiveMeetingsTable.id, id));
        }
        for (let i = 0; i < data.orderedIds.length; i++) {
          const id = data.orderedIds[i]!;
          const slot = slots[i]!;
          await tx
            .update(executiveMeetingsTable)
            .set({
              startTime: slot.startTime,
              endTime: slot.endTime,
              dailyNumber: i + 1,
              updatedBy: userId,
            })
            .where(eq(executiveMeetingsTable.id, id));
        }
        await logAudit(tx, {
          action: "executive_meeting.reorder",
          entityType: "meeting",
          entityId: data.orderedIds[0]!,
          oldValue: {
            meetingDate: data.meetingDate,
            order: allRows
              .slice()
              .sort((a, b) => a.dailyNumber - b.dailyNumber)
              .map((r) => r.id),
          },
          newValue: {
            meetingDate: data.meetingDate,
            order: data.orderedIds,
          },
          performedBy: userId,
        });
        const updated = await tx
          .select()
          .from(executiveMeetingsTable)
          .where(eq(executiveMeetingsTable.meetingDate, data.meetingDate));
        const seen = new Set<number>();
        for (const u of updated) {
          if (seen.has(u.dailyNumber)) {
            throw new Error("duplicate_daily_number_after_reorder");
          }
          seen.add(u.dailyNumber);
        }
      });
      // Return the updated meetings in the new order, with attendees.
      const idToFetch = data.orderedIds;
      const fullList = await Promise.all(
        idToFetch.map((id) => fetchMeetingWithAttendees(id)),
      );
      // Realtime: every viewer on this day refetches so the new
      // ordering (and any time-slot reshuffling that came with it)
      // shows up without a manual refresh.
      void emitExecutiveMeetingsDayChanged(data.meetingDate);
      res.json({ meetings: fullList.filter(Boolean) });
    } catch (err) {
      const status = (err as { httpStatus?: number }).httpStatus ?? 500;
      const code = (err as { code?: string }).code ?? "server_error";
      const msg = err instanceof Error ? err.message : String(err);
      res.status(status).json({ error: msg, code });
    }
  },
);

// =====================================================================
// REQUESTS
// =====================================================================

const TIME_RE_LOCAL = /^\d{2}:\d{2}(:\d{2})?$/;
const DATE_RE_LOCAL = /^\d{4}-\d{2}-\d{2}$/;

function validateApplyPayload(
  reqRow: typeof executiveMeetingRequestsTable.$inferSelect,
): string | null {
  const details = (reqRow.requestDetails ?? {}) as Record<string, unknown>;
  if (reqRow.requestType === "reschedule") {
    const st = typeof details.startTime === "string" ? details.startTime : null;
    const et = typeof details.endTime === "string" ? details.endTime : null;
    const md = typeof details.meetingDate === "string" ? details.meetingDate : null;
    if (st && !TIME_RE_LOCAL.test(st)) return "startTime invalid";
    if (et && !TIME_RE_LOCAL.test(et)) return "endTime invalid";
    if (md && !DATE_RE_LOCAL.test(md)) return "meetingDate invalid";
    if (st && et && st > et) return "startTime must be <= endTime";
  }
  return null;
}

async function applyApprovedRequest(
  tx: DbExecutor,
  reqRow: typeof executiveMeetingRequestsTable.$inferSelect,
  performedBy: number,
): Promise<void> {
  const details = (reqRow.requestDetails ?? {}) as Record<string, unknown>;
  if (!reqRow.meetingId) return;
  const meetingId = reqRow.meetingId;
  const [existing] = await tx
    .select()
    .from(executiveMeetingsTable)
    .where(eq(executiveMeetingsTable.id, meetingId));
  if (!existing) return;

  const update: Partial<typeof executiveMeetingsTable.$inferInsert> = {
    updatedBy: performedBy,
  };
  switch (reqRow.requestType) {
    case "reschedule": {
      if (typeof details.startTime === "string" && TIME_RE_LOCAL.test(details.startTime))
        update.startTime = details.startTime;
      if (typeof details.endTime === "string" && TIME_RE_LOCAL.test(details.endTime))
        update.endTime = details.endTime;
      if (typeof details.meetingDate === "string" && DATE_RE_LOCAL.test(details.meetingDate))
        update.meetingDate = details.meetingDate;
      const effStart = update.startTime ?? existing.startTime;
      const effEnd = update.endTime ?? existing.endTime;
      if (effStart && effEnd && effStart > effEnd) {
        throw new Error("APPLY_VALIDATION:startTime must be <= endTime");
      }
      break;
    }
    case "change_location": {
      if (typeof details.location === "string")
        update.location = details.location.slice(0, 300);
      if (typeof details.meetingUrl === "string")
        update.meetingUrl = details.meetingUrl.slice(0, 1000);
      if (
        typeof details.platform === "string" &&
        (PLATFORMS as readonly string[]).includes(details.platform)
      )
        update.platform = details.platform;
      break;
    }
    case "cancel_meeting":
      update.status = "cancelled";
      break;
    case "highlight":
      update.isHighlighted = 1;
      break;
    case "unhighlight":
      update.isHighlighted = 0;
      break;
    case "note": {
      const newNote = typeof details.note === "string" ? details.note.slice(0, 5000) : "";
      const prev = existing.notes ?? "";
      update.notes = prev ? `${prev}\n${newNote}` : newNote;
      break;
    }
    case "add_attendee": {
      const rawName = typeof details.name === "string" ? details.name.trim() : "";
      if (!rawName) break;
      // Same sanitization boundary as the direct attendee POST/PUT paths so
      // an approved request cannot smuggle script/event-handler markup into
      // the rich-text attendee.name column.
      const name = sanitizeRichText(rawName.slice(0, 5000));
      if (!name) break;
      // Same plain-text sanitization as the direct attendee write paths
      // so an approved request cannot smuggle <script>/HTML into title.
      const title =
        typeof details.title === "string"
          ? sanitizePlainTextOrNull(details.title.slice(0, 200))
          : null;
      const attendanceType =
        typeof details.attendanceType === "string" &&
        (ATTENDANCE_TYPES as readonly string[]).includes(details.attendanceType)
          ? (details.attendanceType as (typeof ATTENDANCE_TYPES)[number])
          : "internal";
      const [{ value: maxOrder }] = await tx
        .select({
          value: sql<number>`coalesce(max(${executiveMeetingAttendeesTable.sortOrder}), -1)`,
        })
        .from(executiveMeetingAttendeesTable)
        .where(eq(executiveMeetingAttendeesTable.meetingId, meetingId));
      await tx.insert(executiveMeetingAttendeesTable).values({
        meetingId,
        name,
        title,
        attendanceType,
        sortOrder: (maxOrder ?? -1) + 1,
      });
      break;
    }
    case "remove_attendee": {
      const attendeeId = Number(details.attendeeId);
      if (Number.isInteger(attendeeId) && attendeeId > 0) {
        await tx
          .delete(executiveMeetingAttendeesTable)
          .where(
            and(
              eq(executiveMeetingAttendeesTable.id, attendeeId),
              eq(executiveMeetingAttendeesTable.meetingId, meetingId),
            ),
          );
      }
      break;
    }
    default:
      break;
  }
  if (Object.keys(update).length > 1) {
    await tx
      .update(executiveMeetingsTable)
      .set(update)
      .where(eq(executiveMeetingsTable.id, meetingId));
  }
}

router.get(
  "/executive-meetings/requests",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const status = typeof req.query.status === "string" ? req.query.status : "";
    const mine = req.query.mine === "true" || req.query.mine === "1";
    const dateFromRaw =
      typeof req.query.dateFrom === "string" ? req.query.dateFrom.trim() : "";
    const dateToRaw =
      typeof req.query.dateTo === "string" ? req.query.dateTo.trim() : "";
    const requesterRaw = Number(req.query.requester);
    const userId = req.session.userId!;

    const names = await getRoleNamesForUser(userId);
    const canSeeAllRequests =
      APPROVE_ROLES.some((r) => names.has(r)) ||
      ADMIN_AUDIT_ROLES.some((r) => names.has(r));

    const conds: SQL[] = [];
    if (status) conds.push(eq(executiveMeetingRequestsTable.status, status));
    if (mine || !canSeeAllRequests) {
      conds.push(eq(executiveMeetingRequestsTable.requestedBy, userId));
    } else if (Number.isInteger(requesterRaw) && requesterRaw > 0) {
      // Approvers may filter by a specific requester; non-approvers ignored.
      conds.push(eq(executiveMeetingRequestsTable.requestedBy, requesterRaw));
    }
    if (dateFromRaw && /^\d{4}-\d{2}-\d{2}$/.test(dateFromRaw)) {
      conds.push(
        gte(
          executiveMeetingRequestsTable.createdAt,
          new Date(`${dateFromRaw}T00:00:00.000Z`),
        ),
      );
    }
    if (dateToRaw && /^\d{4}-\d{2}-\d{2}$/.test(dateToRaw)) {
      const end = new Date(`${dateToRaw}T00:00:00.000Z`);
      end.setUTCDate(end.getUTCDate() + 1);
      conds.push(lt(executiveMeetingRequestsTable.createdAt, end));
    }
    const where = conds.length > 0 ? and(...conds) : undefined;

    const rows = await db
      .select({
        r: executiveMeetingRequestsTable,
        requesterUsername: usersTable.username,
        requesterDisplayNameAr: usersTable.displayNameAr,
        requesterDisplayNameEn: usersTable.displayNameEn,
      })
      .from(executiveMeetingRequestsTable)
      .leftJoin(usersTable, eq(usersTable.id, executiveMeetingRequestsTable.requestedBy))
      .where(where)
      .orderBy(desc(executiveMeetingRequestsTable.createdAt))
      .limit(200);

    res.json({
      requests: rows.map((row) => ({
        ...row.r,
        requester: row.requesterUsername
          ? {
              username: row.requesterUsername,
              displayNameAr: row.requesterDisplayNameAr,
              displayNameEn: row.requesterDisplayNameEn,
            }
          : null,
      })),
    });
  },
);

router.post(
  "/executive-meetings/requests",
  requireExecutiveAccess,
  requireRequest,
  async (req, res): Promise<void> => {
    const data = parseBody(res, requestCreateSchema, req.body);
    if (!data) return;
    let meetingId = data.meetingId ?? null;
    if (meetingId !== null) {
      const [m] = await db
        .select({ id: executiveMeetingsTable.id })
        .from(executiveMeetingsTable)
        .where(eq(executiveMeetingsTable.id, meetingId));
      if (!m) {
        res.status(404).json({ error: "Meeting not found", code: "not_found" });
        return;
      }
    }
    const userId = req.session.userId!;
    const result = await db.transaction(async (tx) => {
      const insertValues: typeof executiveMeetingRequestsTable.$inferInsert = {
        meetingId,
        requestedBy: userId,
        requestType: data.requestType,
        requestDetails: data.requestDetails,
        status: "new",
      };
      const [row] = await tx
        .insert(executiveMeetingRequestsTable)
        .values(insertValues)
        .returning();
      await logAudit(tx, {
        action: "submit",
        entityType: "request",
        entityId: row!.id,
        newValue: row!,
        performedBy: userId,
      });
      const approverIds = await getUserIdsForRoleNames(APPROVE_ROLES);
      const requesterDisplay = await getUserDisplayForNotify(userId);
      const recipients = await recordExecutiveMeetingNotifications(tx, {
        recipientUserIds: approverIds,
        meetingId: row!.meetingId,
        notificationType: "request_submitted",
        titleAr: "طلب جديد بانتظار المراجعة",
        titleEn: "New executive-meeting request awaiting review",
        bodyAr: requesterDisplay
          ? `${requesterDisplay.ar} — ${row!.requestType}`
          : row!.requestType,
        bodyEn: requesterDisplay
          ? `${requesterDisplay.en} — ${row!.requestType}`
          : row!.requestType,
        relatedType: "executive_meeting_request",
        relatedId: row!.id,
        excludeUserId: userId,
      });
      return { row: row!, recipients };
    });
    void broadcastExecutiveMeetingNotifications(
      result.recipients,
      "request_submitted",
      result.row.meetingId,
    );
    void sendExecutiveMeetingEmail(
      result.recipients,
      {
        ar: "طلب جديد بانتظار المراجعة",
        en: "New executive-meeting request awaiting review",
      },
      {
        ar: `طلب من النوع ${result.row.requestType} بانتظار اعتمادك.`,
        en: `A ${result.row.requestType} request is awaiting your review.`,
      },
      "request_submitted",
    );
    res.status(201).json(result.row);
  },
);

router.post(
  "/executive-meetings/:id/requests",
  requireExecutiveAccess,
  requireRequest,
  async (req, res): Promise<void> => {
    const meetingId = Number(req.params.id);
    if (!Number.isInteger(meetingId) || meetingId <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const [m] = await db
      .select({ id: executiveMeetingsTable.id })
      .from(executiveMeetingsTable)
      .where(eq(executiveMeetingsTable.id, meetingId));
    if (!m) {
      res.status(404).json({ error: "Meeting not found", code: "not_found" });
      return;
    }
    const data = parseBody(res, requestNestedCreateSchema, req.body);
    if (!data) return;
    const userId = req.session.userId!;
    const result = await db.transaction(async (tx) => {
      const insertValues: typeof executiveMeetingRequestsTable.$inferInsert = {
        meetingId,
        requestedBy: userId,
        requestType: data.requestType,
        requestDetails: data.requestDetails,
        status: "new",
      };
      const [row] = await tx
        .insert(executiveMeetingRequestsTable)
        .values(insertValues)
        .returning();
      await logAudit(tx, {
        action: "submit",
        entityType: "request",
        entityId: row!.id,
        newValue: row!,
        performedBy: userId,
      });
      const approverIds = await getUserIdsForRoleNames(APPROVE_ROLES);
      const requesterDisplay = await getUserDisplayForNotify(userId);
      const recipients = await recordExecutiveMeetingNotifications(tx, {
        recipientUserIds: approverIds,
        meetingId: row!.meetingId,
        notificationType: "request_submitted",
        titleAr: "طلب جديد بانتظار المراجعة",
        titleEn: "New executive-meeting request awaiting review",
        bodyAr: requesterDisplay
          ? `${requesterDisplay.ar} — ${row!.requestType}`
          : row!.requestType,
        bodyEn: requesterDisplay
          ? `${requesterDisplay.en} — ${row!.requestType}`
          : row!.requestType,
        relatedType: "executive_meeting_request",
        relatedId: row!.id,
        excludeUserId: userId,
      });
      return { row: row!, recipients };
    });
    void broadcastExecutiveMeetingNotifications(
      result.recipients,
      "request_submitted",
      result.row.meetingId,
    );
    void sendExecutiveMeetingEmail(
      result.recipients,
      {
        ar: "طلب جديد بانتظار المراجعة",
        en: "New executive-meeting request awaiting review",
      },
      {
        ar: `طلب من النوع ${result.row.requestType} بانتظار اعتمادك.`,
        en: `A ${result.row.requestType} request is awaiting your review.`,
      },
      "request_submitted",
    );
    res.status(201).json(result.row);
  },
);

router.patch(
  "/executive-meetings/requests/:id",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const userId = req.session.userId!;
    const [existing] = await db
      .select()
      .from(executiveMeetingRequestsTable)
      .where(eq(executiveMeetingRequestsTable.id, id));
    if (!existing) {
      res.status(404).json({ error: "Request not found", code: "not_found" });
      return;
    }
    const data = parseBody(res, requestReviewSchema, req.body);
    if (!data) return;
    const names = await getRoleNamesForUser(userId);

    if ("action" in data && data.action === "withdraw") {
      if (existing.requestedBy !== userId) {
        res.status(403).json({ error: "Forbidden", code: "forbidden" });
        return;
      }
      if (existing.status !== "new") {
        res.status(409).json({ error: "Cannot withdraw", code: "bad_state" });
        return;
      }
      const updated = await db.transaction(async (tx) => {
        const [row] = await tx
          .update(executiveMeetingRequestsTable)
          .set({ status: "withdrawn" })
          .where(eq(executiveMeetingRequestsTable.id, id))
          .returning();
        await logAudit(tx, {
          action: "withdraw",
          entityType: "request",
          entityId: id,
          oldValue: existing,
          newValue: row,
          performedBy: userId,
        });
        return row;
      });
      res.json(updated);
      return;
    }

    // Status-driven review path (approved | rejected | needs_edit)
    const canApprove = APPROVE_ROLES.some((r) => names.has(r));
    if (!canApprove) {
      res.status(403).json({ error: "Forbidden", code: "forbidden" });
      return;
    }
    if (existing.status !== "new") {
      res.status(409).json({ error: "Already reviewed", code: "bad_state" });
      return;
    }
    const review = data as {
      status: (typeof REQUEST_REVIEW_STATUSES)[number];
      reviewNotes?: string | null;
    };

    if (review.status === "approved") {
      const validationError = validateApplyPayload(existing);
      if (validationError) {
        res.status(400).json({ error: validationError, code: "validation" });
        return;
      }
    }

    try {
      const result = await db.transaction(async (tx) => {
        const [row] = await tx
          .update(executiveMeetingRequestsTable)
          .set({
            status: review.status,
            reviewedBy: userId,
            reviewDecision: review.status,
            reviewNotes: review.reviewNotes ?? null,
          })
          .where(
            and(
              eq(executiveMeetingRequestsTable.id, id),
              eq(executiveMeetingRequestsTable.status, "new"),
            ),
          )
          .returning();
        if (!row) throw new Error("RACE_ALREADY_REVIEWED");
        await logAudit(tx, {
          action: review.status,
          entityType: "request",
          entityId: id,
          oldValue: existing,
          newValue: row,
          performedBy: userId,
        });
        const taskRecipients: number[] = [];
        // Track every day a viewer might need to refetch — both the
        // pre-apply day (for reschedule, viewers on the old day must
        // lose the row) and the post-apply day. Captured inside the
        // transaction so we never emit stale dates if the apply throws.
        const affectedDates: string[] = [];
        if (review.status === "approved") {
          if (row.meetingId) {
            const [pre] = await tx
              .select({ meetingDate: executiveMeetingsTable.meetingDate })
              .from(executiveMeetingsTable)
              .where(eq(executiveMeetingsTable.id, row.meetingId));
            if (pre?.meetingDate) affectedDates.push(pre.meetingDate);
          }
          await applyApprovedRequest(tx, row, userId);
          if (row.meetingId) {
            const [post] = await tx
              .select({ meetingDate: executiveMeetingsTable.meetingDate })
              .from(executiveMeetingsTable)
              .where(eq(executiveMeetingsTable.id, row.meetingId));
            if (post?.meetingDate) affectedDates.push(post.meetingDate);
            await logAudit(tx, {
              action: "apply",
              entityType: "meeting",
              entityId: row.meetingId,
              oldValue: { fromRequestId: row.id },
              newValue: { requestType: row.requestType, details: row.requestDetails },
              performedBy: userId,
            });
          }
          const [task] = await tx
            .insert(executiveMeetingTasksTable)
            .values({
              taskType: `follow_up_${row.requestType}`,
              meetingId: row.meetingId,
              requestId: row.id,
              assignedTo: row.assignedTo ?? null,
              status: "pending",
              notes: review.reviewNotes ?? null,
            })
            .returning();
          await logAudit(tx, {
            action: "create",
            entityType: "task",
            entityId: task!.id,
            newValue: task!,
            performedBy: userId,
          });
          if (task && task.assignedTo) {
            const r = await recordExecutiveMeetingNotifications(tx, {
              recipientUserIds: [task.assignedTo],
              meetingId: task.meetingId ?? row.meetingId,
              notificationType: "task_assigned",
              titleAr: "أُسندت إليك مهمة",
              titleEn: "You were assigned a task",
              bodyAr: task.taskType,
              bodyEn: task.taskType,
              relatedType: "executive_meeting_task",
              relatedId: task.id,
              excludeUserId: userId,
            });
            taskRecipients.push(...r);
          }
        }
        const requesterId = existing.requestedBy;
        const reviewerDisplay = await getUserDisplayForNotify(userId);
        let reviewRecipients: number[] = [];
        if (requesterId && requesterId !== userId) {
          reviewRecipients = await recordExecutiveMeetingNotifications(tx, {
            recipientUserIds: [requesterId],
            meetingId: row.meetingId,
            notificationType: `request_${review.status}`,
            titleAr:
              review.status === "approved"
                ? "تم اعتماد طلبك"
                : review.status === "rejected"
                  ? "رُفض طلبك"
                  : "طلبك يحتاج تعديلاً",
            titleEn:
              review.status === "approved"
                ? "Your request was approved"
                : review.status === "rejected"
                  ? "Your request was rejected"
                  : "Your request needs edits",
            bodyAr: reviewerDisplay
              ? `${reviewerDisplay.ar} — ${row.requestType}`
              : row.requestType,
            bodyEn: reviewerDisplay
              ? `${reviewerDisplay.en} — ${row.requestType}`
              : row.requestType,
            relatedType: "executive_meeting_request",
            relatedId: row.id,
            excludeUserId: userId,
          });
        }
        return { row, reviewRecipients, taskRecipients, affectedDates };
      });
      void broadcastExecutiveMeetingNotifications(
        result.reviewRecipients,
        `request_${review.status}`,
        result.row.meetingId,
      );
      if (result.taskRecipients.length > 0) {
        void broadcastExecutiveMeetingNotifications(
          result.taskRecipients,
          "task_assigned",
          result.row.meetingId,
        );
      }
      // Realtime: an approved request mutated the underlying meeting
      // (highlight, reschedule, location change, attendee add/remove,
      // cancel, note). Refetch every affected day so viewers on either
      // the pre- or post-reschedule day stay in sync.
      void emitExecutiveMeetingsDaysChanged(result.affectedDates);
      res.json(result.row);
    } catch (err) {
      const msg = err instanceof Error ? err.message : String(err);
      if (msg === "RACE_ALREADY_REVIEWED") {
        res.status(409).json({ error: "Already reviewed", code: "bad_state" });
        return;
      }
      if (msg.startsWith("APPLY_VALIDATION:")) {
        res
          .status(400)
          .json({ error: msg.slice("APPLY_VALIDATION:".length), code: "validation" });
        return;
      }
      res.status(500).json({ error: msg, code: "server_error" });
    }
  },
);

// =====================================================================
// TASKS
// =====================================================================

router.get(
  "/executive-meetings/tasks",
  requireExecutiveAccess,
  requireTaskView,
  async (req, res): Promise<void> => {
    const status = typeof req.query.status === "string" ? req.query.status : "";
    const mine = req.query.mine === "true" || req.query.mine === "1";
    const meetingIdRaw = typeof req.query.meetingId === "string" ? req.query.meetingId : "";
    const dateRaw = typeof req.query.date === "string" ? req.query.date.trim() : "";
    const dateFromRaw = typeof req.query.dateFrom === "string" ? req.query.dateFrom.trim() : "";
    const dateToRaw = typeof req.query.dateTo === "string" ? req.query.dateTo.trim() : "";
    const assigneeRaw = Number(req.query.assigneeId);
    const userId = req.session.userId!;
    const names = await getRoleNamesForUser(userId);
    const hasBroadView = TASK_BROAD_VIEW_ROLES.some((r) => names.has(r));
    const conds: SQL[] = [];
    if (status && (TASK_STATUSES as readonly string[]).includes(status)) {
      conds.push(eq(executiveMeetingTasksTable.status, status));
    }
    // Plain coordinators (no broad-view role) are hard-scoped to their own
    // assignments — any `mine`/`assigneeId` query parameters they submit are
    // ignored and replaced with `assignedTo = currentUserId`.
    if (!hasBroadView) {
      conds.push(eq(executiveMeetingTasksTable.assignedTo, userId));
    } else {
      if (mine) {
        conds.push(eq(executiveMeetingTasksTable.assignedTo, userId));
      }
      if (Number.isInteger(assigneeRaw) && assigneeRaw > 0) {
        conds.push(eq(executiveMeetingTasksTable.assignedTo, assigneeRaw));
      }
    }
    if (meetingIdRaw) {
      const n = Number(meetingIdRaw);
      if (Number.isInteger(n) && n > 0) {
        conds.push(eq(executiveMeetingTasksTable.meetingId, n));
      }
    }
    if (dateRaw && DATE_RE_LOCAL.test(dateRaw)) {
      const start = new Date(`${dateRaw}T00:00:00.000Z`);
      const end = new Date(start.getTime() + 24 * 60 * 60 * 1000);
      conds.push(gte(executiveMeetingTasksTable.createdAt, start));
      conds.push(lt(executiveMeetingTasksTable.createdAt, end));
    } else {
      if (dateFromRaw && DATE_RE_LOCAL.test(dateFromRaw)) {
        conds.push(
          gte(
            executiveMeetingTasksTable.createdAt,
            new Date(`${dateFromRaw}T00:00:00.000Z`),
          ),
        );
      }
      if (dateToRaw && DATE_RE_LOCAL.test(dateToRaw)) {
        const end = new Date(`${dateToRaw}T00:00:00.000Z`);
        end.setUTCDate(end.getUTCDate() + 1);
        conds.push(lt(executiveMeetingTasksTable.createdAt, end));
      }
    }
    const where = conds.length > 0 ? and(...conds) : undefined;
    const rows = await db
      .select({
        t: executiveMeetingTasksTable,
        assigneeUsername: usersTable.username,
        assigneeDisplayNameAr: usersTable.displayNameAr,
        assigneeDisplayNameEn: usersTable.displayNameEn,
      })
      .from(executiveMeetingTasksTable)
      .leftJoin(usersTable, eq(usersTable.id, executiveMeetingTasksTable.assignedTo))
      .where(where)
      .orderBy(
        asc(executiveMeetingTasksTable.status),
        desc(executiveMeetingTasksTable.createdAt),
      )
      .limit(200);
    res.json({
      tasks: rows.map((row) => ({
        ...row.t,
        assignee: row.assigneeUsername
          ? {
              username: row.assigneeUsername,
              displayNameAr: row.assigneeDisplayNameAr,
              displayNameEn: row.assigneeDisplayNameEn,
            }
          : null,
      })),
    });
  },
);

router.post(
  "/executive-meetings/tasks",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const data = parseBody(res, taskCreateSchema, req.body);
    if (!data) return;
    const userId = req.session.userId!;
    const result = await db.transaction(async (tx) => {
      const insertValues: typeof executiveMeetingTasksTable.$inferInsert = {
        taskType: data.taskType,
        meetingId: data.meetingId ?? null,
        requestId: data.requestId ?? null,
        assignedTo: data.assignedTo ?? null,
        dueAt: data.dueAt ? new Date(data.dueAt) : null,
        notes: data.notes ?? null,
        status: "pending",
      };
      const [row] = await tx
        .insert(executiveMeetingTasksTable)
        .values(insertValues)
        .returning();
      await logAudit(tx, {
        action: "create",
        entityType: "task",
        entityId: row!.id,
        newValue: row!,
        performedBy: userId,
      });
      let recipients: number[] = [];
      if (row?.assignedTo) {
        recipients = await recordExecutiveMeetingNotifications(tx, {
          recipientUserIds: [row.assignedTo],
          meetingId: row.meetingId,
          notificationType: "task_assigned",
          titleAr: "أُسندت إليك مهمة",
          titleEn: "You were assigned a task",
          bodyAr: row.taskType,
          bodyEn: row.taskType,
          relatedType: "executive_meeting_task",
          relatedId: row.id,
          excludeUserId: userId,
        });
      }
      return { row: row!, recipients };
    });
    if (result.recipients.length > 0) {
      void broadcastExecutiveMeetingNotifications(
        result.recipients,
        "task_assigned",
        result.row.meetingId,
      );
    }
    res.status(201).json(result.row);
  },
);

router.patch(
  "/executive-meetings/tasks/:id",
  requireExecutiveAccess,
  requireTaskView,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const userId = req.session.userId!;
    const [existing] = await db
      .select()
      .from(executiveMeetingTasksTable)
      .where(eq(executiveMeetingTasksTable.id, id));
    if (!existing) {
      res.status(404).json({ error: "Task not found", code: "not_found" });
      return;
    }
    const names = await getRoleNamesForUser(userId);
    const isMutator = MUTATE_ROLES.some((r) => names.has(r));
    const isAssignee = existing.assignedTo === userId;
    if (!isMutator && !isAssignee) {
      res.status(403).json({ error: "Forbidden", code: "forbidden" });
      return;
    }
    const data = parseBody(res, taskPatchSchema, req.body);
    if (!data) return;
    const update: Partial<typeof executiveMeetingTasksTable.$inferInsert> = {};
    if (data.status !== undefined) {
      update.status = data.status;
      if (data.status === "completed") update.completedAt = new Date();
      else if (existing.status === "completed") update.completedAt = null;
    }
    if (isMutator) {
      if (data.taskType !== undefined) update.taskType = data.taskType;
      if (data.assignedTo !== undefined) update.assignedTo = data.assignedTo;
      if (data.dueAt !== undefined)
        update.dueAt = data.dueAt && data.dueAt !== "" ? new Date(data.dueAt) : null;
      if (data.notes !== undefined) update.notes = data.notes;
      if (data.meetingId !== undefined) update.meetingId = data.meetingId;
    }
    if (Object.keys(update).length === 0) {
      res.json(existing);
      return;
    }
    const result = await db.transaction(async (tx) => {
      const [row] = await tx
        .update(executiveMeetingTasksTable)
        .set(update)
        .where(eq(executiveMeetingTasksTable.id, id))
        .returning();
      await logAudit(tx, {
        action: "update",
        entityType: "task",
        entityId: id,
        oldValue: existing,
        newValue: row,
        performedBy: userId,
      });
      const reassignRecipients: number[] = [];
      const completionRecipients: number[] = [];
      // Reassignment → notify the new assignee.
      if (
        row &&
        row.assignedTo &&
        row.assignedTo !== existing.assignedTo
      ) {
        const r = await recordExecutiveMeetingNotifications(tx, {
          recipientUserIds: [row.assignedTo],
          meetingId: row.meetingId,
          notificationType: "task_assigned",
          titleAr: "أُسندت إليك مهمة",
          titleEn: "You were assigned a task",
          bodyAr: row.taskType,
          bodyEn: row.taskType,
          relatedType: "executive_meeting_task",
          relatedId: row.id,
          excludeUserId: userId,
        });
        reassignRecipients.push(...r);
      }
      if (
        row &&
        row.status === "completed" &&
        existing.status !== "completed"
      ) {
        // Notify the original requester (if the task came from a request)
        let requesterId: number | null = null;
        if (row.requestId) {
          const [reqOld] = await tx
            .select()
            .from(executiveMeetingRequestsTable)
            .where(eq(executiveMeetingRequestsTable.id, row.requestId));
          requesterId = reqOld?.requestedBy ?? null;
          if (reqOld && reqOld.status !== "done") {
            const [reqNew] = await tx
              .update(executiveMeetingRequestsTable)
              .set({ status: "done" })
              .where(eq(executiveMeetingRequestsTable.id, row.requestId))
              .returning();
            await logAudit(tx, {
              action: "done",
              entityType: "request",
              entityId: row.requestId,
              oldValue: reqOld,
              newValue: reqNew,
              performedBy: userId,
            });
          }
        }
        const completionTargets = Array.from(
          new Set(
            [requesterId, existing.assignedTo].filter(
              (id): id is number => typeof id === "number" && id > 0,
            ),
          ),
        );
        if (completionTargets.length > 0) {
          const r = await recordExecutiveMeetingNotifications(tx, {
            recipientUserIds: completionTargets,
            meetingId: row.meetingId,
            notificationType: "task_completed",
            titleAr: "اكتملت مهمة",
            titleEn: "A task was completed",
            bodyAr: row.taskType,
            bodyEn: row.taskType,
            relatedType: "executive_meeting_task",
            relatedId: row.id,
            excludeUserId: userId,
          });
          completionRecipients.push(...r);
        }
      }
      return { row, reassignRecipients, completionRecipients };
    });
    if (result.reassignRecipients.length > 0) {
      void broadcastExecutiveMeetingNotifications(
        result.reassignRecipients,
        "task_assigned",
        result.row?.meetingId ?? null,
      );
    }
    if (result.completionRecipients.length > 0) {
      void broadcastExecutiveMeetingNotifications(
        result.completionRecipients,
        "task_completed",
        result.row?.meetingId ?? null,
      );
    }
    res.json(result.row);
  },
);

router.delete(
  "/executive-meetings/tasks/:id",
  requireExecutiveAccess,
  requireMutate,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(404).json({ error: "Not found", code: "not_found" });
      return;
    }
    const userId = req.session.userId!;
    const [existing] = await db
      .select()
      .from(executiveMeetingTasksTable)
      .where(eq(executiveMeetingTasksTable.id, id));
    if (!existing) {
      res.status(404).json({ error: "Task not found", code: "not_found" });
      return;
    }
    await db.transaction(async (tx) => {
      await tx
        .delete(executiveMeetingTasksTable)
        .where(eq(executiveMeetingTasksTable.id, id));
      await logAudit(tx, {
        action: "delete",
        entityType: "task",
        entityId: id,
        oldValue: existing,
        performedBy: userId,
      });
    });
    res.status(204).end();
  },
);

// =====================================================================
// AUDIT LOGS
// =====================================================================

router.get(
  "/executive-meetings/audit-logs",
  requireExecutiveAccess,
  requireAdminAudit,
  async (req, res): Promise<void> => {
    const dateRaw = typeof req.query.date === "string" ? req.query.date.trim() : "";
    const dateFromRaw =
      typeof req.query.dateFrom === "string" ? req.query.dateFrom.trim() : "";
    const dateToRaw =
      typeof req.query.dateTo === "string" ? req.query.dateTo.trim() : "";
    const entityType =
      typeof req.query.entityType === "string" ? req.query.entityType.trim() : "";
    const action =
      typeof req.query.action === "string" ? req.query.action.trim() : "";
    const actorRaw = Number(req.query.actorId);
    const actorId = Number.isInteger(actorRaw) && actorRaw > 0 ? actorRaw : null;
    const limitRaw = Number(req.query.limit);
    const limit =
      Number.isFinite(limitRaw) && limitRaw > 0
        ? Math.min(Math.floor(limitRaw), 200)
        : 50;
    const offsetRaw = Number(req.query.offset);
    const offset =
      Number.isFinite(offsetRaw) && offsetRaw > 0 ? Math.floor(offsetRaw) : 0;
    const conds: SQL[] = [];
    if (dateRaw && DATE_RE_LOCAL.test(dateRaw)) {
      const start = new Date(`${dateRaw}T00:00:00.000Z`);
      const end = new Date(start.getTime() + 24 * 60 * 60 * 1000);
      conds.push(gte(executiveMeetingAuditLogsTable.performedAt, start));
      conds.push(lt(executiveMeetingAuditLogsTable.performedAt, end));
    }
    if (dateFromRaw && DATE_RE_LOCAL.test(dateFromRaw)) {
      conds.push(
        gte(
          executiveMeetingAuditLogsTable.performedAt,
          new Date(`${dateFromRaw}T00:00:00.000Z`),
        ),
      );
    }
    if (dateToRaw && DATE_RE_LOCAL.test(dateToRaw)) {
      const end = new Date(`${dateToRaw}T00:00:00.000Z`);
      end.setUTCDate(end.getUTCDate() + 1);
      conds.push(lt(executiveMeetingAuditLogsTable.performedAt, end));
    }
    if (entityType) conds.push(eq(executiveMeetingAuditLogsTable.entityType, entityType));
    if (action) conds.push(eq(executiveMeetingAuditLogsTable.action, action));
    if (actorId) conds.push(eq(executiveMeetingAuditLogsTable.performedBy, actorId));
    const where = conds.length > 0 ? and(...conds) : undefined;
    const [{ count: totalRaw } = { count: 0 }] = await db
      .select({ count: sql<number>`count(*)::int` })
      .from(executiveMeetingAuditLogsTable)
      .where(where);
    const total = Number(totalRaw) || 0;
    const rows = await db
      .select({
        l: executiveMeetingAuditLogsTable,
        actorUsername: usersTable.username,
        actorDisplayNameAr: usersTable.displayNameAr,
        actorDisplayNameEn: usersTable.displayNameEn,
      })
      .from(executiveMeetingAuditLogsTable)
      .leftJoin(usersTable, eq(usersTable.id, executiveMeetingAuditLogsTable.performedBy))
      .where(where)
      .orderBy(desc(executiveMeetingAuditLogsTable.performedAt))
      .limit(limit)
      .offset(offset);
    res.json({
      entries: rows.map((row) => ({
        ...row.l,
        actor: row.actorUsername
          ? {
              username: row.actorUsername,
              displayNameAr: row.actorDisplayNameAr,
              displayNameEn: row.actorDisplayNameEn,
            }
          : null,
      })),
      total,
      limit,
      offset,
      hasMore: offset + rows.length < total,
    });
  },
);

// =====================================================================
// NOTIFICATIONS (placeholder list — visible to any executive read role)
// =====================================================================

router.get(
  "/executive-meetings/notifications",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    // Optional ?date=YYYY-MM-DD filter joins through the meetings table so
    // the schedule's "selected date" can scope the notifications view, as
    // required by Phase 2.
    const dateRaw = typeof req.query.date === "string" ? req.query.date.trim() : "";
    const conds: SQL[] = [];
    let meetingIdsForDate: number[] | null = null;
    if (dateRaw && DATE_RE_LOCAL.test(dateRaw)) {
      const meetingsOnDate = await db
        .select({ id: executiveMeetingsTable.id })
        .from(executiveMeetingsTable)
        .where(eq(executiveMeetingsTable.meetingDate, dateRaw));
      meetingIdsForDate = meetingsOnDate.map((m) => m.id);
      if (meetingIdsForDate.length === 0) {
        res.json({ notifications: [] });
        return;
      }
      conds.push(
        inArray(executiveMeetingNotificationsTable.meetingId, meetingIdsForDate),
      );
    }
    const where = conds.length > 0 ? and(...conds) : undefined;
    const rows = await db
      .select({
        n: executiveMeetingNotificationsTable,
        userUsername: usersTable.username,
        userDisplayNameAr: usersTable.displayNameAr,
        userDisplayNameEn: usersTable.displayNameEn,
      })
      .from(executiveMeetingNotificationsTable)
      .leftJoin(usersTable, eq(usersTable.id, executiveMeetingNotificationsTable.userId))
      .where(where)
      .orderBy(desc(executiveMeetingNotificationsTable.createdAt))
      .limit(200);
    res.json({
      notifications: rows.map((row) => ({
        ...row.n,
        user: row.userUsername
          ? {
              username: row.userUsername,
              displayNameAr: row.userDisplayNameAr,
              displayNameEn: row.userDisplayNameEn,
            }
          : null,
      })),
    });
  },
);

// =====================================================================
// NOTIFICATION PREFERENCES (per-user, per-event-type)
// =====================================================================

// One row per (notificationType, channel) toggle. Missing rows mean
// "default on" — see executive_meeting_notification_prefs in the schema.
const notificationPrefEntrySchema = z.object({
  notificationType: z.enum(EXECUTIVE_MEETING_NOTIFICATION_TYPES),
  inApp: z.boolean(),
  email: z.boolean(),
});

const notificationPrefsBodySchema = z.object({
  prefs: z.array(notificationPrefEntrySchema).max(64),
});

// GET returns the current user's preferences merged with defaults so the
// frontend can render every event type as a toggle without having to
// know which rows exist. `types` echoes the canonical list so the UI
// stays in sync if a new event type is added on the server.
router.get(
  "/executive-meetings/notification-prefs",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const userId = req.session.userId!;
    const rows = await db
      .select({
        notificationType: executiveMeetingNotificationPrefsTable.notificationType,
        inApp: executiveMeetingNotificationPrefsTable.inApp,
        email: executiveMeetingNotificationPrefsTable.email,
      })
      .from(executiveMeetingNotificationPrefsTable)
      .where(eq(executiveMeetingNotificationPrefsTable.userId, userId));
    const byType = new Map(rows.map((r) => [r.notificationType, r]));
    const prefs = EXECUTIVE_MEETING_NOTIFICATION_TYPES.map((type) => {
      const existing = byType.get(type);
      return {
        notificationType: type,
        inApp: existing ? existing.inApp : true,
        email: existing ? existing.email : true,
      };
    });
    res.json({
      types: EXECUTIVE_MEETING_NOTIFICATION_TYPES,
      prefs,
    });
  },
);

// PUT replaces all preferences for the current user. We upsert each
// supplied row and leave any other rows untouched (the default-on
// fallback handles unspecified types). The request only carries
// preferences the user actually toggled, so this is a partial update.
router.put(
  "/executive-meetings/notification-prefs",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const data = parseBody(res, notificationPrefsBodySchema, req.body);
    if (!data) return;
    const userId = req.session.userId!;
    if (data.prefs.length === 0) {
      res.json({ ok: true, count: 0 });
      return;
    }
    await db.transaction(async (tx) => {
      for (const p of data.prefs) {
        await tx
          .insert(executiveMeetingNotificationPrefsTable)
          .values({
            userId,
            notificationType: p.notificationType,
            inApp: p.inApp,
            email: p.email,
          })
          .onConflictDoUpdate({
            target: [
              executiveMeetingNotificationPrefsTable.userId,
              executiveMeetingNotificationPrefsTable.notificationType,
            ],
            set: {
              inApp: p.inApp,
              email: p.email,
              updatedAt: new Date(),
            },
          });
      }
    });
    res.json({ ok: true, count: data.prefs.length });
  },
);

// =====================================================================
// PDF GENERATION (server-side)
// =====================================================================

// Resolve the user's effective font preferences exactly the way the
// frontend does: user-scope row wins, otherwise global, otherwise the
// schema defaults.
async function resolveFontPrefsForUser(userId: number): Promise<PdfFontPrefs> {
  const rows = await db
    .select()
    .from(executiveMeetingFontSettingsTable)
    .where(
      or(
        eq(executiveMeetingFontSettingsTable.scope, "global"),
        and(
          eq(executiveMeetingFontSettingsTable.scope, "user"),
          eq(executiveMeetingFontSettingsTable.userId, userId),
        ),
      ),
    );
  const userRow = rows.find((r) => r.scope === "user" && r.userId === userId);
  const globalRow = rows.find((r) => r.scope === "global");
  const eff = userRow ?? globalRow;
  return {
    fontFamily: eff?.fontFamily ?? "system",
    fontSize: eff?.fontSize ?? 14,
    fontWeight: (eff?.fontWeight as PdfFontPrefs["fontWeight"]) ?? "regular",
    alignment: (eff?.alignment as PdfFontPrefs["alignment"]) ?? "start",
  };
}

// Upload bytes to object storage by signing a PUT URL and streaming the
// buffer through `fetch`. Returns the canonical /objects/<id> path that
// GET /api/storage/objects/* knows how to serve.
async function uploadPdfToStorage(buf: Buffer): Promise<string> {
  const objectStorage = new ObjectStorageService();
  const uploadUrl = await objectStorage.getObjectEntityUploadURL();
  const putRes = await fetch(uploadUrl, {
    method: "PUT",
    body: buf,
    headers: {
      "Content-Type": "application/pdf",
      "Content-Length": String(buf.byteLength),
    },
  });
  if (!putRes.ok) {
    const body = await putRes.text().catch(() => "");
    throw new Error(
      `Failed to upload PDF to object storage (${putRes.status}): ${body.slice(0, 200)}`,
    );
  }
  // Storage returns the signed URL we PUT to — normalize back to
  // /objects/<id> so we can serve it via GET /api/storage/objects/*.
  return objectStorage.normalizeObjectEntityPath(uploadUrl.split("?")[0]!);
}

router.get(
  "/executive-meetings/pdf",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const dateRaw = typeof req.query.date === "string" ? req.query.date.trim() : "";
    if (!dateRaw || !DATE_RE_LOCAL.test(dateRaw)) {
      res
        .status(400)
        .json({ error: "Invalid 'date' (expected YYYY-MM-DD)", code: "bad_date" });
      return;
    }
    const lang: "ar" | "en" =
      typeof req.query.lang === "string" && req.query.lang === "en" ? "en" : "ar";
    const userId = req.session.userId!;

    const meetings = await db
      .select()
      .from(executiveMeetingsTable)
      .where(eq(executiveMeetingsTable.meetingDate, dateRaw))
      .orderBy(asc(executiveMeetingsTable.dailyNumber));
    let attendeesByMeeting = new Map<number, ExecutiveMeetingAttendee[]>();
    if (meetings.length > 0) {
      const ids = meetings.map((m) => m.id);
      const attendees = await db
        .select()
        .from(executiveMeetingAttendeesTable)
        .where(inArray(executiveMeetingAttendeesTable.meetingId, ids))
        .orderBy(
          asc(executiveMeetingAttendeesTable.meetingId),
          asc(executiveMeetingAttendeesTable.sortOrder),
        );
      for (const a of attendees) {
        const arr = attendeesByMeeting.get(a.meetingId) ?? [];
        arr.push(a);
        attendeesByMeeting.set(a.meetingId, arr);
      }
    }

    const font = await resolveFontPrefsForUser(userId);
    const labels =
      lang === "ar"
        ? {
            title: "جدول الاجتماعات التنفيذية",
            no: "م",
            meeting: "الاجتماع",
            attendees: "الحضور",
            time: "الوقت",
            none: "لا توجد اجتماعات في هذا اليوم.",
          }
        : {
            title: "Executive Meetings Schedule",
            no: "#",
            meeting: "Meeting",
            attendees: "Attendees",
            time: "Time",
            none: "No meetings on this day.",
          };

    let pdf: Buffer;
    try {
      pdf = await renderSchedulePdf({
        date: dateRaw,
        lang,
        font,
        labels,
        meetings: meetings.map((m) => ({
          dailyNumber: m.dailyNumber,
          titleAr: m.titleAr,
          titleEn: m.titleEn,
          startTime: m.startTime,
          endTime: m.endTime,
          location: m.location,
          isHighlighted: m.isHighlighted ?? 0,
          attendees: (attendeesByMeeting.get(m.id) ?? []).map((a) => ({
            name: a.name,
            title: a.title,
            attendanceType: a.attendanceType,
            // Forward the row kind so the renderer can render subheadings
            // as labels rather than numbered attendees.
            kind: a.kind,
          })),
        })),
      });
    } catch (err) {
      // Fail loudly so callers see a real error instead of a corrupt PDF.
      console.error("[executive-meetings] PDF render failed", err);
      res.status(500).json({
        error: "Failed to generate PDF",
        code: "pdf_render_failed",
        detail: err instanceof Error ? err.message : String(err),
      });
      return;
    }

    // Upload + archive. If storage upload fails (e.g. sidecar offline)
    // we still serve the PDF inline so the user is never blocked, but
    // we record the archive with a synthetic path so the failure is
    // visible in the audit trail.
    let storagePath = `inline:${dateRaw}`;
    try {
      storagePath = await uploadPdfToStorage(pdf);
    } catch (err) {
      console.error("[executive-meetings] PDF upload failed; serving inline", err);
    }

    try {
      await db.transaction(async (tx) => {
        const [{ value: maxV }] = await tx
          .select({
            value: sql<number>`coalesce(max(${executiveMeetingPdfArchivesTable.version}), 0)`,
          })
          .from(executiveMeetingPdfArchivesTable)
          .where(eq(executiveMeetingPdfArchivesTable.archiveDate, dateRaw));
        const insertValues: typeof executiveMeetingPdfArchivesTable.$inferInsert = {
          archiveDate: dateRaw,
          filePath: storagePath,
          version: (maxV ?? 0) + 1,
          byteSize: pdf.byteLength,
          generatedBy: userId,
        };
        const [row] = await tx
          .insert(executiveMeetingPdfArchivesTable)
          .values(insertValues)
          .returning();
        await logAudit(tx, {
          action: "create",
          entityType: "pdf_archive",
          entityId: row!.id,
          newValue: row!,
          performedBy: userId,
        });
      });
    } catch (err) {
      console.error("[executive-meetings] PDF archive insert failed", err);
    }

    res.setHeader("Content-Type", "application/pdf");
    res.setHeader(
      "Content-Disposition",
      `attachment; filename="executive-meetings-${dateRaw}.pdf"`,
    );
    res.setHeader("Content-Length", String(pdf.byteLength));
    res.setHeader("Cache-Control", "no-store");
    res.end(pdf);
  },
);

// =====================================================================
// PDF ARCHIVES
// =====================================================================

router.get(
  "/executive-meetings/pdf-archives",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const dateRaw = typeof req.query.date === "string" ? req.query.date.trim() : "";
    const conds: SQL[] = [];
    if (dateRaw && DATE_RE_LOCAL.test(dateRaw)) {
      conds.push(eq(executiveMeetingPdfArchivesTable.archiveDate, dateRaw));
    }
    const where = conds.length > 0 ? and(...conds) : undefined;
    const rows = await db
      .select({
        a: executiveMeetingPdfArchivesTable,
        username: usersTable.username,
        displayNameAr: usersTable.displayNameAr,
        displayNameEn: usersTable.displayNameEn,
      })
      .from(executiveMeetingPdfArchivesTable)
      .leftJoin(
        usersTable,
        eq(usersTable.id, executiveMeetingPdfArchivesTable.generatedBy),
      )
      .where(where)
      .orderBy(desc(executiveMeetingPdfArchivesTable.generatedAt))
      .limit(200);
    res.json({
      archives: rows.map((row) => ({
        ...row.a,
        generator: row.username
          ? {
              username: row.username,
              displayNameAr: row.displayNameAr,
              displayNameEn: row.displayNameEn,
            }
          : null,
      })),
    });
  },
);

router.post(
  "/executive-meetings/pdf-archives",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const data = parseBody(res, pdfArchiveCreateSchema, req.body);
    if (!data) return;
    const filePath = data.filePath?.trim() || `print:${data.archiveDate}`;
    const userId = req.session.userId!;
    const created = await db.transaction(async (tx) => {
      const [{ value: maxV }] = await tx
        .select({
          value: sql<number>`coalesce(max(${executiveMeetingPdfArchivesTable.version}), 0)`,
        })
        .from(executiveMeetingPdfArchivesTable)
        .where(eq(executiveMeetingPdfArchivesTable.archiveDate, data.archiveDate));
      const insertValues: typeof executiveMeetingPdfArchivesTable.$inferInsert = {
        archiveDate: data.archiveDate,
        filePath,
        version: (maxV ?? 0) + 1,
        generatedBy: userId,
      };
      const [row] = await tx
        .insert(executiveMeetingPdfArchivesTable)
        .values(insertValues)
        .returning();
      await logAudit(tx, {
        action: "create",
        entityType: "pdf_archive",
        entityId: row!.id,
        newValue: row!,
        performedBy: userId,
      });
      return row;
    });
    res.status(201).json(created);
  },
);

// =====================================================================
// FONT SETTINGS
// =====================================================================

router.get(
  "/executive-meetings/font-settings",
  requireExecutiveAccess,
  async (req, res): Promise<void> => {
    const userId = req.session.userId!;
    const rows = await db
      .select()
      .from(executiveMeetingFontSettingsTable)
      .where(
        or(
          eq(executiveMeetingFontSettingsTable.scope, "global"),
          and(
            eq(executiveMeetingFontSettingsTable.scope, "user"),
            eq(executiveMeetingFontSettingsTable.userId, userId),
          ),
        ),
      );
    const global = rows.find((r) => r.scope === "global") ?? null;
    const user = rows.find((r) => r.scope === "user" && r.userId === userId) ?? null;
    res.json({ global, user });
  },
);

// Shared handler used by both PUT (canonical) and PATCH (alias) so we don't
// rely on Express re-dispatch tricks — both methods run the exact same logic.
const upsertFontSettingsHandler = async (
  req: import("express").Request,
  res: import("express").Response,
): Promise<void> => {
  const userId = req.session.userId!;
  const data = parseBody(res, fontSettingsSchema, req.body);
  if (!data) return;
  if (data.scope === "global") {
    const names = await getRoleNamesForUser(userId);
    const ok = APPROVE_ROLES.some((r) => names.has(r));
    if (!ok) {
      res.status(403).json({ error: "Forbidden", code: "forbidden" });
      return;
    }
  }
  const targetUserId = data.scope === "global" ? null : userId;
  const result = await db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(executiveMeetingFontSettingsTable)
      .where(
        and(
          eq(executiveMeetingFontSettingsTable.scope, data.scope),
          targetUserId === null
            ? isNull(executiveMeetingFontSettingsTable.userId)
            : eq(executiveMeetingFontSettingsTable.userId, targetUserId),
        ),
      );
    let row;
    if (existing) {
      const [updated] = await tx
        .update(executiveMeetingFontSettingsTable)
        .set({
          fontFamily: data.fontFamily,
          fontSize: data.fontSize,
          fontWeight: data.fontWeight,
          alignment: data.alignment,
        })
        .where(eq(executiveMeetingFontSettingsTable.id, existing.id))
        .returning();
      row = updated;
    } else {
      const insertValues: typeof executiveMeetingFontSettingsTable.$inferInsert = {
        scope: data.scope,
        userId: targetUserId,
        fontFamily: data.fontFamily,
        fontSize: data.fontSize,
        fontWeight: data.fontWeight,
        alignment: data.alignment,
      };
      const [inserted] = await tx
        .insert(executiveMeetingFontSettingsTable)
        .values(insertValues)
        .returning();
      row = inserted;
    }
    await logAudit(tx, {
      action: existing ? "update" : "create",
      entityType: "font_settings",
      entityId: row?.id ?? null,
      oldValue: existing ?? null,
      newValue: row ?? null,
      performedBy: userId,
    });
    return row;
  });
  res.json(result);
};

router.put(
  "/executive-meetings/font-settings",
  requireExecutiveAccess,
  upsertFontSettingsHandler,
);

// Backwards-compatible PATCH alias — uses the same handler directly so it
// works whether the client sends PATCH or PUT.
router.patch(
  "/executive-meetings/font-settings",
  requireExecutiveAccess,
  upsertFontSettingsHandler,
);

export { requireApprove };
export default router;
