<?php
session_start();
if (!isset($_SESSION['admin_id'])) { header('Location: login.php'); exit; }
require_once __DIR__ . '/../config/db.php';

// ── Handle CSV download directly (no JWT needed — session auth) ───────────────
if (isset($_GET['download'])) {
    $where  = ['1=1'];
    $params = [];
    if (!empty($_GET['zone']))      { $where[] = 'p.zone = ?';         $params[] = $_GET['zone']; }
    if (!empty($_GET['date_from'])) { $where[] = 's.survey_date >= ?'; $params[] = $_GET['date_from']; }
    if (!empty($_GET['date_to']))   { $where[] = 's.survey_date <= ?'; $params[] = $_GET['date_to']; }

    $sql = 'SELECT s.id, s.survey_date, s.ward_no, s.tole,
                   p.zone, p.sheet_number, p.parcel_no,
                   s.owner_name AS primary_owner_name,
                   s.citizenship_no AS primary_citizenship,
                   s.land_type, s.ownership_type,
                   s.structure_type, s.construction_material,
                   s.num_floors, s.floor_length_m, s.floor_breadth_m,
                   s.year_built, s.structure_condition,
                   s.is_occupied, s.is_rented, s.monthly_rent, s.num_rooms,
                   s.hoh_name, s.contact_no, s.total_members, s.families_count,
                   s.vulnerability_flags, s.residence_status, s.remarks,
                   sv.name AS surveyor_name, s.created_at
            FROM surveys s
            LEFT JOIN parcels p    ON p.id = s.parcel_id
            LEFT JOIN surveyors sv ON sv.id = s.surveyor_id
            WHERE ' . implode(' AND ', $where) . '
            ORDER BY s.survey_date, p.zone, p.parcel_no';

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

    // Multi-owner map
    $surveyIds = array_column($rows, 'id');
    $allOwners = []; $allStructures = []; $allHouseholds = [];
    if (!empty($surveyIds)) {
        $in = implode(',', array_fill(0, count($surveyIds), '?'));
        $o = $pdo->prepare("SELECT survey_id, owner_name, citizenship_no, ownership_share_pct FROM land_owners WHERE survey_id IN ($in) ORDER BY survey_id, sequence_order");
        $o->execute($surveyIds);
        foreach ($o->fetchAll() as $row) $allOwners[$row['survey_id']][] = $row;

        $s = $pdo->prepare("SELECT survey_id, structure_type, construction_material, num_floors, floor_length_m, floor_breadth_m, year_built, structure_condition, is_occupied, is_rented, monthly_rent, num_rooms, owner_type, structure_owner_name FROM survey_structures WHERE survey_id IN ($in) ORDER BY survey_id, sequence_order");
        $s->execute($surveyIds);
        foreach ($s->fetchAll() as $row) $allStructures[$row['survey_id']][] = $row;

        $h = $pdo->prepare("SELECT survey_id, hoh_name, contact_no, total_members, families_count, vulnerability_flags, residence_status FROM survey_households WHERE survey_id IN ($in) ORDER BY survey_id, sequence_order");
        $h->execute($surveyIds);
        foreach ($h->fetchAll() as $row) $allHouseholds[$row['survey_id']][] = $row;
    }

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="bancharee_surveys_' . date('Ymd_His') . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, [
        'Survey ID','Date','Ward','Tole','Zone','Sheet','Parcel No',
        'Land Type','Ownership Type',
        'Owner 1 Name','Owner 1 Citizenship','Owner 1 Share%',
        'Owner 2 Name','Owner 2 Citizenship','Owner 2 Share%',
        'Owner 3 Name','Owner 3 Citizenship','Owner 3 Share%',
        'Structure Type','Material','Floors','Length(m)','Breadth(m)',
        'Year Built','Condition','Occupied','Rented','Monthly Rent(NPR)','Rooms',
        'Structure Owner (if different)','Total Structures',
        'HoH 1 Name','HoH 1 Contact','HoH 1 Members','HoH 1 Vulnerability','HoH 1 Residence',
        'HoH 2 Name','HoH 2 Contact','HoH 2 Members','HoH 2 Vulnerability','HoH 2 Residence',
        'Total Households','Remarks','Surveyor','Created At'
    ]);
    foreach ($rows as $r) {
        $sid     = $r['id'];
        $owners  = $allOwners[$sid]     ?? [];
        $structs = $allStructures[$sid] ?? [];
        $hhs     = $allHouseholds[$sid] ?? [];
        if (empty($owners))  $owners  = [['owner_name'=>$r['primary_owner_name'],'citizenship_no'=>$r['primary_citizenship'],'ownership_share_pct'=>100]];
        if (empty($structs)) $structs = [['structure_type'=>$r['structure_type'],'construction_material'=>$r['construction_material'],'num_floors'=>$r['num_floors'],'floor_length_m'=>$r['floor_length_m'],'floor_breadth_m'=>$r['floor_breadth_m'],'year_built'=>$r['year_built'],'structure_condition'=>$r['structure_condition'],'is_occupied'=>$r['is_occupied'],'is_rented'=>$r['is_rented'],'monthly_rent'=>$r['monthly_rent'],'num_rooms'=>$r['num_rooms'],'owner_type'=>'land_owner','structure_owner_name'=>'']];
        if (empty($hhs))     $hhs     = [['hoh_name'=>$r['hoh_name'],'contact_no'=>$r['contact_no'],'total_members'=>$r['total_members'],'vulnerability_flags'=>$r['vulnerability_flags'],'residence_status'=>$r['residence_status']]];
        $s1 = $structs[0];
        fputcsv($out, [
            $r['id'], $r['survey_date'], $r['ward_no'], $r['tole'],
            $r['zone'], $r['sheet_number'], $r['parcel_no'],
            $r['land_type'], $r['ownership_type'],
            $owners[0]['owner_name']??'', $owners[0]['citizenship_no']??'', $owners[0]['ownership_share_pct']??100,
            $owners[1]['owner_name']??'', $owners[1]['citizenship_no']??'', $owners[1]['ownership_share_pct']??'',
            $owners[2]['owner_name']??'', $owners[2]['citizenship_no']??'', $owners[2]['ownership_share_pct']??'',
            $s1['structure_type']??'', $s1['construction_material']??'',
            $s1['num_floors']??'', $s1['floor_length_m']??'', $s1['floor_breadth_m']??'',
            $s1['year_built']??'', $s1['structure_condition']??'',
            ($s1['is_occupied']??0) ? 'Yes':'No',
            ($s1['is_rented']??0)   ? 'Yes':'No',
            $s1['monthly_rent']??0, $s1['num_rooms']??0,
            ($s1['owner_type']??'')==='different' ? ($s1['structure_owner_name']??'') : 'Same as land owner',
            count($structs),
            $hhs[0]['hoh_name']??'', $hhs[0]['contact_no']??'', $hhs[0]['total_members']??'', $hhs[0]['vulnerability_flags']??'', $hhs[0]['residence_status']??'',
            $hhs[1]['hoh_name']??'', $hhs[1]['contact_no']??'', $hhs[1]['total_members']??'', $hhs[1]['vulnerability_flags']??'', $hhs[1]['residence_status']??'',
            count($hhs), $r['remarks'], $r['surveyor_name'], $r['created_at']
        ]);
    }
    fclose($out);
    exit;
}

