<?php
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

require_once dirname(__DIR__) . "/vendor/autoload.php";
require_once dirname(__DIR__) . '/public_html/sites/config/config.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;

if (!isset($_SESSION['admin_logged_in']) || !in_array($_SESSION['admin_role'], ['superadmin', 'manager'])) {
    header("Location: admin_dashboard.php");
    // echo $_SESSION['admin_role'];

    exit;
}

// 1) basic session checks
if (
    empty($_SESSION['admin_id']) || empty($_SESSION['session_token'])
) {
    header("Location: admin_login.php");
    exit;
}

// 2) pull the token (and role) from the database
$stmt = $conn->prepare("SELECT session_token FROM admin_users WHERE id = ?");
$stmt->bind_param("i", $_SESSION['admin_id']);
$stmt->execute();
$result = $stmt->get_result()->fetch_assoc();
$stmt->close();

// 3) if the DB token is gone or doesn’t match the session’s, force logout
if (
    ! $result || $result['session_token'] !== $_SESSION['session_token']
) {
    session_unset();
    session_destroy();
    header("Location: admin_login.php?error=" . urlencode("Your session has expired."));
    exit;
}


// Helper: Convert column index to Excel letters
function columnLetter($col)
{
    $div = $col;
    $letter = '';
    while ($div > 0) {
        $mod = ($div - 1) % 26;
        $letter = chr(65 + $mod) . $letter;
        $div = (int)(($div - $mod) / 26);
    }
    return $letter;
}

// Helper: Concatenate address fields
function concatAddress($row, $prefix)
{
    return implode(", ", array_filter([
        $row["{$prefix}_house"] ?? '',
        $row["{$prefix}_city"] ?? '',
        $row["{$prefix}_district"] ?? '',
        $row["{$prefix}_state"] ?? '',
        $row["{$prefix}_pincode"] ?? ''
    ]));
}

// Input
// $ad_id = $_GET['ad_id'] ?? 1;
// $post_id = $_GET['post_id'] ?? 1;
// $ad_id = 5;
// $post_id = 19;
$ad_id = (int)($_GET['ad_id'] ?? 0);
$post_id = (int)($_GET['post_id'] ?? 0);
$download = isset($_GET['download']) && $_GET['download'] == 1;


// Fetch post details
$post_stmt = $conn->prepare("SELECT post_code, post_title FROM posts WHERE id = ?");
$post_stmt->bind_param("i", $post_id);
$post_stmt->execute();
$post_row = $post_stmt->get_result()->fetch_assoc() ?: ['post_code' => '', 'post_title' => ''];
$post_stmt->close();

// Fetch applications
$sql = "
    SELECT aa.id as master_id, aa.registration_id, aa.user_id, aa.application_table_id, aa.status as master_status, a.*, aa.pdf_path
    FROM all_applications aa
    JOIN applications a ON a.id = aa.application_table_id
    WHERE aa.ad_id = ? AND aa.post_id = ? AND aa.application_type = 'non-tech' AND aa.status = 'submitted'
";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $ad_id, $post_id);
$stmt->execute();
$res = $stmt->get_result();
$applications = [];
while ($row = $res->fetch_assoc()) $applications[] = $row;
$stmt->close();

// Build sheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$row_num = 1;

// ---- SECTION HEADERS AND STYLES ----
$section_styles = [
    'PRIMARY DETAILS:'           => 'FFDDEBF7', // Light Blue
    'EDUCATIONAL QUALIFICATION:' => 'FFFCE4D6', // Light Orange
    'EXPERIENCE:'                => 'FFE2EFDA', // Light Green
    'PAYMENT DETAILS:'           => 'FFF8CBAD', // Light Pink
    'FILLED BY SCREENING COMMITTEE:' => 'FFD9E1F2' // Light Purple
];

