<?php
// ================================
// File: api/get_flagged_34b.php
// Robust Form 34B disparity detection vs aggregated Form 34A
// - Flags and explains *where* the difference comes from:
//   (registered voters mismatch, total cast mismatch, candidate mismatch)
// - Returns reason_code + diffs + candidate_breaches + thresholds
// - Emails all Admin users a summary (1-hour throttle per recipient)
// ================================

// CORS
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type, Authorization");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { http_response_code(200); exit(); }

// POST-only
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
  http_response_code(405);
  echo json_encode(["status"=>"error","message"=>"Only POST requests are allowed for this endpoint."]);
  exit();
}

require_once __DIR__ . '/conn.php';

// Charset / collation
$conn->query("SET NAMES utf8mb4 COLLATE utf8mb4_general_ci");
$conn->query("SET collation_connection = 'utf8mb4_general_ci'");
$conn->query("SET SESSION group_concat_max_len = 819200");

// ---------- Mail + Logging helpers ----------
function sendViaRemoteMailerDetailed(string $to, string $subject, string $html): array {
  $endpoint = 'https://skizagroundsuite.com/API/ABIS/mail.php';
  $ch = curl_init($endpoint);
  if ($ch === false) return ["sent"=>false,"error"=>"curl_init failed"];

  $post = [
    'receiver' => json_encode([$to]), // ABIS expects JSON array
    'subject'  => $subject,
    'body'     => $html,
  ];

  curl_setopt_array($ch, [
    CURLOPT_POST           => true,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_POSTFIELDS     => $post,   // multipart/form-data
    CURLOPT_TIMEOUT        => 30,
    CURLOPT_CONNECTTIMEOUT => 15,
    CURLOPT_HTTPHEADER     => ['Accept: application/json'],
  ]);

  $resp = curl_exec($ch);
  $curl_err = curl_error($ch);
  $http = curl_getinfo($ch, CURLINFO_HTTP_CODE);
  curl_close($ch);

  $json = $resp !== false ? json_decode($resp, true) : null;
  $ok = ($http >= 200 && $http < 300) && is_array($json) && !empty($json['success']);
  if ($ok && isset($json['sent_to']) && is_array($json['sent_to']) && count($json['sent_to']) === 0) {
    $ok = false;
  }

  return [
    "sent"       => $ok,
    "http_code"  => $http,
    "response"   => $json,
    "curl_error" => $curl_err,
    "raw"        => is_string($resp) ? $resp : null,
  ];
}

function logEmailSimple(
  mysqli $conn,
  string $to,
  string $subject,
  string $body,
  string $purpose,
  ?string $membershipNumber,
  string $status,
  ?string $errorMsg = null,
  ?int $httpCode = null,
  ?string $curlErr = null,
  $remoteResp = null
): void {
  $sql = "INSERT INTO email_logs
    (recipient_email, subject, body, purpose, membership_number, status, error_message, http_code, curl_error, remote_response)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  if (!$stmt = $conn->prepare($sql)) { error_log("logEmailSimple prepare error: ".$conn->error); return; }
  $respJson = is_null($remoteResp) ? null : json_encode($remoteResp, JSON_UNESCAPED_SLASHES);
  if ($respJson !== null && strlen($respJson) > 50000) { $respJson = substr($respJson, 0, 50000) . '…[truncated]'; }
  $stmt->bind_param("ssssssssss", $to, $subject, $body, $purpose, $membershipNumber, $status, $errorMsg, $httpCode, $curlErr, $respJson);
  if (!$stmt->execute()) error_log("logEmailSimple execute error: ".$stmt->error);
  $stmt->close();
}

// ---------- Read body (JSON first, fallback to form data) ----------
$raw = file_get_contents('php://input');
$body = json_decode($raw, true);
if (!is_array($body)) $body = $_POST ?? [];

// Helpers
function clampInt($v, int $min, int $max, int $default = 0): int {
  if ($v === null || $v === '' || !is_numeric($v)) return $default;
  $n = (int)$v;
  if ($n < $min) $n = $min;
  if ($n > $max) $n = $max;
  return $n;
}
function clampFloat($v, float $min, float $max, float $default = 0.0): float {
  if ($v === null || $v === '' || !is_numeric($v)) return $default;
  $n = (float)$v;
  if ($n < $min) $n = $min;
  if ($n > $max) $n = $max;
  return $n;
}

