<link rel="icon" href="logo.jpg" type="image/jpg">
<?php
ob_start(); // Prevent "headers already sent" errors globally
// File: reports.php
// Handles generation of a comprehensive resident report with 
// dynamic column visibility for web preview, PDF, and Excel.

require_once "db.php";
check_login();

// --- 1. COLUMN & FIELD DEFINITIONS ---
$all_columns = [
    'name'          => 'Full Name',
    'age'           => 'Age',
    'gender'        => 'Gender',
    'pwd'           => 'PWD',
    'civil_status'  => 'Civil Status',
    'hh_no'         => 'Household No.',
    'purok'         => 'Purok',
    'relation'      => 'Relation to Head',
    'occupation'    => 'Occupation',
    'education'     => 'Educational Attainment',
    'income'        => 'Monthly Income',
    'pregnant'      => 'Pregnant',
    'breastfeeding' => 'Breastfeeding',
    'fp_method'     => 'FP Method',
    'youth'         => 'Youth Status',
    'health'        => 'Health Conditions',
    'food_prod'     => 'Food Production'
];

// Determine selected columns (default to all if none specified)
$selected_cols = isset($_GET['cols']) ? $_GET['cols'] : array_keys($all_columns);

// Helper function to check if a column is selected
function is_col_visible($col_key, $selected_cols) {
    return in_array($col_key, $selected_cols);
}

// Helper function to compile boolean fields into a readable string
function compile_details($row, $fields) {
    $details = [];
    foreach ($fields as $db_col => $display_name) {
        if (!empty($row[$db_col])) {
            $details[] = $display_name;
        }
    }
    return implode(', ', $details);
}

// Fields for health and food production
$health_fields = [
    'has_cvd' => 'CVD', 'has_hpn' => 'HPN', 'has_dm' => 'DM', 'has_ca' => 'CA',
    'has_eye_prob' => 'Eye Problem', 'has_leprosy' => 'Leprosy', 'has_tb' => 'TB',
    'has_goiter' => 'Goiter', 'has_mental' => 'Mental Problem', 'is_smoker' => 'Smoker', 'has_ckd' => 'CKD'
];

$food_prod_fields = [
    'has_veg_garden' => 'Veg Garden', 'has_livestock' => 'Livestock',
    'has_poultry' => 'Poultry', 'has_fishpond' => 'Fishpond'
];

// --- 2. FILTERS & SORTING ---
$sort       = $_GET['sort']       ?? 'default';
$age_from   = $_GET['age_from']   ?? '';
$age_to     = $_GET['age_to']     ?? '';
$pwd_filter = $_GET['pwd_filter'] ?? '';

$report_header   = $_GET['report_header']   ?? 'Barangay San Fabian - Comprehensive Resident Report';
$report_subtitle = $_GET['report_subtitle'] ?? 'Resident Masterlist';
$report_footer   = $_GET['report_footer']   ?? 'Official Barangay Record';

// Logic for logo visibility
$include_logo = (isset($_GET['include_logo']) || !isset($_GET['sort'])) ? '1' : '0';

// Normalize filters
$age_from = ($age_from !== '' && is_numeric($age_from)) ? (int)$age_from : '';
$age_to   = ($age_to   !== '' && is_numeric($age_to))   ? (int)$age_to   : '';

// Build ORDER BY
switch ($sort) {
    case 'name_asc':  $order_by = "r.last_name ASC, r.first_name ASC"; break;
    case 'name_desc': $order_by = "r.last_name DESC, r.first_name DESC"; break;
    case 'age_asc':   $order_by = "r.birthday DESC"; break;
    case 'age_desc':  $order_by = "r.birthday ASC"; break;
    case 'income_asc':$order_by = "r.monthly_income ASC"; break;
    case 'income_desc':$order_by = "r.monthly_income DESC"; break;
    default:          $order_by = "h.purok, h.household_no, r.relation_to_head, r.birthday"; break;
}

$base_sql = "SELECT r.*, h.household_no, h.purok 
             FROM residents r 
             JOIN households h ON r.household_id = h.id 
             WHERE r.is_archived = 0 
             ORDER BY $order_by";

function row_passes_filters($row, $age_from, $age_to, $pwd_filter) {
    $age = date_diff(date_create($row['birthday']), date_create('now'))->y;
    if ($age_from !== '' && $age < $age_from) return false;
    if ($age_to !== '' && $age > $age_to) return false;
    if ($pwd_filter === 'pwd' && empty($row['is_pwd'])) return false;
    if ($pwd_filter === 'non_pwd' && !empty($row['is_pwd'])) return false;
    return true;
}

