<?php
// public/admin/api_house_report.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 {
  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);
  }

  $mode = $_GET['mode'] ?? 'suggest';

  // ========== SUGGEST (typeahead) ==========
  if ($mode === 'suggest') {
    $q    = trim($_GET['q'] ?? '');
    $ward = trim($_GET['ward'] ?? '');

    if ($q === '' && $ward === '') { echo json_encode(['items'=>[]]); exit; }

    $sql = 'SELECT id,
                   house_no,
                   name  AS resident_name,
                   phone AS mobile,
                   ward
            FROM residents
            WHERE 1=1 ' .
            ($q !== '' ? ' AND house_no LIKE :q ' : '') .
            ($ward !== '' && strtolower($ward) !== 'all' ? ' AND LOWER(ward)=LOWER(:w) ' : '') .
           ' ORDER BY house_no
             LIMIT 20';

    $params = [];
    if ($q !== '') $params['q'] = $q.'%';
    if ($ward !== '' && strtolower($ward) !== 'all') $params['w'] = $ward;

    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rows = $st->fetchAll();

    echo json_encode(['items' => array_map(function($r){
      return [
        'id'            => (int)$r['id'],
        'house_no'      => $r['house_no'],
        'resident_name' => $r['resident_name'],
        'mobile'        => $r['mobile'],
        'ward'          => $r['ward']
      ];
    }, $rows)]);
    exit;
  }

  // ========== HOUSES BY WARD (for dropdown) ==========
  if ($mode === 'houses_by_ward') {
    $ward = trim($_GET['ward'] ?? '');
    if ($ward === '') { echo json_encode(['houses'=>[]]); exit; }

    if (strtolower($ward) === 'all') {
      // return nothing here; JS shows "All houses" and allows typing
      echo json_encode(['houses'=>[]]); exit;
    }

    $st = $pdo->prepare('SELECT id, house_no FROM residents WHERE LOWER(ward)=LOWER(:w) ORDER BY house_no');
    $st->execute(['w'=>$ward]);
    $rows = $st->fetchAll();
    echo json_encode(['houses' => array_map(fn($r)=>[
      'id'       => (int)$r['id'],
      'house_no' => $r['house_no']
    ], $rows)]);
    exit;
  }

  // ========== ALL DETAILS (wide table; ward or all) ==========
  if ($mode === 'all_details') {
    $ward = trim($_GET['ward'] ?? 'all');

    $where = '';
    $params = [];
    if ($ward !== '' && strtolower($ward) !== 'all') {
      $where = ' WHERE LOWER(ward)=LOWER(:w) ';
      $params['w'] = $ward;
    }

    // 1) Residents
    $st = $pdo->prepare('SELECT id, house_no, name AS resident_name, phone AS mobile, ward FROM residents'
      . $where . ' ORDER BY ward, house_no');
    $st->execute($params);
    $residents = $st->fetchAll();
    if (!$residents) { echo json_encode(['items'=>[]]); exit; }

    // 2) Trainings for those residents
    $ids = array_column($residents, 'id');
    $trainingsByResident = [];
    foreach (array_chunk($ids, 500) as $chunk) {
      $in = implode(',', array_fill(0, count($chunk), '?'));
      $st2 = $pdo->prepare("SELECT resident_id, training_number, waste_segregation, door_to_door
                            FROM trainings WHERE resident_id IN ($in)");
      $st2->execute($chunk);
      while ($row = $st2->fetch()) {
        $rid = (int)$row['resident_id'];
        $trainingsByResident[$rid][] = $row;
      }
    }

    // 3) Normalize
    $norm = function($v){
      if ($v === null || $v === '') return '-';
      $s = strtolower((string)$v);
      if (in_array($s, ['yes','1','true'])) return 'yes';
      if (in_array($s, ['no','0','false']))  return 'no';
      return '-';
    };

    $items = [];
    foreach ($residents as $r) {
      $rid = (int)$r['id'];
      $rounds = [
        1 => ['has_training'=>false,'ws'=>null,'dc'=>null],
        2 => ['has_training'=>false,'ws'=>null,'dc'=>null],
        3 => ['has_training'=>false,'ws'=>null,'dc'=>null],
      ];
      foreach ($trainingsByResident[$rid] ?? [] as $t) {
        $n = (int)$t['training_number'];
        if ($n>=1 && $n<=3) {
          $rounds[$n]['has_training'] = true;
          $rounds[$n]['ws'] = $t['waste_segregation'];
          $rounds[$n]['dc'] = $t['door_to_door'];
        }
      }
      $items[] = [
        'id'            => $rid,
        'house_no'      => $r['house_no'],
        'resident_name' => $r['resident_name'],
        'mobile'        => $r['mobile'],
        'ward'          => $r['ward'],
        'T1' => ['training' => $rounds[1]['has_training'] ? 'done' : 'not done yet', 'ws' => $norm($rounds[1]['ws']), 'dc' => $norm($rounds[1]['dc'])],
        'T2' => ['training' => $rounds[2]['has_training'] ? 'done' : 'not done yet', 'ws' => $norm($rounds[2]['ws']), 'dc' => $norm($rounds[2]['dc'])],
        'T3' => ['training' => $rounds[3]['has_training'] ? 'done' : 'not done yet', 'ws' => $norm($rounds[3]['ws']), 'dc' => $norm($rounds[3]['dc'])],
      ];
    }

    echo json_encode(['items'=>$items]);
    exit;
  }

  // ========== DETAIL (single house) ==========
  if ($mode === 'detail') {
    $house   = trim($_GET['house'] ?? '');
    $houseId = isset($_GET['house_id']) ? (int)$_GET['house_id'] : null;

    $baseSelect = 'SELECT id, house_no, name AS resident_name, phone AS mobile, ward FROM residents';

    if ($houseId) {
      $st = $pdo->prepare($baseSelect . ' WHERE id = :id');
      $st->execute(['id'=>$houseId]);
    } else {
      if ($house === '') fail('Missing house parameter', 400);
      $st = $pdo->prepare($baseSelect . ' WHERE house_no = :h LIMIT 1');
      $st->execute(['h'=>$house]);
    }
    $res = $st->fetch();
    if (!$res) fail('House not found', 404);

    $st2 = $pdo->prepare('SELECT training_number, waste_segregation, door_to_door
                          FROM trainings
                          WHERE resident_id = :rid');
    $st2->execute(['rid' => $res['id']]);
    $rows = $st2->fetchAll();

    $rounds = [
      1 => ['has_training'=>false,'ws'=>null,'dc'=>null],
      2 => ['has_training'=>false,'ws'=>null,'dc'=>null],
      3 => ['has_training'=>false,'ws'=>null,'dc'=>null],
    ];
    foreach ($rows as $r) {
      $n = (int)$r['training_number'];
      if ($n < 1 || $n > 3) continue;
      $rounds[$n]['has_training'] = true;
      $rounds[$n]['ws'] = $r['waste_segregation'];
      $rounds[$n]['dc'] = $r['door_to_door'];
    }

    $norm = function($v) {
      if ($v === null || $v === '') return '-';
      $s = strtolower((string)$v);
      if (in_array($s, ['yes','1','true'])) return 'yes';
      if (in_array($s, ['no','0','false']))  return 'no';
      return '-';
    };

    $payload = [
      'house_no' => $res['house_no'],
      'resident_name' => $res['resident_name'],
      'mobile' => $res['mobile'],
      'ward' => $res['ward'],
      'training' => [
        'T1' => $rounds[1]['has_training'] ? 'done' : 'not done yet',
        'T2' => $rounds[2]['has_training'] ? 'done' : 'not done yet',
        'T3' => $rounds[3]['has_training'] ? 'done' : 'not done yet',
      ],
      'waste' => [
        'WS1' => $norm($rounds[1]['ws']),
        'WS2' => $norm($rounds[2]['ws']),
        'WS3' => $norm($rounds[3]['ws']),
      ],
      'dtdc' => [
        'DC1' => $norm($rounds[1]['dc']),
        'DC2' => $norm($rounds[2]['dc']),
        'DC3' => $norm($rounds[3]['dc']),
      ],
    ];

    echo json_encode($payload);
    exit;
  }

  fail('Unknown mode', 400);

} catch (Throwable $e) {
  fail($e->getMessage(), 500);
}
