import { Router } from "express";
import { requireAuth } from "../../middleware/auth.js";
import { requirePermission, governorateScope } from "../../middleware/rbac.js";
import { query } from "../../db/client.js";

export const reportsRouter = Router();
// Deny by default: any report read requires reports.view
reportsRouter.use(requireAuth, requirePermission("reports.view"));

/**
 * Secretariats report — the أمانة dimension is the owner-designated basis for
 * reporting and KPIs (2026-10-28). All counts are live, scope-filtered INSIDE SQL.
 * Status distribution uses the G-3 approved values + the safe default.
 */
reportsRouter.get("/secretariats", async (req, res, next) => {
  try {
    const scope = governorateScope(req);
    const params: unknown[] = [];
    let scopeSql = "";
    if (scope !== null) {
      if (scope.length === 0) {
        res.json({ items: [] });
        return;
      }
      params.push(scope);
      scopeSql = `AND u.governorate_id = ANY($1::int[])`;
    }
    const rows = await query(
      `SELECT u.id, u.name_ar, g.name_ar AS governorate_name,
        (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,
        (SELECT COUNT(DISTINCT p.member_id)::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,
        (SELECT COUNT(DISTINCT p.member_id)::int FROM org_positions p
          JOIN members m2 ON m2.id = p.member_id
          JOIN statuses st ON st.id = m2.formal_membership_status_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 AND st.code = 'member') AS status_member,
        (SELECT COUNT(DISTINCT p.member_id)::int FROM org_positions p
          JOIN members m2 ON m2.id = p.member_id
          JOIN statuses st ON st.id = m2.formal_membership_status_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 AND st.code = 'associate_member') AS status_associate,
        (SELECT COUNT(DISTINCT p.member_id)::int FROM org_positions p
          JOIN members m2 ON m2.id = p.member_id
          JOIN statuses st ON st.id = m2.formal_membership_status_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 AND st.code = 'under_verification') AS status_verification,
        (SELECT COUNT(DISTINCT p.member_id)::int FROM org_positions p
          JOIN members m2 ON m2.id = p.member_id
          JOIN statuses st ON st.id = m2.formal_membership_status_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 AND st.code = 'suspended') AS status_suspended,
        (SELECT COUNT(DISTINCT p.member_id)::int FROM org_positions p
          JOIN members m2 ON m2.id = p.member_id
          JOIN statuses st ON st.id = m2.formal_membership_status_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 AND st.code = 'unspecified') AS status_unspecified
       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,
    );
    const items = rows.rows.map((r: Record<string, unknown>) => ({ ...r, readiness_note: (r.members as number) >= 20 ? "reached" : "below" }));
    res.json({ items });
  } catch (error) {
    next(error);
  }
});