<?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");
    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;
}


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;
}

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

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


$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();

$sql = "
    SELECT aa.id as master_id, aa.registration_id, aa.user_id, aa.application_table_id, a.*, aa.pdf_path
    FROM all_applications aa
    JOIN tech_applications a ON a.id = aa.application_table_id
    WHERE aa.ad_id = ? AND aa.post_id = ? AND aa.application_type = '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();

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

$section_styles = [
    'PRIMARY DETAILS:'           => 'FFDDEBF7',
    'EDUCATIONAL QUALIFICATION:' => 'FFFCE4D6',
    'TRADE DETAILS:'             => 'FFEFD9C1',
    'EXPERIENCE:'                => 'FFE2EFDA',
    'PAYMENT DETAILS:'           => 'FFF8CBAD',
    'FILLED BY SCREENING COMMITTEE:' => 'FFD9E1F2'
];

$columns = [
    'Sno',
    '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',
    'SSC Marks',
    'HSC Marks',
    'ITI Marks',
    'Trade Name',
    'Certificate',
    'Board',
    'From Date',
    'To Date',
    'Year of Passing',
    'Percentage',
    'Total Experience (Years, Months, Days)',
    'Challan Number',
    'Challan Date',
    'Meets Screening Criteria',
    'Recommended for Interview/Test',
    'Remarks',
    'Admin Remarks'
];

$section_ranges = [
    [1, 21, 'PRIMARY DETAILS:'],
    [22, 24, 'EDUCATIONAL QUALIFICATION:'],
    [25, 31, 'TRADE DETAILS:'],
    [32, 32, 'EXPERIENCE:'],
    [33, 34, 'PAYMENT DETAILS:'],
    [35, 38, '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";
    $sheet->setCellValue($cell1, $label);
    $sheet->mergeCells("$cell1:$cell2");

    $bgColor = $section_styles[$label];

    // Style merged section title (row 1)
    $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

    // Style each column header cell in row 2
    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'); // Always black text
    }
}


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

$row_num = 3;
$sno = 1;
foreach ($applications as $app) {
    $col_num = 1;
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $sno++);
    // $sheet->setCellValue(columnLetter($col_num++) . $row_num, $app['registration_id']);
    $regCell = columnLetter($col_num++) . $row_num;


    $app_type = 'tech';
    // Detect protocol and host
    $protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? "https" : "http";
    $host = $_SERVER['HTTP_HOST'];
    // Get base directory dynamically (e.g., /intern-project-copy/admin_html)
    $scriptDir = dirname($_SERVER['SCRIPT_NAME']);
    $scriptDir = rtrim(str_replace('\\', '/', $scriptDir), '/');
    // Combine to form base URL
    $baseUrl = "{$protocol}://{$host}{$scriptDir}";
    // Final view link
    $viewUrl = "{$baseUrl}/view_application.php?application_id={$app['application_table_id']}&type={$app_type}";
    // $app_type = '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']);

    $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();

    foreach (
        [
            'ssc_name',
            'dob',
            'age',
            'gender',
            'category',
            'disability',
            'ex_servicemen',
            'gov_employee',
            'csir_employee',
            'marital_status',
            'email',
            'mobile',
            'secondary_email',
            'alt_mobile',
            'relative_in_csir'
        ] as $key
    ) {
        $val = $prim[$key] ?? '';
        if ($key === 'alt_mobile' && (!empty($val) && $val != 0)) $val = "'" . $val;
        $sheet->setCellValue(columnLetter($col_num++) . $row_num, $val);
    }

    $sheet->setCellValue(columnLetter($col_num++) . $row_num, concatAddress($prim, 'corr'));
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, concatAddress($prim, 'perm'));

    $qual_marks = ['ssc' => '', 'hsc' => '', 'iti' => ''];
    $qstmt = $conn->prepare("SELECT qualification_type, marks FROM tech_qualifications WHERE application_id = ?");
    $qstmt->bind_param("i", $app['id']);
    $qstmt->execute();
    $qres = $qstmt->get_result();
    while ($q = $qres->fetch_assoc()) {
        $qual_marks[strtolower($q['qualification_type'])] = $q['marks'];
    }
    $qstmt->close();
    foreach (['ssc', 'hsc', 'iti'] as $qtype) {
        $sheet->setCellValue(columnLetter($col_num++) . $row_num, $qual_marks[$qtype] ?? '');
    }

    $tstmt = $conn->prepare("SELECT * FROM tech_trade_details WHERE application_id = ? LIMIT 1");
    $tstmt->bind_param("i", $app['id']);
    $tstmt->execute();
    $trade = $tstmt->get_result()->fetch_assoc() ?: [];
    $tstmt->close();
    foreach (['trade_name', 'trade_certificate', 'trade_board', 'from_date', 'to_date', 'year_of_passing', 'percentage'] as $key) {
        $sheet->setCellValue(columnLetter($col_num++) . $row_num, $trade[$key] ?? '');
    }

    $estmt = $conn->prepare("SELECT from_date, to_date FROM tech_experience 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_date'] ?? '');
        $to = strtotime($e['to_date'] ?? '');
        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);

    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $app['payment_reference_no'] ?? '');
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $app['payment_date'] ?? '');
    for ($i = 0; $i < 4; $i++) $sheet->setCellValue(columnLetter($col_num++) . $row_num, '');
    $row_num++;
}

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

$saveDir = __DIR__ . '/sheets/';
if (!file_exists($saveDir)) mkdir($saveDir, 0777, true);
$savePath = $saveDir . 'tech_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";