<?php
require __DIR__ . '/../../includes/db.php';
header('Content-Type: application/json; charset=utf-8');

function fail($msg, $code = 500) {
  http_response_code($code);
  echo json_encode(['error' => $msg]);
  exit;
}

try {
  // Make sure $pdo exists and throws exceptions
  if (!isset($pdo)) fail('DB handle not set from includes/db.php');
  if (method_exists($pdo, 'setAttribute')) {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  }

  $ward = $_GET['ward'] ?? 'all';
  $wardFilter = '';
  $params = [];
  if ($ward !== 'all') {
    // case-insensitive match protects against W1 vs w1
    $wardFilter = ' WHERE LOWER(ward) = LOWER(:w) ';
    $params['w'] = $ward;
  }

  // Totals from residents
  $sqlTotal = 'SELECT COUNT(*) AS total,
    SUM(CASE WHEN waste_segregation IN ("yes","Yes","YES",1,"1") THEN 1 ELSE 0 END) AS yes_cnt,
    SUM(CASE WHEN waste_segregation IN ("no","No","NO",0,"0") THEN 1 ELSE 0 END)  AS no_cnt,
    SUM(CASE WHEN door_to_door IN ("yes","Yes","YES",1,"1") THEN 1 ELSE 0 END) AS dtdc_cnt
    FROM residents' . $wardFilter;

  $st = $pdo->prepare($sqlTotal);
  $st->execute($params);
 $tot = $st->fetch() ?: ['total'=>0,'yes_cnt'=>0,'no_cnt'=>0,'dtdc_cnt'=>0];


  // Growth by survey round from trainings table
  $sqlRounds = 'SELECT t.training_number,
      SUM(CASE WHEN t.waste_segregation IN ("yes","Yes","YES",1,"1") THEN 1 ELSE 0 END) AS yes_cnt
    FROM trainings t
    JOIN residents r ON r.id = t.resident_id
    ' . ($ward !== 'all' ? ' WHERE LOWER(r.ward) = LOWER(:w) ' : '') . '
    GROUP BY t.training_number
    ORDER BY t.training_number';

  $st2 = $pdo->prepare($sqlRounds);
  $st2->execute($params);
  $rounds = $st2->fetchAll();

  // Normalize 1..3
  $roundYes = [1=>0,2=>0,3=>0];
  foreach ($rounds as $row) {
    $n = (int)$row['training_number'];
    if ($n>=1 && $n<=3) $roundYes[$n] = (int)$row['yes_cnt'];
  }

  $response = [
    'ward' => $ward,
    'totals' => [
      'total' => (int)$tot['total'],
      'yes'   => (int)$tot['yes_cnt'],
      'no'    => (int)$tot['no_cnt'],
      'dtdc'  => (int)$tot['dtdc_cnt']   // <-- NEW
    ],
    'growth' => [
      'labels' => ['Survey 1','Survey 2','Survey 3'],
      'yes'    => [$roundYes[1], $roundYes[2], $roundYes[3]]
    ],
    'breakdown' => [] // default empty
  ];

  // If ward=all, optionally include per-ward breakdown so UI can show Excel-like rows.
  // We'll always include it for 'all' to make the front-end simpler.
  if ($ward === 'all') {
    $sqlByWard = 'SELECT ward,
        COUNT(*) AS total,
        SUM(CASE WHEN waste_segregation IN ("yes","Yes","YES",1,"1") THEN 1 ELSE 0 END) AS yes_cnt,
        SUM(CASE WHEN waste_segregation IN ("no","No","NO",0,"0") THEN 1 ELSE 0 END) AS no_cnt,
        SUM(CASE WHEN door_to_door IN ("yes","Yes","YES",1,"1") THEN 1 ELSE 0 END) AS dtdc_cnt
      FROM residents
      GROUP BY ward
      ORDER BY ward';
    $st3 = $pdo->query($sqlByWard);
    $rows = $st3->fetchAll();
    $breakdown = [];
    foreach ($rows as $r) {
      $breakdown[] = [
        'ward' => $r['ward'],
        'total' => (int)$r['total'],
        'yes' => (int)$r['yes_cnt'],
        'no' => (int)$r['no_cnt'],
         'dtdc'  => (int)$r['dtdc_cnt']   // <-- NEW
      ];
    }
    $response['breakdown'] = $breakdown;
  } else {
    // For non-all wards, include a single-row breakdown for the selected ward for consistency
    $response['breakdown'] = [
      [
        'ward' => $ward,
        'total' => (int)$tot['total'],
        'yes' => (int)$tot['yes_cnt'],
        'no' => (int)$tot['no_cnt'],
        'dtdc'  => (int)$tot['dtdc_cnt']   // <-- NEW
      ]
    ];
  }

  echo json_encode($response);
} catch (Throwable $e) {
  // Return the error in JSON so you can see it in Network → Response
  fail($e->getMessage(), 500);
}