// Tolerances (0 = strict)
$absCandidate  = clampInt($body['abs_candidate_diff'] ?? 0, 0, 100000000, 0);
$pctCandidate  = clampFloat($body['pct_candidate_diff'] ?? 0, 0.0, 100.0, 0.0); // allow user to pass 0-100; we normalize below
$absRegistered = clampInt($body['abs_registered_diff'] ?? 0, 0, 100000000, 0);
$absTotalCast  = clampInt($body['abs_totalcast_diff']  ?? 0, 0, 100000000, 0);

// Normalize pctCandidate:
// - if user sends 0-1 treat as fraction
// - if user sends 0-100 treat as percentage
$pctCandidateFraction = ($pctCandidate > 1.0) ? ($pctCandidate / 100.0) : $pctCandidate;

// Filters
$filterCounty    = isset($body['county_code']) ? trim((string)$body['county_code']) : null;
$filterSeverity  = isset($body['severity']) ? trim((string)$body['severity']) : null;   // Low|Medium|High
$filterStatus    = isset($body['status']) ? trim((string)$body['status']) : null;       // Open|In Review|Resolved
$includeResolved = !empty($body['includeResolved']);

// Put tolerances into SQL session vars (for readability)
$conn->query("SET @abs_candidate_diff := " . (int)$absCandidate);
$conn->query("SET @pct_candidate_diff := " . (float)$pctCandidateFraction);
$conn->query("SET @abs_registered_diff := " . (int)$absRegistered);
$conn->query("SET @abs_totalcast_diff  := " . (int)$absTotalCast);

// ----------------------------------------------------------------------------------
// Core idea (more robust):
// - Build B header aggregates from results_form34b + results_form34b_votes (+ polling_station)
// - Build A aggregates from results_form34a + results_form34a_votes (+ polling_station)
// - Build per-candidate diffs by matching candidates in same county+const
// - Return reason_code + numeric diffs so you can see *exactly* what diverged.
// ----------------------------------------------------------------------------------