// ── Page display ──────────────────────────────────────────────────────────────
require_once __DIR__ . '/includes/header.php';

$totalSurveys = $pdo->query("SELECT COUNT(*) FROM surveys")->fetchColumn();
$zoneCounts   = $pdo->query("SELECT zone, COUNT(*) as cnt FROM parcels GROUP BY zone")->fetchAll(PDO::FETCH_KEY_PAIR);
?>

<div class="mb-6">
  <h2 class="text-xl font-bold text-slate-800">Export Survey Data</h2>
  <p class="text-sm text-slate-500 mt-0.5">Download survey records as CSV for Excel or further analysis</p>
</div>

<div class="grid grid-cols-1 md:grid-cols-2 gap-6">

  <!-- Export form — submits to THIS page with ?download=1 -->
  <div class="bg-white rounded-2xl shadow-sm border border-gray-100 p-6">
    <h3 class="font-semibold text-slate-700 mb-5">Download CSV</h3>
    <form action="export.php" method="get" class="space-y-5">
      <input type="hidden" name="download" value="1">

      <div>
        <label class="block text-xs font-medium text-slate-600 mb-1.5">Zone Filter</label>
        <select name="zone"
                class="w-full px-3 py-2.5 border border-gray-200 rounded-xl text-sm focus:outline-none focus:ring-2 focus:ring-blue-500 bg-gray-50">
          <option value="">All Zones (<?= number_format($totalSurveys) ?> surveys)</option>
          <option value="0-300m">0–300m (<?= number_format($zoneCounts['0-300m'] ?? 0) ?> parcels)</option>
          <option value="300-400m">300–400m (<?= number_format($zoneCounts['300-400m'] ?? 0) ?> parcels)</option>
          <option value="400-900m">400–900m (<?= number_format($zoneCounts['400-900m'] ?? 0) ?> parcels)</option>
        </select>
      </div>

      <div class="grid grid-cols-2 gap-3">
        <div>
          <label class="block text-xs font-medium text-slate-600 mb-1.5">Date From</label>
          <input type="date" name="date_from"
                 class="w-full px-3 py-2.5 border border-gray-200 rounded-xl text-sm focus:outline-none focus:ring-2 focus:ring-blue-500 bg-gray-50">
        </div>
        <div>
          <label class="block text-xs font-medium text-slate-600 mb-1.5">Date To</label>
          <input type="date" name="date_to"
                 class="w-full px-3 py-2.5 border border-gray-200 rounded-xl text-sm focus:outline-none focus:ring-2 focus:ring-blue-500 bg-gray-50">
        </div>
      </div>

      <button type="submit"
              class="w-full py-3 bg-green-600 hover:bg-green-700 text-white font-semibold text-sm rounded-xl transition-colors shadow-sm flex items-center justify-center gap-2">
        ⬇ Download CSV
      </button>
    </form>
    <div class="mt-4 p-3 bg-gray-50 rounded-xl text-xs text-slate-500">
      CSV opens in Excel. For PDF: File → Print → Save as PDF.
    </div>
  </div>

  <!-- Info panel -->
  <div class="space-y-4">
    <div class="bg-white rounded-2xl shadow-sm border border-gray-100 p-5">
      <h4 class="font-semibold text-slate-700 text-sm mb-4">Current Data</h4>
      <div class="space-y-3">
        <div class="flex justify-between items-center">
          <span class="text-sm text-slate-500">Total Surveys</span>
          <span class="font-bold text-slate-800 text-lg"><?= number_format($totalSurveys) ?></span>
        </div>
        <?php
        $zColors = ['0-300m'=>'text-red-600','300-400m'=>'text-orange-600','400-900m'=>'text-blue-600'];
        foreach ($zoneCounts as $zone => $cnt): ?>
        <div class="flex justify-between items-center text-sm">
          <span class="text-slate-400"><?= htmlspecialchars($zone) ?></span>
          <span class="font-semibold <?= $zColors[$zone] ?? 'text-slate-600' ?>"><?= number_format($cnt) ?> parcels</span>
        </div>
        <?php endforeach ?>
      </div>
    </div>
    <div class="bg-slate-50 border border-slate-100 rounded-2xl p-5">
      <h4 class="font-semibold text-slate-700 text-sm mb-3">CSV Includes</h4>
      <div class="grid grid-cols-2 gap-1 text-xs text-slate-500">
        <span>✓ Survey ID &amp; Date</span><span>✓ Ward &amp; Tole</span>
        <span>✓ Zone &amp; Parcel No</span><span>✓ Up to 3 owners</span>
        <span>✓ Citizenship numbers</span><span>✓ Share %</span>
        <span>✓ Structure details</span><span>✓ Structure owner</span>
        <span>✓ Household HoH</span><span>✓ Members count</span>
        <span>✓ Vulnerability flags</span><span>✓ Residence status</span>
        <span>✓ Remarks</span><span>✓ Surveyor name</span>
      </div>
    </div>
  </div>

</div>

<?php require_once __DIR__ . '/includes/footer.php'; ?>
