import type { Request } from "express";
import { randomUUID } from "node:crypto";
import { one, query, transaction } from "../../db/client.js";
import { audit } from "../../lib/audit.js";
import { badRequest, notFound } from "../../lib/errors.js";
import { governorateScope } from "../../middleware/rbac.js";

export interface MemberRow {
  id: number;
  public_uuid: string;
  full_name: string;
  governorate_id: number | null;
  governorate_name: string | null;
  formal_status_id: number;
  formal_status_ar: string;
  formal_status_code: string;
  record_source: string;
  source_record_ref: string | null;
  verified_at: string | null;
  is_demo: boolean;
  deleted_at: string | null;
  archive_reason: string | null;
  created_at: string;
  updated_at: string;
  has_contact: boolean;
  suspension_until: string | null;
  secretariat_id: number | null;
  secretariat_name: string | null;
  membership_no: string | null;
  system_no: string | null;
  university: string | null;
  faculty: string | null;
  qualification: string | null;
  graduation_year: number | null;
  has_photo: boolean;
}

/** A member row plus the computed suspension-expiry flag (display only — never auto-changes status). */
export type MemberView = MemberRow & { suspension_expired: boolean };

/** Today's date (YYYY-MM-DD) in the org timezone — date-only comparisons stay deterministic. */
function cairoToday(): string {
  return new Intl.DateTimeFormat("en-CA", { timeZone: "Africa/Cairo" }).format(new Date());
}

function withSuspensionFlag(row: MemberRow): MemberView {
  return {
    ...row,
    suspension_expired:
      row.formal_status_code === "suspended" &&
      row.suspension_until !== null &&
      row.suspension_until < cairoToday(),
  };
}

const SECRETARIAT_JOIN = `
  LEFT JOIN LATERAL (
    SELECT u.id AS unit_id
      FROM org_positions p JOIN org_units u ON u.id = p.org_unit_id
     WHERE p.member_id = m.id AND p.deleted_at IS NULL AND p.ended_on IS NULL
       AND u.deleted_at IS NULL AND u.unit_kind = 'governorate'
     LIMIT 1
  ) sec ON TRUE
`;

const SELECT_BASE = `
  SELECT m.id, m.public_uuid::text AS public_uuid, m.full_name,
         m.governorate_id, g.name_ar AS governorate_name,
         m.formal_membership_status_id AS formal_status_id, s.name_ar AS formal_status_ar, s.code AS formal_status_code,
         m.record_source, m.source_record_ref, m.verified_at::text, m.is_demo,
         m.deleted_at::text, m.archive_reason, m.created_at::text, m.updated_at::text,
         m.suspension_until::text AS suspension_until,
         (mc.id IS NOT NULL) AS has_contact,
         sec.unit_id AS secretariat_id, sec.unit_name AS secretariat_name,
         m.membership_no, m.system_no, m.university, m.faculty, m.qualification, m.graduation_year,
         (m.photo IS NOT NULL) AS has_photo
  FROM members m
  LEFT JOIN governorates g ON g.id = m.governorate_id
  LEFT JOIN statuses s ON s.id = m.formal_membership_status_id
  LEFT JOIN member_contacts mc ON mc.member_id = m.id
  LEFT JOIN LATERAL (
    SELECT u.id AS unit_id, u.name_ar AS unit_name
      FROM org_positions p JOIN org_units u ON u.id = p.org_unit_id
     WHERE p.member_id = m.id AND p.deleted_at IS NULL AND p.ended_on IS NULL
       AND u.deleted_at IS NULL AND u.unit_kind = 'governorate'
     LIMIT 1
  ) sec ON TRUE
`;