$sql = "
SELECT
  bh.form34b_id AS id,
  bh.county_code AS county_code,
  bh.const_code  AS const_code,

  -- Numeric diffs used for debugging
  (IFNULL(bh.b_registered_sum,0) - IFNULL(ah.a_registered_sum,0)) AS registered_diff,
  (IFNULL(bh.b_total_cast,0) - IFNULL(ah.a_total_cast,0))         AS total_cast_diff,

  -- Which rule triggered the flag
  CASE
    WHEN cb.breach_count IS NOT NULL AND cb.breach_count > 0
      THEN 'CANDIDATE_MISMATCH'
    WHEN ABS(IFNULL(bh.b_total_cast,0) - IFNULL(ah.a_total_cast,0)) > @abs_totalcast_diff
      THEN 'TOTAL_CAST_MISMATCH'
    WHEN ABS(IFNULL(bh.b_registered_sum,0) - IFNULL(ah.a_registered_sum,0)) > @abs_registered_diff
      THEN 'REGISTERED_MISMATCH'
    ELSE 'OK'
  END AS reason_code,

  CASE
    WHEN cb.breach_count IS NOT NULL AND cb.breach_count > 0
      THEN 'Candidate totals mismatch vs 34A aggregate'
    WHEN ABS(IFNULL(bh.b_total_cast,0) - IFNULL(ah.a_total_cast,0)) > @abs_totalcast_diff
      THEN 'Total cast mismatch: 34B (valid+rejected) vs aggregate 34A'
    WHEN ABS(IFNULL(bh.b_registered_sum,0) - IFNULL(ah.a_registered_sum,0)) > @abs_registered_diff
      THEN 'Registered voters mismatch: 34B vs aggregate 34A'
    ELSE 'OK'
  END AS issue,

  CASE
    WHEN cb.breach_count IS NOT NULL AND cb.breach_count > 0 THEN 'High'
    WHEN ABS(IFNULL(bh.b_total_cast,0) - IFNULL(ah.a_total_cast,0)) > @abs_totalcast_diff THEN 'High'
    WHEN ABS(IFNULL(bh.b_registered_sum,0) - IFNULL(ah.a_registered_sum,0)) > @abs_registered_diff THEN 'Medium'
    ELSE 'Low'
  END AS severity,

  CASE
    WHEN (cb.breach_count IS NOT NULL AND cb.breach_count > 0)
      OR ABS(IFNULL(bh.b_total_cast,0) - IFNULL(ah.a_total_cast,0)) > @abs_totalcast_diff
      OR ABS(IFNULL(bh.b_registered_sum,0) - IFNULL(ah.a_registered_sum,0)) > @abs_registered_diff
    THEN 'Open' ELSE 'Resolved'
  END AS status,

  CONCAT(
    '{',
      '\"reason_code\":\"', 
        CASE
          WHEN cb.breach_count IS NOT NULL AND cb.breach_count > 0 THEN 'CANDIDATE_MISMATCH'
          WHEN ABS(IFNULL(bh.b_total_cast,0) - IFNULL(ah.a_total_cast,0)) > @abs_totalcast_diff THEN 'TOTAL_CAST_MISMATCH'
          WHEN ABS(IFNULL(bh.b_registered_sum,0) - IFNULL(ah.a_registered_sum,0)) > @abs_registered_diff THEN 'REGISTERED_MISMATCH'
          ELSE 'OK'
        END,
      '\",',

      '\"a_registered_sum\":', IFNULL(ah.a_registered_sum,0), ',',
      '\"a_valid_sum\":', IFNULL(ah.a_valid_sum,0), ',',
      '\"a_rejected_sum\":', IFNULL(ah.a_rejected_sum,0), ',',
      '\"a_total_cast\":', IFNULL(ah.a_total_cast,0), ',',

      '\"b_registered_sum\":', IFNULL(bh.b_registered_sum,0), ',',
      '\"b_valid_sum\":', IFNULL(bh.b_valid_sum,0), ',',
      '\"b_rejected_sum\":', IFNULL(bh.b_rejected_sum,0), ',',
      '\"b_total_cast\":', IFNULL(bh.b_total_cast,0), ',',

      '\"diffs\":{',
        '\"registered_diff\":', (IFNULL(bh.b_registered_sum,0) - IFNULL(ah.a_registered_sum,0)), ',',
        '\"total_cast_diff\":', (IFNULL(bh.b_total_cast,0) - IFNULL(ah.a_total_cast,0)),
      '},',

      '\"thresholds\":{',
        '\"abs_candidate_diff\":', @abs_candidate_diff, ',',
        '\"pct_candidate_diff\":', @pct_candidate_diff, ',',
        '\"abs_registered_diff\":', @abs_registered_diff, ',',
        '\"abs_totalcast_diff\":', @abs_totalcast_diff,
      '},',

      '\"candidate_breaches\":', IFNULL(cb.breach_details, '[]'),
    '}'
  ) AS detail_json