// ======================
//  EXCEL (XLS) EXPORT
// ======================
if (isset($_GET['generate_excel'])) {
    while (ob_get_level() > 0) ob_end_clean();
    $filename = "Resident_Report_" . date('Y-m-d') . ".xls";
    header("Content-Type: application/vnd.ms-excel; charset=utf-8");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">';
    echo '<head><meta charset="utf-8"></head><body><table border="1">';
    
    $visible_count = count($selected_cols);
    echo '<tr><th colspan="'.$visible_count.'" style="text-align:center; font-size:18px; height: 60px;">';
    if ($include_logo == '1' && file_exists('logo.jpg')) {
        $protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? "https://" : "http://";
        $logo_url = $protocol . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/\\') . '/logo.jpg';
        echo '<img src="'.$logo_url.'" height="50" style="vertical-align: middle; margin-right: 15px;"> ';
    }
    echo htmlspecialchars($report_header) . '</th></tr>';
    echo '<tr><th colspan="'.$visible_count.'" style="text-align:center; font-style: italic;">'.htmlspecialchars($report_subtitle).'</th></tr>';
    echo '<tr><th colspan="'.$visible_count.'"></th></tr>';

    echo '<tr style="background-color:#d9edf7;">';
    foreach($selected_cols as $ckey) echo '<th>'.htmlspecialchars($all_columns[$ckey]).'</th>';
    echo '</tr>';

    $result = $conn->query($base_sql);
    if ($result && $result->num_rows > 0) {
        $current_household_id = null;
        while ($row = $result->fetch_assoc()) {
            if (!row_passes_filters($row, $age_from, $age_to, $pwd_filter)) continue;
            
            if ($current_household_id !== null && $row['household_id'] !== $current_household_id) {
                echo '<tr><td colspan="'.$visible_count.'" style="background-color: #f8f9fa;"></td></tr>'; 
            }
            $current_household_id = $row['household_id'];

            echo '<tr>';
            foreach($selected_cols as $ckey) {
                $val = '';
                switch($ckey) {
                    case 'name': $val = strtoupper($row['last_name'].", ".$row['first_name']." ".$row['suffix']); break;
                    case 'age': $val = date_diff(date_create($row['birthday']), date_create('now'))->y; break;
                    case 'pwd': $val = $row['is_pwd']?'Yes':'No'; break;
                    case 'hh_no': $val = $row['household_no']; break;
                    case 'relation': $val = $row['relation_to_head']; break;
                    case 'education': $val = $row['educational_attainment']; break;
                    case 'income': $val = number_format($row['monthly_income'], 2); break;
                    case 'pregnant': $val = $row['is_pregnant']?'Yes':'No'; break;
                    case 'breastfeeding': $val = $row['is_breastfeeding']?'Yes':'No'; break;
                    case 'youth': $val = $row['youth_status']; break;
                    case 'health': $val = compile_details($row, $health_fields); break;
                    case 'food_prod': $val = compile_details($row, $food_prod_fields); break;
                    default: $val = $row[$ckey] ?? ''; break;
                }
                echo '<td>'.htmlspecialchars($val).'</td>';
            }
            echo '</tr>';
        }
    }
    echo '</table></body></html>'; exit;
}

