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

/**
 * Organizational structure (conservative core).
 * Everything here is a documented human decision — no invented taxonomies, no automation:
 *  - G-1: a position exists only with an appointment decision reference; holders never get accounts.
 *  - G-4: the 20-member threshold is returned as a DISPLAY-ONLY reference flag.
 */

export interface UnitRow {
  id: number;
  unit_kind: "national" | "governorate";
  governorate_id: number | null;
  governorate_name: string | null;
  name_ar: string;
  formation_decision_ref: string;
  notes: string | null;
  open_positions: number;
  member_count: number | null; // governorate units only (live members in that governorate)
  readiness_note: string | null; // G-4 reference display only
  created_at: string;
}

export async function listUnits(req: Request): Promise<UnitRow[]> {
  const scope = governorateScope(req);
  const params: unknown[] = [];
  let scopeSql = "";
  if (scope !== null) {
    if (scope.length === 0) return [];
    params.push(scope);
    scopeSql = `AND (u.unit_kind = 'national' OR u.governorate_id = ANY($1::int[]))`;
  }
  const rows = await query<UnitRow>(
    `SELECT u.id, u.unit_kind, u.governorate_id, g.name_ar AS governorate_name,
            u.name_ar, u.formation_decision_ref, u.notes, u.created_at::text,
            (SELECT COUNT(*)::int FROM org_positions p
              WHERE p.org_unit_id = u.id AND p.deleted_at IS NULL AND p.ended_on IS NULL) AS open_positions,
            mc.n AS member_count
       FROM org_units u
       LEFT JOIN governorates g ON g.id = u.governorate_id
       LEFT JOIN LATERAL (
         SELECT COUNT(*)::int AS n FROM members m
          WHERE m.governorate_id = u.governorate_id AND m.deleted_at IS NULL
       ) mc ON u.unit_kind = 'governorate'
      WHERE u.deleted_at IS NULL ${scopeSql}
      ORDER BY u.unit_kind DESC, g.name_ar NULLS FIRST, u.id`,
    params,
  );
  return rows.rows.map((r) => ({
    ...r,
    readiness_note:
      r.unit_kind === "governorate"
        ? r.member_count !== null && r.member_count < 20
          ? `دون العتبة المرجعية (20 عضوًا) — عرض مرجعي فقط، لا أثر تشغيليًا (G-4)`
          : `بلغ العتبة المرجعية (20 عضوًا) — عرض مرجعي فقط (G-4)`
        : null,
  }));
}

export async function getUnit(req: Request, id: number): Promise<UnitRow | null> {
  const units = await listUnits(req);
  return units.find((u) => u.id === id) ?? null;
}

export interface CreateUnitInput {
  unitKind: "national" | "governorate";
  governorateId?: number | null;
  nameAr: string;
  formationDecisionRef: string;
  notes?: string | null;
}