FROM
  -- ---------- B: Form34B header + votes aggregate ----------
  (
    SELECT
      h.id AS form34b_id,
      h.county_code,
      h.const_code,

      -- Registered comes from polling_station aggregation (same as your original)
      ps.ps_registered_sum AS b_registered_sum,

      -- Valid is sum of candidate lines in results_form34b_votes
      IFNULL(bv.sum_valid, 0) AS b_valid_sum,

      -- Rejected comes from header column rejected (as in your original)
      COALESCE(h.rejected, 0) AS b_rejected_sum,

      IFNULL(bv.sum_valid, 0) + COALESCE(h.rejected, 0) AS b_total_cast
    FROM results_form34b h
    LEFT JOIN (
      SELECT county_code, const_code, SUM(IFNULL(registered_voters,0)) AS ps_registered_sum
      FROM polling_station
      GROUP BY county_code, const_code
    ) ps
      ON ps.county_code COLLATE utf8mb4_general_ci = h.county_code COLLATE utf8mb4_general_ci
     AND ps.const_code  COLLATE utf8mb4_general_ci = h.const_code  COLLATE utf8mb4_general_ci
    LEFT JOIN (
      SELECT v.form34b_id, SUM(IFNULL(v.votes,0)) AS sum_valid
      FROM results_form34b_votes v
      GROUP BY v.form34b_id
    ) bv
      ON bv.form34b_id = h.id
  ) AS bh

  -- ---------- A: aggregate Form34A to county+const ----------
  INNER JOIN (
    SELECT
      a.county_code,
      a.const_code,
      ps2.ps_registered_sum AS a_registered_sum,

      IFNULL(av.sum_valid, 0) AS a_valid_sum,
      0 AS a_rejected_sum,
      IFNULL(av.sum_valid, 0) + 0 AS a_total_cast
    FROM results_form34a a
    LEFT JOIN (
      SELECT
        a2.county_code,
        a2.const_code,
        SUM(IFNULL(v.votes,0)) AS sum_valid
      FROM results_form34a a2
      JOIN results_form34a_votes v
        ON v.form34a_id = a2.id
      GROUP BY a2.county_code, a2.const_code
    ) av
      ON av.county_code COLLATE utf8mb4_general_ci = a.county_code COLLATE utf8mb4_general_ci
     AND av.const_code  COLLATE utf8mb4_general_ci = a.const_code  COLLATE utf8mb4_general_ci
    LEFT JOIN (
      SELECT county_code, const_code, SUM(IFNULL(registered_voters,0)) AS ps_registered_sum
      FROM polling_station
      GROUP BY county_code, const_code
    ) ps2
      ON ps2.county_code COLLATE utf8mb4_general_ci = a.county_code COLLATE utf8mb4_general_ci
     AND ps2.const_code  COLLATE utf8mb4_general_ci = a.const_code  COLLATE utf8mb4_general_ci
    GROUP BY a.county_code, a.const_code, ps2.ps_registered_sum, av.sum_valid
  ) AS ah
    ON ah.county_code COLLATE utf8mb4_general_ci = bh.county_code COLLATE utf8mb4_general_ci
   AND ah.const_code  COLLATE utf8mb4_general_ci = bh.const_code  COLLATE utf8mb4_general_ci

  -- ---------- Candidate diffs per form34b_id ----------
  LEFT JOIN (
    SELECT
      x.form34b_id,
      COUNT(*) AS breach_count,
      CONCAT(
        '[',
        GROUP_CONCAT(
          CONCAT(
            '{',
              '\"candidate_id\":\"', x.candidate_id, '\",',
              '\"a_votes\":', IFNULL(x.a_votes,0), ',',
              '\"b_votes\":', IFNULL(x.b_votes,0), ',',
              '\"diff\":', (IFNULL(x.b_votes,0) - IFNULL(x.a_votes,0)), ',',
              '\"pct\":',
                CASE
                  WHEN IFNULL(x.a_votes,0) = 0 AND IFNULL(x.b_votes,0) > 0 THEN 1
                  WHEN IFNULL(x.a_votes,0) = 0 THEN 0
                  ELSE ABS(IFNULL(x.b_votes,0) - IFNULL(x.a_votes,0)) / IFNULL(x.a_votes,1)
                END
            ,'}'
          )
          ORDER BY ABS(IFNULL(x.b_votes,0) - IFNULL(x.a_votes,0)) DESC
          SEPARATOR ','
        ),
        ']'
      ) AS breach_details
    FROM (
      -- We build candidate list per form34b_id, then join A and B aggregates safely (no correlated subselects)
      SELECT
        h.id AS form34b_id,
        cand.candidate_id,

        -- A votes per candidate at county+const level
        IFNULL(ac.a_votes, 0) AS a_votes,

        -- B votes per candidate at form34b_id level
        IFNULL(bc.b_votes, 0) AS b_votes
      FROM results_form34b h
      JOIN (
        -- candidate universe for that county+const from BOTH A and B
        SELECT county_code, const_code, candidate_id
        FROM (
          SELECT
            a.county_code COLLATE utf8mb4_general_ci AS county_code,
            a.const_code  COLLATE utf8mb4_general_ci AS const_code,
            CAST(v.candidate_id AS CHAR(64)) COLLATE utf8mb4_general_ci AS candidate_id
          FROM results_form34a a
          JOIN results_form34a_votes v ON v.form34a_id = a.id

          UNION

          SELECT
            b.county_code COLLATE utf8mb4_general_ci AS county_code,
            b.const_code  COLLATE utf8mb4_general_ci AS const_code,
            CAST(v.candidate_id AS CHAR(64)) COLLATE utf8mb4_general_ci AS candidate_id
          FROM results_form34b b
          JOIN results_form34b_votes v ON v.form34b_id = b.id
        ) u
        GROUP BY county_code, const_code, candidate_id
      ) cand
        ON cand.county_code COLLATE utf8mb4_general_ci = h.county_code COLLATE utf8mb4_general_ci
       AND cand.const_code  COLLATE utf8mb4_general_ci = h.const_code  COLLATE utf8mb4_general_ci

      LEFT JOIN (
        SELECT
          a.county_code COLLATE utf8mb4_general_ci AS county_code,
          a.const_code  COLLATE utf8mb4_general_ci AS const_code,
          CAST(v.candidate_id AS CHAR(64)) COLLATE utf8mb4_general_ci AS candidate_id,
          SUM(IFNULL(v.votes,0)) AS a_votes
        FROM results_form34a a
        JOIN results_form34a_votes v ON v.form34a_id = a.id
        GROUP BY a.county_code, a.const_code, candidate_id
      ) ac
        ON ac.county_code COLLATE utf8mb4_general_ci = h.county_code COLLATE utf8mb4_general_ci
       AND ac.const_code  COLLATE utf8mb4_general_ci = h.const_code  COLLATE utf8mb4_general_ci
       AND ac.candidate_id COLLATE utf8mb4_general_ci = cand.candidate_id COLLATE utf8mb4_general_ci

      LEFT JOIN (
        SELECT
          v.form34b_id,
          CAST(v.candidate_id AS CHAR(64)) COLLATE utf8mb4_general_ci AS candidate_id,
          SUM(IFNULL(v.votes,0)) AS b_votes
        FROM results_form34b_votes v
        GROUP BY v.form34b_id, candidate_id
      ) bc
        ON bc.form34b_id = h.id
       AND bc.candidate_id COLLATE utf8mb4_general_ci = cand.candidate_id COLLATE utf8mb4_general_ci
    ) x
    WHERE
      ABS(IFNULL(x.b_votes,0) - IFNULL(x.a_votes,0)) > @abs_candidate_diff
      OR (
        CASE
          WHEN IFNULL(x.a_votes,0) = 0 AND IFNULL(x.b_votes,0) > 0 THEN 1
          WHEN IFNULL(x.a_votes,0) = 0 THEN 0
          ELSE ABS(IFNULL(x.b_votes,0) - IFNULL(x.a_votes,0)) / IFNULL(x.a_votes,1)
        END
      ) > @pct_candidate_diff
    GROUP BY x.form34b_id
  ) cb
    ON cb.form34b_id = bh.form34b_id