// ======================
//  PDF EXPORT & PREVIEW
// ======================
if (isset($_GET['generate_pdf']) || isset($_GET['preview_pdf'])) {
    require('fpdf/fpdf.php');
    class PDF extends FPDF {
        public $customHeader, $customSubtitle, $customFooter, $includeLogo;
        function Header() {
            $hasLogo = ($this->includeLogo && file_exists('logo.jpg'));
            if ($hasLogo) { $this->Image('logo.jpg', 12, 10, 22); $this->SetX(38); }
            $this->SetFont('Arial', 'B', 14);
            $this->Cell(0, 8, utf8_decode($this->customHeader), 0, 1, $hasLogo ? 'L' : 'C');
            if (!empty($this->customSubtitle)) {
                if ($hasLogo) $this->SetX(38);
                $this->SetFont('Arial', 'I', 11);
                $this->Cell(0, 6, utf8_decode($this->customSubtitle), 0, 1, $hasLogo ? 'L' : 'C');
            }
            $this->Ln(8);
        }
        function Footer() {
            $this->SetY(-15); $this->SetFont('Arial', 'I', 8);
            $this->Cell(0, 10, utf8_decode($this->customFooter).' | Page '.$this->PageNo().'/{nb}', 0, 0, 'C');
        }
    }

    $pdf = new PDF();
    $pdf->customHeader = $report_header; $pdf->customSubtitle = $report_subtitle;
    $pdf->customFooter = $report_footer; $pdf->includeLogo = ($include_logo == '1');
    $pdf->AliasNbPages(); $pdf->AddPage('L', 'Legal');

    // Available Widths Mapping
    $pdf_widths = [
        'name' => 45, 'age' => 10, 'gender' => 15, 'pwd' => 10, 'civil_status' => 25,
        'hh_no' => 20, 'purok' => 20, 'relation' => 25, 'occupation' => 30, 'education' => 35,
        'income' => 20, 'pregnant' => 12, 'breastfeeding' => 12, 'fp_method' => 20, 'youth' => 20,
        'health' => 60, 'food_prod' => 40
    ];

    // Determine Total Width to center/distribute if needed (optional adjustment)
    $total_width = 0;
    foreach($selected_cols as $ckey) $total_width += $pdf_widths[$ckey];

    $pdf->SetFont('Arial', 'B', 7);
    foreach($selected_cols as $ckey) $pdf->Cell($pdf_widths[$ckey], 7, $all_columns[$ckey], 1, 0, 'C');
    $pdf->Ln();

    $pdf->SetFont('Arial', '', 6);
    $result = $conn->query($base_sql);
    if ($result && $result->num_rows > 0) {
        $current_household_id = null;
        while ($row = $result->fetch_assoc()) {
            if (!row_passes_filters($row, $age_from, $age_to, $pwd_filter)) continue;

            if ($current_household_id !== null && $row['household_id'] !== $current_household_id) {
                // Visual separator for PDF
                $pdf->SetFillColor(230, 230, 230);
                $pdf->Cell($total_width, 1, '', 'LRB', 1, 'C', true);
                $pdf->SetFillColor(255, 255, 255);
            }
            $current_household_id = $row['household_id'];

            foreach($selected_cols as $ckey) {
                $val = '';
                switch($ckey) {
                    case 'name': $val = strtoupper($row['last_name'].", ".$row['first_name']); break;
                    case 'age': $val = date_diff(date_create($row['birthday']), date_create('now'))->y; break;
                    case 'pwd': $val = $row['is_pwd']?'Y':'N'; break;
                    case 'hh_no': $val = $row['household_no']; break;
                    case 'relation': $val = $row['relation_to_head']; break;
                    case 'education': $val = $row['educational_attainment']; break;
                    case 'income': $val = number_format($row['monthly_income'],0); break;
                    case 'pregnant': $val = $row['is_pregnant']?'Y':'N'; break;
                    case 'breastfeeding': $val = $row['is_breastfeeding']?'Y':'N'; break;
                    case 'youth': $val = $row['youth_status']; break;
                    case 'health': $val = compile_details($row, $health_fields); break;
                    case 'food_prod': $val = compile_details($row, $food_prod_fields); break;
                    default: $val = $row[$ckey] ?? ''; break;
                }
                $pdf->Cell($pdf_widths[$ckey], 6, utf8_decode($val), 1);
            }
            $pdf->Ln();
        }
    } else {
        $pdf->Cell($total_width, 10, 'No active residents found matching filters.', 1, 1, 'C');
    }

    while (ob_get_level() > 0) ob_end_clean();
    $pdf->Output(isset($_GET['preview_pdf']) ? 'I' : 'D', 'Barangay_Report.pdf'); exit;
}