// Define columns as per your structure:
$columns = [
    'Sno',
    // --- PRIMARY DETAILS
    'Registration ID',
    'Post Code',
    'Post Title',
    'Name',
    'DOB',
    'Age',
    'Gender',
    'Category',
    'Physically Disabled (Yes/No)',
    'Ex Serviceman (Yes/No)',
    'Government Employee (Yes/No)',
    'CSIR Employee (Yes/No)',
    'Marital Status',
    'Primary Email ID',
    'Primary Number',
    'Alternative Email ID',
    'Alternative Number',
    'Relative in CSIR (Yes/No)',
    'Corresponding Address',
    'Permenant Address',
    // --- EDUCATIONAL QUALIFICATION
    'SSC Marks',
    'HSC Marks',
    'Diploma Marks',
    'UG Marks',
    'PG Marks',
    // --- EXPERIENCE
    'Total Experience (Years)',
    // --- PAYMENT
    'Challan Number',
    'Challan Date',
    // --- SCREENING COMMITTEE
    'Meets Screening Criteria [Yes/No]',
    'Recommended for Interview/Test [Yes/No]',
    'Screening Remarks',
    'Admin Remarks'
];

// --- ROW 1: SECTION HEADERS (color and merge as needed) ---
$section_ranges = [
    // start_col, end_col, section name
    [1, 21, 'PRIMARY DETAILS:'],
    [22, 26, 'EDUCATIONAL QUALIFICATION:'],
    [27, 27, 'EXPERIENCE:'],
    [28, 29, 'PAYMENT DETAILS:'],
    [30, 33, 'FILLED BY SCREENING COMMITTEE:'],
];
// foreach ($section_ranges as [$start, $end, $label]) {
//     $cell1 = columnLetter($start) . "1";
//     $cell2 = columnLetter($end) . "1";
//     $sheet->setCellValue($cell1, $label);
//     $sheet->mergeCells("$cell1:$cell2");
//     $sheet->getStyle("$cell1:$cell2")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($section_styles[$label]);
//     $sheet->getStyle("$cell1:$cell2")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
// }
foreach ($section_ranges as [$start, $end, $label]) {
    $cell1 = columnLetter($start) . "1";
    $cell2 = columnLetter($end) . "1";
    $bgColor = $section_styles[$label];

    // Row 1: Section title
    $sheet->setCellValue($cell1, $label);
    $sheet->mergeCells("$cell1:$cell2");

    $style = $sheet->getStyle("$cell1:$cell2");
    $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($bgColor);
    $style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $style->getFont()->getColor()->setARGB('FF000000'); // Always black text

    // Row 2: Column headers within this section
    for ($i = $start; $i <= $end; $i++) {
        $col = columnLetter($i);
        $cell = $col . '2';
        $headerStyle = $sheet->getStyle($cell);
        $headerStyle->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($bgColor);
        $headerStyle->getFont()->getColor()->setARGB('FF000000'); // Black header text
    }
}


// --- ROW 2: COLUMN TITLES ---
$col_num = 1;
foreach ($columns as $col_title) {
    $sheet->setCellValue(columnLetter($col_num) . '2', $col_title);
    $col_num++;
}