";

// Execute
$res = $conn->query($sql);
if (!$res) {
  http_response_code(500);
  echo json_encode(["status"=>"error","message"=>"Query failed","debug"=>$conn->error]);
  exit();
}

// Build response
$rows = [];
while ($r = $res->fetch_assoc()) {
  if ($filterCounty && strcasecmp((string)$r['county_code'], (string)$filterCounty) !== 0) continue;
  if ($filterSeverity && strcasecmp((string)$r['severity'], (string)$filterSeverity) !== 0) continue;
  if ($filterStatus && strcasecmp((string)$r['status'], (string)$filterStatus) !== 0) continue;

  $rows[] = [
    "id"           => (int)$r['id'],
    "county"       => $r['county_code'],
    "constituency" => $r['const_code'],
    "issue"        => $r['issue'],
    "severity"     => $r['severity'],
    "status"       => $r['status'],
    // extra helpful debug fields (top level)
    "reason_code"  => $r['reason_code'],
    "registered_diff" => (int)$r['registered_diff'],
    "total_cast_diff" => (int)$r['total_cast_diff'],
    "detail_json"  => json_decode($r['detail_json'], true),
  ];
}

// Show flagged only by default
if (!$includeResolved) {
  $rows = array_values(array_filter($rows, function ($x) {
    return isset($x['status']) && $x['status'] !== 'Resolved';
  }));
}

