<?php
require_once __DIR__ . '/../../config/cors.php';
require_once __DIR__ . '/../../config/jwt.php';

$user = auth_required();
if ($user['role'] === 'surveyor') {
    http_response_code(403); echo json_encode(['error' => 'Forbidden']); exit;
}

$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 AS primary_hoh, s.contact_no AS primary_contact,
               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();

// Build a map: survey_id → all owners (for multi-owner export)
$surveyIds = array_column($rows, 'id');
$allOwners = [];
if (!empty($surveyIds)) {
    $in  = implode(',', array_fill(0, count($surveyIds), '?'));
    $oStmt = $pdo->prepare("SELECT survey_id, owner_name, citizenship_no, ownership_share_pct, is_primary
                             FROM land_owners WHERE survey_id IN ($in) ORDER BY survey_id, sequence_order");
    $oStmt->execute($surveyIds);
    foreach ($oStmt->fetchAll() as $o) {
        $allOwners[$o['survey_id']][] = $o;
    }
}

// Build map: survey_id → all structures
$allStructures = [];
if (!empty($surveyIds)) {
    $in = implode(',', array_fill(0, count($surveyIds), '?'));
    $sStmt = $pdo->prepare("SELECT survey_id, structure_type, construction_material,
                                    num_floors, floor_length_m, floor_breadth_m, year_built,
                                    structure_condition, owner_type, structure_owner_name
                             FROM survey_structures WHERE survey_id IN ($in) ORDER BY survey_id, sequence_order");
    $sStmt->execute($surveyIds);
    foreach ($sStmt->fetchAll() as $s) {
        $allStructures[$s['survey_id']][] = $s;
    }
}

// Build map: survey_id → all households
$allHouseholds = [];
if (!empty($surveyIds)) {
    $in = implode(',', array_fill(0, count($surveyIds), '?'));
    $hStmt = $pdo->prepare("SELECT survey_id, hoh_name, contact_no, total_members, families_count,
                                    vulnerability_flags, residence_status, structure_seq
                             FROM survey_households WHERE survey_id IN ($in) ORDER BY survey_id, sequence_order");
    $hStmt->execute($surveyIds);
    foreach ($hStmt->fetchAll() as $h) {
        $allHouseholds[$h['survey_id']][] = $h;
    }
}

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="bancharee_surveys_' . date('Ymd') . '.csv"');

$out = fopen('php://output', 'w');

// Headers
fputcsv($out, [
    'Survey ID', 'Survey Date', 'Ward', 'Tole', 'Zone', 'Sheet', 'Parcel No (Kitta)',
    'Land Type', 'Ownership Type',
    // Owner columns (up to 3 owners)
    '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 columns (primary structure)
    'Structure Type', 'Material', 'Floors', 'Length(m)', 'Breadth(m)',
    'Year Built', 'Condition', 'Occupied', 'Rented', 'Monthly Rent(NPR)', 'Rooms',
    'Structure Owner (if different)',
    // All structures count
    'Total Structures',
    // Household columns (up to 2 households)
    '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 no multi-owner data, use legacy fields
    if (empty($owners)) {
        $owners = [['owner_name' => $r['primary_owner_name'],
                    'citizenship_no' => $r['primary_citizenship'],
                    'ownership_share_pct' => 100]];
    }
    // Primary structure fallback
    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'],
                     'owner_type' => 'land_owner',
                     'structure_owner_name' => '']];
    }
    // Primary household fallback
    if (empty($hhs)) {
        $hhs = [['hoh_name' => $r['primary_hoh'],
                 'contact_no' => $r['primary_contact'],
                 'total_members' => $r['total_members'],
                 'vulnerability_flags' => $r['vulnerability_flags'],
                 'residence_status' => $r['residence_status']]];
    }

    $s1 = $structs[0];
    $row = [
        $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
        $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'] ?? '',
        // Primary structure
        $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'] ?? $r['is_occupied']) ? 'Yes' : 'No',
        ($s1['is_rented']   ?? $r['is_rented'])   ? 'Yes' : 'No',
        $s1['monthly_rent'] ?? $r['monthly_rent'],
        $s1['num_rooms']    ?? $r['num_rooms'],
        ($s1['owner_type'] ?? '') === 'different' ? ($s1['structure_owner_name'] ?? '') : 'Same as land owner',
        count($structs),
        // Households
        $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']
    ];
    fputcsv($out, $row);
}
fclose($out);