// --- ROWS 3+: DATA ---
$row_num = 3;
$sno = 1;
foreach ($applications as $app) {
    $col_num = 1;

    // SNO
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $sno++);

    // -- Fetch primary details for this user
    $prim_stmt = $conn->prepare("SELECT * FROM primary_details WHERE user_id = ? LIMIT 1");
    $prim_stmt->bind_param("i", $app['user_id']);
    $prim_stmt->execute();
    $prim = $prim_stmt->get_result()->fetch_assoc() ?: [];
    $prim_stmt->close();

    // --- PRIMARY DETAILS
    // $sheet->setCellValue(columnLetter($col_num++) . $row_num, $app['registration_id']);
    $regCell = columnLetter($col_num++) . $row_num;


    $app_type = 'non-tech';
    // Dynamically determine protocol and host
    $protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? "https" : "http";
    $host = $_SERVER['HTTP_HOST'];
    // Get current script's directory, e.g., /intern-project-copy/admin_html
    $scriptDir = dirname($_SERVER['SCRIPT_NAME']);
    $scriptDir = rtrim(str_replace('\\', '/', $scriptDir), '/');
    // Construct base URL
    $baseUrl = "{$protocol}://{$host}{$scriptDir}";
    // Final dynamic view URL
    $viewUrl = "{$baseUrl}/view_application.php?application_id={$app['application_table_id']}&type={$app_type}";
    // $app_type = 'non-tech';
    // $viewUrl = "http://localhost/intern-project-copy/admin_html/view_application.php?application_id={$app['application_table_id']}&type={$app_type}";

    $sheet->setCellValue($regCell, $app['registration_id']);
    $sheet->getCell($regCell)->getHyperlink()->setUrl($viewUrl);
    $sheet->getStyle($regCell)->getFont()->setUnderline(true)->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE);

    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $post_row['post_code']);
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $post_row['post_title']);
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['ssc_name'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['dob'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['age'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['gender'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['category'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['disability'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['ex_servicemen'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['gov_employee'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['csir_employee'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['marital_status'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['email'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['mobile'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['secondary_email'] ?? '');
    // $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['alt_mobile'] ?? '');
    $alt_mobile = (!empty($prim['alt_mobile']) && $prim['alt_mobile'] != 0) ? ("'" . $prim['alt_mobile']) : '';
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $alt_mobile);
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $prim['relative_in_csir'] ?? '');

    // --- Address concat
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, concatAddress($prim, "corr"));
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, concatAddress($prim, "perm"));

    // --- EDUCATIONAL QUALIFICATION (marks only)
    $qual_types = ['SSC', 'HSC', 'Diploma', 'UG', 'PG'];
    $qual_marks = [];
    $qstmt = $conn->prepare("SELECT qualification_type, marks FROM qualifications WHERE application_id = ?");
    $qstmt->bind_param("i", $app['id']);
    $qstmt->execute();
    $qres = $qstmt->get_result();
    while ($q = $qres->fetch_assoc()) {
        $qual_marks[$q['qualification_type']] = $q['marks'];
    }
    $qstmt->close();
    foreach ($qual_types as $qtype) {
        $sheet->setCellValue(columnLetter($col_num++) . $row_num, $qual_marks[$qtype] ?? '');
    }

    // --- EXPERIENCE: Total duration in years, months, days (sum of all experiences)
    $estmt = $conn->prepare("SELECT `from`, `to` FROM experiences WHERE application_id = ?");
    $estmt->bind_param("i", $app['id']);
    $estmt->execute();
    $exp_res = $estmt->get_result();
    $total_days = 0;
    while ($e = $exp_res->fetch_assoc()) {
        $from = strtotime($e['from'] ?? '');
        $to = strtotime($e['to'] ?? '');
        if ($from && $to && $to >= $from) {
            $total_days += ($to - $from) / 86400;
        }
    }
    $estmt->close();

    $years = floor($total_days / 365);
    $months = floor(($total_days % 365) / 30.44);
    $days = round($total_days - ($years * 365) - ($months * 30.44));
    $exp_str = "";
    if ($years > 0) $exp_str .= "{$years} years, ";
    if ($months > 0 || $years > 0) $exp_str .= "{$months} months, ";
    $exp_str .= "{$days} days";
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $exp_str);

    // --- PAYMENT
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $app['payment_reference_no'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $app['payment_date'] ?? '');

    // --- SCREENING COMMITTEE: Leave empty
    for ($i = 0; $i < 4; $i++) {
        $sheet->setCellValue(columnLetter($col_num++) . $row_num, '');
    }

    $row_num++;
}

// Auto-size all columns
foreach (range(1, count($columns)) as $col_num) {
    $col_letter = columnLetter($col_num);
    $sheet->getColumnDimension($col_letter)->setAutoSize(true);
}



// Save file
$saveDir = __DIR__ . '/sheets/';
if (!file_exists($saveDir)) mkdir($saveDir, 0777, true);
$savePath = $saveDir . 'screening_applications_ad' . $ad_id . '_post' . $post_id . '.xlsx';

$writer = new Xlsx($spreadsheet);
$writer->save($savePath);
ob_end_clean();
if ($download && file_exists($savePath)) {
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . basename($savePath) . '"');
    header('Content-Length: ' . filesize($savePath));
    readfile($savePath);
    exit;
} else {
    echo "Excel file generated: $savePath\n";
}


// echo "Excel file generated: $savePath\n";