// ---------- Email all Admin users with a summary (1-hour throttle) ----------
try {
  $flaggedCount = count($rows);
  if ($flaggedCount > 0) {
    $admins = [];
    $ur = $conn->query("SELECT name, email FROM users WHERE role='Admin' AND email IS NOT NULL AND email <> ''");
    if ($ur) {
      while ($u = $ur->fetch_assoc()) {
        if (filter_var($u['email'], FILTER_VALIDATE_EMAIL)) $admins[] = $u;
      }
      $ur->close();
    }

    if (!empty($admins)) {
      $rowsPreview = array_slice($rows, 0, 50);

      // severity/status counts
      $highCount = 0; $openCount = 0; $inReviewCount = 0; $resolvedCount = 0;
      foreach ($rows as $rr) {
        if (strcasecmp((string)$rr['severity'],'High') === 0) $highCount++;
        if (strcasecmp((string)$rr['status'],'Open') === 0) $openCount++;
        elseif (strcasecmp((string)$rr['status'],'In Review') === 0) $inReviewCount++;
        elseif (strcasecmp((string)$rr['status'],'Resolved') === 0) $resolvedCount++;
      }

      $badge   = ($highCount > 0) ? '🔴' : (($openCount > 0) ? '🟠' : '🟢');
      $urgency = ($highCount > 0) ? 'URGENT' : (($openCount > 0) ? 'ATTENTION' : 'FYI');
      $subject = "{$badge} {$urgency}: 34B ANOMALY ALERT — {$flaggedCount} flagged (High: {$highCount})";

      $investigateUrl = 'https://lamsoft.skizagroundsuite.com/admin/login';

      // table rows
      $tableRows = '';
      foreach ($rowsPreview as $x) {
        $id    = htmlspecialchars((string)$x['id']);
        $county = htmlspecialchars((string)$x['county']);
        $const  = htmlspecialchars((string)$x['constituency']);
        $issue  = htmlspecialchars((string)$x['issue']);
        $reason = htmlspecialchars((string)($x['reason_code'] ?? ''));
        $castDiff = htmlspecialchars((string)($x['total_cast_diff'] ?? 0));
        $regDiff  = htmlspecialchars((string)($x['registered_diff'] ?? 0));

        $sev    = htmlspecialchars((string)$x['severity']);
        $stat   = htmlspecialchars((string)$x['status']);

        $sevColor = '#667085';
        if (strcasecmp($sev,'High') === 0)   $sevColor = '#d92d20';
        if (strcasecmp($sev,'Medium') === 0) $sevColor = '#b54708';

        $statColor = '#667085';
        if (strcasecmp($stat,'Open') === 0)       $statColor = '#d92d20';
        elseif (strcasecmp($stat,'In Review')===0)$statColor = '#b54708';
        elseif (strcasecmp($stat,'Resolved')===0) $statColor = '#057a55';

        $sevHtml  = '<span style="color:'.$sevColor.';font-weight:800">'.$sev.'</span>';
        $statHtml = '<span style="color:'.$statColor.';font-weight:800">'.$stat.'</span>';

        $tableRows .= "
          <tr>
            <td>{$id}</td>
            <td>{$county}</td>
            <td>{$const}</td>
            <td>{$reason}<br><span style=\"color:#64748b\">cast_diff={$castDiff}, reg_diff={$regDiff}</span></td>
            <td>{$issue}</td>
            <td>{$sevHtml}</td>
            <td>{$statHtml}</td>
          </tr>
        ";
      }

      $html =
        '<div style="font-family:system-ui,-apple-system,Segoe UI,Roboto,Arial,sans-serif;font-size:14px;color:#111;line-height:1.55">
          <div style="background:linear-gradient(90deg,#0d6efd,#6610f2);color:#fff;padding:14px 18px;border-radius:10px;margin-bottom:12px;">
            <div style="font-size:16px;font-weight:900;letter-spacing:.3px">34B ANOMALY ALERT</div>
            <div style="opacity:.9">Auto-validation vs aggregated 34A</div>
          </div>

          <div style="display:flex;gap:10px;flex-wrap:wrap;margin-bottom:10px">
            <div style="background:#f8fafc;border:1px solid #e5e7eb;border-radius:8px;padding:10px 12px;min-width:140px">
              <div style="font-size:12px;color:#475569">Total flagged</div>
              <div style="font-size:20px;font-weight:900">'.$flaggedCount.'</div>
            </div>
            <div style="background:#fff1f2;border:1px solid #fecaca;border-radius:8px;padding:10px 12px;min-width:140px">
              <div style="font-size:12px;color:#991b1b">High severity</div>
              <div style="font-size:20px;font-weight:900;color:#b91c1c">'.$highCount.'</div>
            </div>
            <div style="background:#ecfdf5;border:1px solid #bbf7d0;border-radius:8px;padding:10px 12px;min-width:140px">
              <div style="font-size:12px;color:#065f46">Open</div>
              <div style="font-size:20px;font-weight:900;color:#047857">'.$openCount.'</div>
            </div>
          </div>

          <div style="background:#fff8e1;border:1px dashed #f59e0b;border-radius:8px;padding:10px 12px;margin-bottom:10px">
            <strong style="color:#92400e">Action:</strong> Review <strong>High</strong> severity first. Each row now includes <em>reason_code</em> and numeric diffs.
          </div>

          <div style="overflow:auto;margin-top:10px">
            <table cellpadding="6" cellspacing="0" border="0" style="border-collapse:collapse;min-width:860px;width:100%">
              <thead>
                <tr style="background:#f4f6f8;border-bottom:1px solid #e5e7eb">
                  <th align="left" style="padding:8px 10px;font-size:12px;color:#475569;">ID</th>
                  <th align="left" style="padding:8px 10px;font-size:12px;color:#475569;">County</th>
                  <th align="left" style="padding:8px 10px;font-size:12px;color:#475569;">Constituency</th>
                  <th align="left" style="padding:8px 10px;font-size:12px;color:#475569;">Reason</th>
                  <th align="left" style="padding:8px 10px;font-size:12px;color:#475569;">Issue</th>
                  <th align="left" style="padding:8px 10px;font-size:12px;color:#475569;">Severity</th>
                  <th align="left" style="padding:8px 10px;font-size:12px;color:#475569;">Status</th>
                </tr>
              </thead>
              <tbody>'.$tableRows.'</tbody>
            </table>
          </div>

          <p style="margin:14px 0 6px">
            <a href="'.htmlspecialchars($investigateUrl).'" target="_blank" rel="noreferrer"
              style="display:inline-block;padding:11px 16px;background:#0d6efd;color:#fff;text-decoration:none;border-radius:8px;font-weight:800">
              Open Investigation Console
            </a>
          </p>
          <p style="margin:6px 0;color:#666">
            If the button doesn’t open, copy & paste this URL:<br>
            <span style="word-break:break-all;">'.htmlspecialchars($investigateUrl).'</span>
          </p>

          <p style="margin-top:10px;color:#555">Generated at '.date('Y-m-d H:i:s').'.</p>
        </div>';

      // Throttle per recipient: 1 hour
      $throttleStmt = $conn->prepare("
        SELECT 1
        FROM email_logs
        WHERE recipient_email = ?
          AND purpose = '34B_flag_alert'
          AND created_at >= (NOW() - INTERVAL 1 HOUR)
        LIMIT 1
      ");
      if ($throttleStmt) {
        foreach ($admins as $a) {
          $to = $a['email'];

          $throttleStmt->bind_param("s", $to);
          $throttleStmt->execute();
          $throttleStmt->store_result();

          if ($throttleStmt->num_rows > 0) {
            continue;
          }

          $send = sendViaRemoteMailerDetailed($to, $subject, $html);
          $emailSent = !empty($send['sent']);

          if ($emailSent) {
            logEmailSimple($conn, $to, $subject, $html, '34B_flag_alert', null, 'SENT', null, $send['http_code'] ?? null, $send['curl_error'] ?? null, $send['response'] ?? null);
          } else {
            $parts = [];
            if (!empty($send['curl_error'])) $parts[] = 'cURL: '.$send['curl_error'];
            if (!empty($send['http_code']))  $parts[] = 'HTTP: '.$send['http_code'];
            if (!empty($send['response']['error'])) $parts[] = 'Mailer: '.$send['response']['error'];
            $errMsg = $parts ? implode(' | ', $parts) : 'unknown error';
            logEmailSimple($conn, $to, $subject, $html, '34B_flag_alert', null, 'FAILED', $errMsg, $send['http_code'] ?? null, $send['curl_error'] ?? null, $send['response'] ?? null);
          }
        }
        $throttleStmt->close();
      } else {
        error_log("Throttle prepare failed: ".$conn->error);
      }
    }
  }
} catch (Throwable $t) {
  error_log('Admin notify error: '.$t->getMessage());
}

// Final API response
echo json_encode(["status"=>"ok","rows"=>$rows], JSON_UNESCAPED_UNICODE);