export async function listMembers(
  req: Request,
  opts: { page: number; pageSize: number; search?: string; secretariatId?: number },
): Promise<{ items: MemberRow[]; total: number }> {
  const scope = governorateScope(req);
  const conditions = ["m.deleted_at IS NULL"];
  const params: unknown[] = [];

  // Scope INSIDE the SQL — a scoped secretary cannot reach other governorates by editing
  // the request (filters, pagination, or direct identifiers)
  if (scope !== null) {
    params.push(scope);
    conditions.push(`m.governorate_id = ANY($${params.length}::int[])`);
  }
  if (opts.search && opts.search.trim()) {
    params.push(`%${opts.search.trim()}%`);
    conditions.push(`(m.full_name ILIKE $${params.length} OR m.public_uuid::text ILIKE $${params.length} OR m.membership_no::text ILIKE $${params.length})`);
  }
  if (opts.secretariatId) {
    // secretariat = first-class reporting dimension (owner decision 2026-10-28)
    params.push(opts.secretariatId);
    conditions.push(`sec.unit_id = $${params.length}`);
  }

  const where = `WHERE ${conditions.join(" AND ")}`;
  params.push(opts.pageSize, (opts.page - 1) * opts.pageSize);
  const rows = await query<MemberRow>(
    `${SELECT_BASE} ${where} ORDER BY m.id DESC LIMIT $${params.length - 1} OFFSET $${params.length}`,
    params,
  );
  const total = await one<{ count: string }>(`SELECT COUNT(*)::text AS count FROM members m ${SECRETARIAT_JOIN} ${where}`, params.slice(0, -2));
  return { items: rows.rows.map(withSuspensionFlag), total: Number(total?.count ?? 0) };
}

export async function getMemberByUuid(req: Request, uuid: string, includeArchived = false): Promise<MemberView | null> {
  const scope = governorateScope(req);
  const params: unknown[] = [uuid];
  const conditions = ["m.public_uuid = $1::uuid"];
  if (!includeArchived) conditions.push("m.deleted_at IS NULL");
  if (scope !== null) {
    params.push(scope);
    conditions.push(`m.governorate_id = ANY($${params.length}::int[])`);
  }
  const row = await one<MemberRow>(`${SELECT_BASE} WHERE ${conditions.join(" AND ")}`, params);
  return row ? withSuspensionFlag(row) : null;
}

export interface CreateMemberInput {
  fullName: string;
  governorateId?: number | null;
  recordSource?: string;
  sourceRecordRef?: string | null;
  membershipNo?: string | null;
}

/** Official membership number: unique among live members; format is a staff convention. */
export async function assertMembershipNoFree(no: string, exceptMemberId?: number): Promise<void> {
  const dup = await one<{ id: number }>(
    `SELECT id FROM members WHERE membership_no = $1 AND deleted_at IS NULL AND ($2::int IS NULL OR id <> $2::int)`,
    [no, exceptMemberId ?? null],
  );
  if (dup) throw badRequest("رقم العضوية مستخدم بالفعل لعضو آخر", "membership_no_taken");
}