$page_title = "System Reports";
include 'header.php';
?>
<link rel="icon" href="logo.jpg" type="image/jpg">
<style>
    tr.household-separator td { border-top: 2px solid #ccc !important; border-bottom: 2px solid #ccc !important; background-color: #f8f9fa; height: 10px; padding: 0 !important; }
    .col-checklist { display: grid; grid-template-columns: repeat(auto-fill, minmax(180px, 1fr)); gap: 10px; margin-top: 15px; }
    .col-item { display: flex; align-items: center; gap: 8px; font-size: 0.9em; background: #fdfdfd; padding: 5px; border-radius: 4px; border: 1px solid #f0f0f0; }
    .customization-section { border: 1px solid var(--card-border); padding: 20px; margin-bottom: 25px; border-radius: 8px; background: var(--table-header-bg); }
    .customization-section legend { font-weight: 600; padding: 0 10px; color: var(--primary-color); background-color: var(--card-bg); border: 1px solid var(--card-border); border-radius: 4px; }
    .report-filters { display: flex; flex-wrap: wrap; gap: 15px; margin-bottom: 15px; align-items: flex-end; }
    .report-filters .form-group { flex: 1 1 200px; margin-bottom: 0; }
    .badge-pwd { background: #17a2b8; color: #fff; font-size: 0.7rem; padding: 2px 6px; border-radius: 4px; font-weight: bold; }
</style>

<div class="main-content">
    <div class="page-header">
        <h1>Comprehensive Resident Report</h1>
        <p>Customizable reports tool with column selection, dynamic PDF generation, and Excel exports.</p>
    </div>

    <div class="card">
        <form method="get" id="reportForm">
            <!-- 1. COLUMN SELECTION -->
            <fieldset class="customization-section">
                <legend><i class="fas fa-columns"></i> 1. Select Columns to Display</legend>
                <div class="col-checklist">
                    <?php foreach($all_columns as $key => $label): ?>
                        <label class="col-item">
                            <input type="checkbox" name="cols[]" value="<?php echo $key; ?>" 
                                   <?php echo is_col_visible($key, $selected_cols) ? 'checked' : ''; ?>
                                   onchange="this.form.submit()">
                            <?php echo $label; ?>
                        </label>
                    <?php endforeach; ?>
                </div>
            </fieldset>

            <!-- 2. DOCUMENT INFO & FILTERS -->
            <fieldset class="customization-section">
                <legend><i class="fas fa-filter"></i> 2. Document Info & Filters</legend>
                
                <div class="report-filters">
                    <div class="form-group" style="flex: 2 1 300px;">
                        <label>Header Title</label>
                        <input type="text" name="report_header" class="form-control" value="<?php echo htmlspecialchars($report_header); ?>">
                    </div>
                    <div class="form-group" style="flex: 1 1 200px;">
                        <label>Subtitle</label>
                        <input type="text" name="report_subtitle" class="form-control" value="<?php echo htmlspecialchars($report_subtitle); ?>">
                    </div>
                    <div class="form-group" style="flex: 1 1 200px;">
                        <label>Footer Text</label>
                        <input type="text" name="report_footer" class="form-control" value="<?php echo htmlspecialchars($report_footer); ?>">
                    </div>
                </div>

                <div class="report-filters">
                    <div class="form-group">
                        <label>Sort By</label>
                        <select name="sort" class="form-control" onchange="this.form.submit()">
                            <option value="default" <?php echo $sort=='default'?'selected':''; ?>>Default (Purok/HH)</option>
                            <option value="name_asc" <?php echo $sort=='name_asc'?'selected':''; ?>>Name A-Z</option>
                            <option value="name_desc" <?php echo $sort=='name_desc'?'selected':''; ?>>Name Z-A</option>
                            <option value="age_asc" <?php echo $sort=='age_asc'?'selected':''; ?>>Age (Youngest First)</option>
                            <option value="age_desc" <?php echo $sort=='age_desc'?'selected':''; ?>>Age (Oldest First)</option>
                            <option value="income_desc" <?php echo $sort=='income_desc'?'selected':''; ?>>Income (High-Low)</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <label>Age From</label>
                        <input type="number" name="age_from" class="form-control" value="<?php echo htmlspecialchars($age_from); ?>" min="0" onchange="this.form.submit()">
                    </div>
                    <div class="form-group">
                        <label>Age To</label>
                        <input type="number" name="age_to" class="form-control" value="<?php echo htmlspecialchars($age_to); ?>" min="0" onchange="this.form.submit()">
                    </div>
                    <div class="form-group">
                        <label>PWD Filter</label>
                        <select name="pwd_filter" class="form-control" onchange="this.form.submit()">
                            <option value="">All Residents</option>
                            <option value="pwd" <?php echo $pwd_filter=='pwd'?'selected':''; ?>>PWD Only</option>
                            <option value="non_pwd" <?php echo $pwd_filter=='non_pwd'?'selected':''; ?>>Non-PWD Only</option>
                        </select>
                    </div>
                    <div class="form-group" style="display: flex; align-items: center; gap: 8px; padding-bottom: 8px;">
                        <input type="checkbox" name="include_logo" id="include_logo" value="1" <?php echo $include_logo == '1' ? 'checked' : ''; ?> style="width: 18px; height: 18px;" onchange="this.form.submit()">
                        <label for="include_logo" style="margin: 0; font-weight: 600;"> Include Logo</label>
                    </div>
                </div>
                
                <div class="report-filters" style="justify-content: flex-end;">
                    <div class="form-group" style="display:flex; gap:10px; flex: none;">
                        <button type="submit" class="btn btn-secondary">Apply Filters</button>
                        <button type="submit" name="generate_excel" value="1" class="btn btn-primary" formtarget="_blank"><i class="fas fa-file-excel"></i> Excel</button>
                        <button type="submit" name="preview_pdf" value="1" class="btn btn-info" formtarget="_blank" style="background-color: #17a2b8; color: white;"><i class="fas fa-eye"></i> Preview PDF</button>
                        <button type="submit" name="generate_pdf" value="1" class="btn btn-success" formtarget="_blank"><i class="fas fa-download"></i> Download PDF</button>
                        <a href="reports.php" class="btn btn-light" title="Reset All Settings"><i class="fas fa-undo"></i></a>
                    </div>
                </div>
            </fieldset>
        </form>

        <div class="table-responsive">
            <table class="table">
                <thead>
                    <tr>
                        <?php foreach($selected_cols as $ckey) echo "<th>".$all_columns[$ckey]."</th>"; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $result = $conn->query($base_sql);
                    if ($result && $result->num_rows > 0) {
                        $current_household_id = null;
                        while ($row = $result->fetch_assoc()) {
                            if (!row_passes_filters($row, $age_from, $age_to, $pwd_filter)) continue;
                            
                            if ($current_household_id !== null && $row['household_id'] !== $current_household_id) {
                                echo '<tr class="household-separator"><td colspan="'.count($selected_cols).'"></td></tr>';
                            }
                            $current_household_id = $row['household_id'];

                            echo "<tr>";
                            foreach($selected_cols as $ckey) {
                                switch($ckey) {
                                    case 'name': echo "<td><strong>".htmlspecialchars(strtoupper($row['last_name'].", ".$row['first_name'] . ' ' . $row['suffix']))."</strong></td>"; break;
                                    case 'age': echo "<td>".date_diff(date_create($row['birthday']), date_create('now'))->y."</td>"; break;
                                    case 'pwd': echo "<td>".($row['is_pwd']?'<span class="badge-pwd">Yes</span>':'No')."</td>"; break;
                                    case 'hh_no': echo "<td>".htmlspecialchars($row['household_no'])."</td>"; break;
                                    case 'civil_status': echo "<td>".$row['civil_status']."</td>"; break;
                                    case 'purok': echo "<td>".$row['purok']."</td>"; break;
                                    case 'relation': echo "<td>".htmlspecialchars($row['relation_to_head'])."</td>"; break;
                                    case 'occupation': echo "<td>".$row['occupation']."</td>"; break;
                                    case 'education': echo "<td>".htmlspecialchars($row['educational_attainment'])."</td>"; break;
                                    case 'income': echo "<td>".number_format($row['monthly_income'],2)."</td>"; break;
                                    case 'pregnant': echo "<td>".($row['is_pregnant']?'Yes':'No')."</td>"; break;
                                    case 'breastfeeding': echo "<td>".($row['is_breastfeeding']?'Yes':'No')."</td>"; break;
                                    case 'fp_method': echo "<td>".$row['fp_method']."</td>"; break;
                                    case 'youth': echo "<td>".htmlspecialchars($row['youth_status'])."</td>"; break;
                                    case 'health': echo "<td>".compile_details($row, $health_fields)."</td>"; break;
                                    case 'food_prod': echo "<td>".compile_details($row, $food_prod_fields)."</td>"; break;
                                    default: echo "<td>".htmlspecialchars($row[$ckey] ?? '')."</td>"; break;
                                }
                            }
                            echo "</tr>";
                        }
                    } else {
                        echo "<tr><td colspan='".count($selected_cols)."' style='text-align:center;'>No residents found matching your current filters.</td></tr>";
                    }
                    ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
<?php include 'footer.php'; ?>