export async function createUnit(req: Request, input: CreateUnitInput): Promise<UnitRow> {
  if (input.unitKind === "governorate") {
    const gov = await one(`SELECT id FROM governorates WHERE id = $1`, [input.governorateId ?? 0]);
    if (!gov) throw badRequest("المحافظة المحددة غير موجودة", "invalid_governorate");
    const scope = governorateScope(req);
    if (scope !== null && !scope.includes(input.governorateId!)) {
      throw badRequest("المحافظة خارج نطاقك", "out_of_scope");
    }
  }
  const row = await one<{ id: number }>(
    `INSERT INTO org_units (unit_kind, governorate_id, name_ar, formation_decision_ref, notes, is_demo, created_by)
     VALUES ($1, $2, $3, $4, $5, TRUE, $6) RETURNING id`,
    [
      input.unitKind,
      input.unitKind === "governorate" ? input.governorateId! : null,
      input.nameAr.trim(),
      input.formationDecisionRef.trim(),
      input.notes?.trim() || null,
      req.user!.id,
    ],
  );
  await audit({
    actorUserId: req.user!.id,
    action: "structure.unit_created",
    entityType: "org_unit",
    entityId: row!.id,
    changedFields: { unitKind: input.unitKind, governorateId: input.governorateId ?? null, nameAr: input.nameAr.trim(), formationDecisionRef: input.formationDecisionRef.trim() },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
  const unit = await getUnit(req, row!.id);
  if (!unit) throw new Error("unit vanished after insert");
  return unit;
}

export interface PositionRow {
  id: number;
  org_unit_id: number;
  member_id: number | null;
  member_name: string | null;
  member_uuid: string | null;
  title_ar: string;
  appointment_decision_ref: string;
  decided_by: string | null;
  started_on: string | null;
  ended_on: string | null;
  end_reason: string | null;
  created_at: string;
}

export async function listPositions(req: Request, unitId: number): Promise<PositionRow[]> {
  const unit = await getUnit(req, unitId);
  if (!unit) throw notFound("الوحدة غير موجودة أو خارج نطاقك");
  const rows = await query<PositionRow>(
    `SELECT p.id, p.org_unit_id, p.member_id, m.full_name AS member_name, m.public_uuid::text AS member_uuid,
            p.title_ar, p.appointment_decision_ref, p.decided_by,
            p.started_on::text, p.ended_on::text, p.end_reason, p.created_at::text
       FROM org_positions p
       LEFT JOIN members m ON m.id = p.member_id
      WHERE p.org_unit_id = $1 AND p.deleted_at IS NULL
      ORDER BY p.ended_on NULLS FIRST, p.id DESC`,
    [unitId],
  );
  return rows.rows;
}

export interface CreatePositionInput {
  unitId: number;
  memberUuid?: string | null;
  titleAr: string;
  appointmentDecisionRef: string;
  decidedBy?: string | null;
  startedOn?: string | null;
}

export async function createPosition(req: Request, input: CreatePositionInput): Promise<PositionRow> {
  const unit = await getUnit(req, input.unitId);
  if (!unit) throw notFound("الوحدة غير موجودة أو خارج نطاقك");

  // The optional holder link: the member must exist AND be inside the caller's scope
  let memberId: number | null = null;
  if (input.memberUuid) {
    const scope = governorateScope(req);
    const params: unknown[] = [input.memberUuid];
    let scopeSql = "";
    if (scope !== null) {
      params.push(scope);
      scopeSql = `AND m.governorate_id = ANY($2::int[])`;
    }
    const member = await one<{ id: number }>(
      `SELECT m.id FROM members m WHERE m.public_uuid = $1::uuid AND m.deleted_at IS NULL ${scopeSql}`,
      params,
    );
    if (!member) throw notFound("العضو غير موجود أو خارج نطاقك");
    memberId = member.id;
  }

  const inserted = await one<{ id: number }>(
    `INSERT INTO org_positions (org_unit_id, member_id, title_ar, appointment_decision_ref, decided_by, started_on, is_demo, created_by)
     VALUES ($1, $2, $3, $4, $5, $6, TRUE, $7) RETURNING id`,
    [input.unitId, memberId, input.titleAr.trim(), input.appointmentDecisionRef.trim(), input.decidedBy?.trim() || null, input.startedOn || null, req.user!.id],
  );
  await audit({
    actorUserId: req.user!.id,
    action: "structure.position_created",
    entityType: "org_position",
    entityId: inserted!.id,
    changedFields: { unitId: input.unitId, titleAr: input.titleAr.trim(), appointmentDecisionRef: input.appointmentDecisionRef.trim(), linkedMemberId: memberId },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
  const rows = await query<PositionRow>(
    `SELECT p.id, p.org_unit_id, p.member_id, m.full_name AS member_name, m.public_uuid::text AS member_uuid,
            p.title_ar, p.appointment_decision_ref, p.decided_by,
            p.started_on::text, p.ended_on::text, p.end_reason, p.created_at::text
       FROM org_positions p LEFT JOIN members m ON m.id = p.member_id
      WHERE p.id = $1`,
    [inserted!.id],
  );
  return rows.rows[0]!;
}

export async function closePosition(
  req: Request,
  positionId: number,
  input: { endedOn: string; endReason: string },
): Promise<void> {
  const rows = await query<{ id: number; org_unit_id: number; member_id: number | null; ended_on: string | null; title_ar: string }>(
    `SELECT p.id, p.org_unit_id, p.member_id, p.ended_on::text, p.title_ar FROM org_positions p WHERE p.id = $1 AND p.deleted_at IS NULL`,
    [positionId],
  );
  const pos = rows.rows[0];
  if (!pos) throw notFound("المنصب غير موجود");
  // scope: the caller must see the owning unit
  const unit = await getUnit(req, pos.org_unit_id);
  if (!unit) throw notFound("المنصب غير موجود أو خارج نطاقك");
  if (pos.ended_on) throw badRequest("المنصب منتهٍ بالفعل", "already_closed");
  await query(`UPDATE org_positions SET ended_on = $2, end_reason = $3, updated_at = now(), updated_by = $4 WHERE id = $1`, [
    positionId,
    input.endedOn,
    input.endReason.trim(),
    req.user!.id,
  ]);
  await audit({
    actorUserId: req.user!.id,
    action: "structure.position_closed",
    entityType: "org_position",
    entityId: positionId,
    changedFields: { endedOn: input.endedOn, linkedMemberId: pos.member_id },
    context: { reason: input.endReason.trim() },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
}

export async function archiveUnit(req: Request, unitId: number, reason: string): Promise<void> {
  const unit = await getUnit(req, unitId);
  if (!unit) throw notFound("الوحدة غير موجودة أو خارج نطاقك");
  const open = await one<{ n: number }>(
    `SELECT COUNT(*)::int AS n FROM org_positions WHERE org_unit_id = $1 AND deleted_at IS NULL AND ended_on IS NULL`,
    [unitId],
  );
  if (open && open.n > 0) {
    throw badRequest(`لا يمكن أرشفة وحدة بها ${open.n} منصبًا مفتوحًا — أنهِ المناصب أولًا بقرارات موثقة`, "open_positions_remain");
  }
  await query(`UPDATE org_units SET deleted_at = now(), archive_reason = $2, updated_at = now(), updated_by = $3 WHERE id = $1`, [
    unitId,
    reason.trim(),
    req.user!.id,
  ]);
  await audit({
    actorUserId: req.user!.id,
    action: "structure.unit_archived",
    entityType: "org_unit",
    entityId: unitId,
    context: { reason: reason.trim() },
    ip: req.ip ?? null,
    userAgent: req.headers["user-agent"] ?? null,
  });
}