export async function createMember(req: Request, input: CreateMemberInput): Promise<MemberRow> {
  const membershipNo = input.membershipNo?.trim() || null;
  if (membershipNo) {
    if (!/^[1-9][0-9]{0,9}$/.test(membershipNo)) {
      throw badRequest("رقم العضوية يجب أن يكون رقمًا طبيعيًا (بدون أصفار بادئة)", "invalid_membership_no");
    }
    await assertMembershipNoFree(membershipNo);
  }
  if (input.governorateId != null) {
    const gov = await one(`SELECT id FROM governorates WHERE id = $1`, [input.governorateId]);
    if (!gov) throw badRequest("المحافظة المحددة غير موجودة", "invalid_governorate");
  }
  const uuid = randomUUID(); // UUIDv4 — non-sequential, generated once, immutable
  const statusRow = await one<{ id: number }>(
    `SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
      WHERE sg.code = 'member_formal_status' AND s.code = 'unspecified'`,
  );
  if (!statusRow) throw badRequest("قائمة حالات العضوية غير مهيأة — راجع مسؤول النظام", "status_catalog_missing");

  const row = await transaction(async (client) => {
    const inserted = await client.query<MemberRow>(
      `INSERT INTO members (public_uuid, full_name, governorate_id, formal_membership_status_id, record_source, source_record_ref, is_demo, created_by, membership_no)
       VALUES ($1::uuid, $2, $3, $4, $5, $6, TRUE, $7, $8) RETURNING id`,
      [uuid, input.fullName.trim(), input.governorateId ?? null, statusRow.id, input.recordSource ?? "manual_entry", input.sourceRecordRef ?? null, req.user!.id, membershipNo],
    );
    const memberId = (inserted.rows[0] as { id: number }).id;
    await client.query(
      `INSERT INTO member_status_history (member_id, from_status_id, to_status_id, reason, created_by)
       VALUES ($1, NULL, $2, 'تعيين الحالة الافتراضية الآمنة عند الإنشاء (G-3)', $3)`,
      [memberId, statusRow.id, req.user!.id],
    );
    await audit(
      {
        actorUserId: req.user!.id,
        action: "members.created",
        entityType: "member",
        entityId: memberId,
        changedFields: { fullName: input.fullName.trim(), governorateId: input.governorateId ?? null, publicUuid: uuid, membershipNo },
        ip: req.ip ?? null,
        userAgent: req.headers["user-agent"] ?? null,
      },
      client,
    );
    return memberId;
  });

  const created = await getMemberByUuid(req, uuid, true);
  if (!created) throw new Error("member vanished after insert");
  void row;
  return created;
}

export async function updateMember(
  req: Request,
  uuid: string,
  patch: { fullName?: string; governorateId?: number | null; sourceRecordRef?: string | null; membershipNo?: string | null },
): Promise<MemberRow> {
  const existing = await getMemberByUuid(req, uuid);
  if (!existing) throw notFound("العضو غير موجود أو خارج نطاقك");
  if (patch.governorateId !== undefined && patch.governorateId !== null) {
    const gov = await one(`SELECT id FROM governorates WHERE id = $1`, [patch.governorateId]);
    if (!gov) throw badRequest("المحافظة المحددة غير موجودة", "invalid_governorate");
  }
  const changed: Record<string, unknown> = {};
  const sets: string[] = ["updated_at = now()", "updated_by = $UPD"];
  const params: unknown[] = [];
  const add = (col: string, value: unknown) => {
    params.push(value);
    sets.push(`${col} = $${params.length}`);
  };

  if (patch.fullName !== undefined && patch.fullName.trim() !== existing.full_name) {
    changed.fullName = { from: existing.full_name, to: patch.fullName.trim() };
    add("full_name", patch.fullName.trim());
  }
  if (patch.governorateId !== undefined) {
    const to = patch.governorateId ?? null;
    if (to !== existing.governorate_id) {
      changed.governorateId = { from: existing.governorate_id, to };
      add("governorate_id", to);
    }
  }
  if (patch.membershipNo !== undefined) {
    const to = patch.membershipNo?.trim() || null;
    if (to) {
      if (!/^[1-9][0-9]{0,9}$/.test(to)) {
        throw badRequest("رقم العضوية يجب أن يكون رقمًا طبيعيًا (بدون أصفار بادئة)", "invalid_membership_no");
      }
      await assertMembershipNoFree(to, existing.id);
    }
    if (to !== existing.membership_no) {
      changed.membershipNo = { from: existing.membership_no, to };
      add("membership_no", to);
    }
  }
  if (patch.sourceRecordRef !== undefined) {
    const to = patch.sourceRecordRef ?? null;
    if (to !== existing.source_record_ref) {
      changed.sourceRecordRef = { from: existing.source_record_ref, to };
      add("source_record_ref", to);
    }
  }
  if (Object.keys(changed).length === 0) throw badRequest("لا تغييرات مقدمة", "no_changes");

  params.push(req.user!.id, existing.id);
  const upd = sets.join(", ").replace("$UPD", `$${params.length - 1}`);
  await query(`UPDATE members SET ${upd} WHERE id = $${params.length}`, params);
  await audit({
    actorUserId: req.user!.id,
    action: "members.updated",
    entityType: "member",
    entityId: existing.id,
    changedFields: changed,
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
  const updated = await getMemberByUuid(req, uuid, true);
  return updated!;
}

export async function changeFormalStatus(
  req: Request,
  uuid: string,
  input: { toStatusCode: string; reason: string; decisionRef?: string | null; decidedBy?: string | null; suspensionUntil?: string | null },
): Promise<void> {
  const existing = await getMemberByUuid(req, uuid);
  if (!existing) throw notFound("العضو غير موجود أو خارج نطاقك");
  const target = await one<{ id: number; name_ar: string; code: string }>(
    `SELECT s.id, s.name_ar, s.code FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
      WHERE sg.code = 'member_formal_status' AND s.code = $1`,
    [input.toStatusCode],
  );
  if (!target) throw badRequest("حالة العضوية المطلوبة غير معرّفة — القيم المعتمدة فقط (G-3)", "invalid_status");
  if (target.id === existing.formal_status_id) throw badRequest("العضو في هذه الحالة بالفعل", "same_status");

  // Suspension duration (G-3 decision 2026-10-27): optional date recorded with the suspension.
  // Null = indefinite («حتى إشعار آخر»). Expiry is flagged for humans — status NEVER auto-changes.
  let suspensionUntil: string | null = null;
  if (input.suspensionUntil != null) {
    if (target.code !== "suspended") {
      throw badRequest("مدة الإيقاف تُسجل مع حالة «موقوف» فقط", "suspension_until_requires_suspended");
    }
    if (!/^\d{4}-\d{2}-\d{2}$/.test(input.suspensionUntil) || Number.isNaN(Date.parse(input.suspensionUntil))) {
      throw badRequest("تاريخ نهاية الإيقاف غير صالح (YYYY-MM-DD)", "invalid_suspension_until");
    }
    if (input.suspensionUntil < cairoToday()) {
      throw badRequest("تاريخ نهاية الإيقاف يجب أن يكون اليوم أو بعده", "suspension_until_in_past");
    }
    suspensionUntil = input.suspensionUntil;
  }

  await transaction(async (client) => {
    await client.query(
      `UPDATE members SET formal_membership_status_id = $2, suspension_until = $3, updated_at = now(), updated_by = $4 WHERE id = $1`,
      [existing.id, target.id, suspensionUntil, req.user!.id],
    );
    await client.query(
      `INSERT INTO member_status_history (member_id, from_status_id, to_status_id, decision_ref, decided_by, reason, suspension_until, created_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
      [existing.id, existing.formal_status_id, target.id, input.decisionRef ?? null, input.decidedBy ?? null, input.reason, suspensionUntil, req.user!.id],
    );
    await audit(
      {
        actorUserId: req.user!.id,
        action: "members.status_changed",
        entityType: "member",
        entityId: existing.id,
        changedFields: { from: existing.formal_status_code, to: input.toStatusCode, decisionRef: input.decisionRef ?? null, suspensionUntil },
        context: { reason: input.reason },
        ip: req.ip ?? null,
        userAgent: req.headers["user-agent"] ?? null,
      },
      client,
    );
  });
}

export async function archiveMember(req: Request, uuid: string, reason: string): Promise<void> {
  const existing = await getMemberByUuid(req, uuid);
  if (!existing) throw notFound("العضو غير موجود أو خارج نطاقك");
  await query(
    `UPDATE members SET deleted_at = now(), archive_reason = $2, updated_at = now(), updated_by = $3 WHERE id = $1`,
    [existing.id, reason, req.user!.id],
  );
  await audit({
    actorUserId: req.user!.id,
    action: "members.archived",
    entityType: "member",
    entityId: existing.id,
    context: { reason },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
}

export interface ContactInput {
  phonePrimary?: string | null;
  phoneAlt?: string | null;
  email?: string | null;
  preferredChannel?: string | null;
  consentContact?: boolean;
  consentChannel?: string | null;
  consentNoticeVersion?: string | null;
}

export async function upsertContact(req: Request, uuid: string, input: ContactInput): Promise<void> {
  const existing = await getMemberByUuid(req, uuid);
  if (!existing) throw notFound("العضو غير موجود أو خارج نطاقك");
  const normalizePhone = (p: string | null | undefined) => (p && p.trim() ? p.replace(/[^0-9]/g, "") : null);

  const phonePrimary = normalizePhone(input.phonePrimary);
  const phoneAlt = normalizePhone(input.phoneAlt);
  if (phonePrimary && (phonePrimary.length < 8 || phonePrimary.length > 15)) throw badRequest("رقم الهاتف الأساسي غير صالح (8–15 رقمًا)", "invalid_phone");
  if (phoneAlt && (phoneAlt.length < 8 || phoneAlt.length > 15)) throw badRequest("رقم الهاتف البديل غير صالح", "invalid_phone");
  if (input.email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(input.email)) throw badRequest("البريد الإلكتروني غير صالح", "invalid_email");

  const consentAt = input.consentContact === true ? new Date() : null;
  await query(
    `INSERT INTO member_contacts (member_id, phone_primary, phone_alt, email, preferred_channel,
        consent_contact_at, consent_channel, consent_notice_version, created_by, updated_by)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9)
     ON CONFLICT (member_id) DO UPDATE SET
       phone_primary = EXCLUDED.phone_primary, phone_alt = EXCLUDED.phone_alt, email = EXCLUDED.email,
       preferred_channel = EXCLUDED.preferred_channel,
       consent_contact_at = COALESCE(EXCLUDED.consent_contact_at, member_contacts.consent_contact_at),
       consent_channel = EXCLUDED.consent_channel,
       consent_notice_version = EXCLUDED.consent_notice_version,
       consent_withdrawn_at = CASE WHEN EXCLUDED.consent_contact_at IS NULL THEN member_contacts.consent_withdrawn_at ELSE NULL END,
       updated_at = now(), updated_by = EXCLUDED.updated_by`,
    [existing.id, phonePrimary, phoneAlt, input.email?.trim() || null, input.preferredChannel ?? null,
     consentAt, input.consentChannel ?? null, input.consentNoticeVersion ?? null, req.user!.id],
  );
  await audit({
    actorUserId: req.user!.id,
    action: "members.contact_updated",
    entityType: "member",
    entityId: existing.id,
    // minimization: which fields changed, never the values (AUD-REQ-005)
    changedFields: {
      phonePrimary: phonePrimary ? "set" : "cleared",
      phoneAlt: phoneAlt ? "set" : "cleared",
      email: input.email ? "set" : "cleared",
      consentRecorded: input.consentContact === true,
    },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
}

/** Restricted read: caller MUST hold members.view_contact_details; every read is audited (MEM-REQ-003). */
export async function revealContact(req: Request, uuid: string): Promise<{
  phonePrimary: string | null; phoneAlt: string | null; email: string | null;
  preferredChannel: string | null; consent: Record<string, unknown>;
}> {
  const existing = await getMemberByUuid(req, uuid);
  if (!existing) throw notFound("العضو غير موجود أو خارج نطاقك");
  const row = await one<{
    phone_primary: string | null; phone_alt: string | null; email: string | null;
    preferred_channel: string | null; consent_contact_at: string | null;
    consent_channel: string | null; consent_notice_version: string | null;
    consent_withdrawn_at: string | null; contact_verification_status: string;
  }>(`SELECT * FROM member_contacts WHERE member_id = $1`, [existing.id]);
  if (!row) throw notFound("لا توجد بيانات اتصال مسجلة لهذا العضو");

  await audit({
    actorUserId: req.user!.id,
    action: "members.contact_viewed",
    entityType: "member",
    entityId: existing.id,
    context: { fields: ["phonePrimary", "phoneAlt", "email"] },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
  return {
    phonePrimary: row.phone_primary,
    phoneAlt: row.phone_alt,
    email: row.email,
    preferredChannel: row.preferred_channel,
    consent: {
      contactAt: row.consent_contact_at,
      channel: row.consent_channel,
      noticeVersion: row.consent_notice_version,
      withdrawnAt: row.consent_withdrawn_at,
    },
  };
}

/** Masked projection for ordinary profile views — never returns raw contact values. */
export async function getContactMasked(req: Request, uuid: string): Promise<Record<string, unknown> | null> {
  const existing = await getMemberByUuid(req, uuid);
  if (!existing) throw notFound("العضو غير موجود أو خارج نطاقك");
  const row = await one<{ phone_primary: string | null; email: string | null; contact_verification_status: string }>(
    `SELECT phone_primary, email, contact_verification_status FROM member_contacts WHERE member_id = $1`,
    [existing.id],
  );
  if (!row) return null;
  return {
    hasPhone: row.phone_primary !== null,
    hasAltPhone: false,
    hasEmail: row.email !== null,
    maskedPhone: row.phone_primary ? "***" : null,
    maskedEmail: row.email ? "***" : null,
    verification: row.contact_verification_status,
  };
}

export async function getStatusHistory(req: Request, uuid: string): Promise<unknown[]> {  const existing = await getMemberByUuid(req, uuid, true);
  if (!existing) throw notFound("العضو غير موجود أو خارج نطاقك");
  const rows = await query(
    `SELECT h.id, h.effective_from::text, fs.name_ar AS from_status_ar, ts.name_ar AS to_status_ar,
            h.suspension_until::text AS suspension_until,
            h.decision_ref, h.decided_by, h.reason, h.created_at::text
       FROM member_status_history h
       LEFT JOIN statuses fs ON fs.id = h.from_status_id
       JOIN statuses ts ON ts.id = h.to_status_id
      WHERE h.member_id = $1
      ORDER BY h.effective_from DESC, h.id DESC`,
    [existing.id],
  );
  return rows.rows;
}

/** Active secretariat units visible to the caller (scope-filtered) — feeds the members filter. */
export async function listSecretariats(req: Request): Promise<{ id: number; name_ar: string; governorate_name: string | null; members: number }[]> {
  const scope = governorateScope(req);
  const params: unknown[] = [];
  let scopeSql = "";
  if (scope !== null) {
    if (scope.length === 0) return [];
    params.push(scope);
    scopeSql = `AND u.governorate_id = ANY($1::int[])`;
  }
  const rows = await query<{ id: number; name_ar: string; governorate_name: string | null; members: number }>(
    `SELECT u.id, u.name_ar, g.name_ar AS governorate_name,
            (SELECT COUNT(*)::int FROM org_positions p JOIN members m2 ON m2.id = p.member_id
              WHERE p.org_unit_id = u.id AND p.deleted_at IS NULL AND p.ended_on IS NULL
                AND m2.deleted_at IS NULL) AS members
       FROM org_units u LEFT JOIN governorates g ON g.id = u.governorate_id
      WHERE u.deleted_at IS NULL AND u.unit_kind = 'governorate' ${scopeSql}
      ORDER BY u.name_ar`,
    params,
  );
  return rows.rows;
}


/** Self-service education update (member edits their OWN file only — enforced by the caller route). */
export async function updateEducation(
  req: Request,
  memberId: number,
  input: { university?: string | null; faculty?: string | null; qualification?: string | null; graduationYear?: number | null },
): Promise<void> {
  const sets: string[] = ["updated_at = now()", "updated_by = $1"];
  const params: unknown[] = [req.user!.id];
  const fields: [string, unknown][] = [
    ["university", input.university?.trim() || null],
    ["faculty", input.faculty?.trim() || null],
    ["qualification", input.qualification?.trim() || null],
    ["graduation_year", input.graduationYear ?? null],
  ];
  const changed: Record<string, unknown> = {};
  for (const [col, value] of fields) {
    params.push(value);
    sets.push(`${col} = $${params.length}`);
    changed[col] = value;
  }
  await query(`UPDATE members SET ${sets.join(", ")} WHERE id = $${params.length + 1}`, [...params, memberId]);
  await audit({
    actorUserId: req.user!.id,
    action: "members.self_updated",
    entityType: "member",
    entityId: memberId,
    changedFields: { education: changed },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
}

/** Store the official photo (bytes already validated by the route middleware). */
export async function setPhoto(req: Request, memberId: number, bytes: Buffer, mime: string): Promise<void> {
  await query(
    `UPDATE members SET photo = $2, photo_mime = $3, photo_updated_at = now(), updated_at = now(), updated_by = $1 WHERE id = $4`,
    [req.user!.id, bytes, mime, memberId],
  );
  await audit({
    actorUserId: req.user!.id,
    action: "members.photo_updated",
    entityType: "member",
    entityId: memberId,
    changedFields: { photo: "set", mime },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
}

export async function clearPhoto(req: Request, memberId: number): Promise<void> {
  await query(
    `UPDATE members SET photo = NULL, photo_mime = NULL, photo_updated_at = now(), updated_at = now(), updated_by = $1 WHERE id = $2`,
    [req.user!.id, memberId],
  );
  await audit({
    actorUserId: req.user!.id,
    action: "members.photo_updated",
    entityType: "member",
    entityId: memberId,
    changedFields: { photo: "cleared" },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
}

export async function getPhoto(memberId: number): Promise<{ bytes: Buffer; mime: string } | null> {
  const row = await one<{ photo: Buffer; photo_mime: string }>(
    `SELECT photo, photo_mime FROM members WHERE id = $1 AND photo IS NOT NULL`,
    [memberId],
  );
  if (!row) return null;
  return { bytes: row.photo, mime: row.photo_mime };
}


/** Full in-scope member list for Excel export (same filters/scope as the paged list). */
export async function listMembersForExport(
  req: Request,
  opts: { search?: string; secretariatId?: number },
): Promise<ExportRow[]> {
  const scope = governorateScope(req);
  const conditions = ["m.deleted_at IS NULL"];
  const params: unknown[] = [];
  if (scope !== null) {
    params.push(scope);
    conditions.push(`m.governorate_id = ANY($${params.length}::int[])`);
  }
  if (opts.search && opts.search.trim()) {
    params.push(`%${opts.search.trim()}%`);
    conditions.push(`(m.full_name ILIKE $${params.length} OR m.public_uuid::text ILIKE $${params.length} OR m.membership_no::text ILIKE $${params.length})`);
  }
  if (opts.secretariatId) {
    params.push(opts.secretariatId);
    conditions.push(`sec.unit_id = $${params.length}`);
  }
  const rows = await query<ExportRow>(
    `SELECT m.membership_no::text AS membership_no, m.system_no, m.full_name,
            g.name_ar AS governorate_name, sec.unit_name AS secretariat_name,
            st.name_ar AS formal_status_ar, m.created_at::text AS created_at
       FROM members m
       LEFT JOIN governorates g ON g.id = m.governorate_id
       LEFT JOIN statuses st ON st.id = m.formal_membership_status_id
       LEFT JOIN LATERAL (
         SELECT u.name_ar AS unit_name FROM org_positions p JOIN org_units u ON u.id = p.org_unit_id
          WHERE p.member_id = m.id AND p.deleted_at IS NULL AND p.ended_on IS NULL
            AND u.deleted_at IS NULL AND u.unit_kind = 'governorate' LIMIT 1
       ) sec ON TRUE
      WHERE ${conditions.join(" AND ")}
      ORDER BY m.id DESC`,
    params,
  );
  return rows.rows;
}

export interface ExportRow {
  membership_no: string | null;
  system_no: string | null;
  full_name: string;
  governorate_name: string | null;
  secretariat_name: string | null;
  formal_status_ar: string | null;
  created_at: string;
}