<?php
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;

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 = 18;

$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, sa.*, aa.pdf_path
    FROM all_applications aa
    JOIN scientist_applications sa ON sa.id = aa.application_table_id
    WHERE aa.ad_id = ? AND aa.post_id = ? AND aa.application_type = 'scientist' 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',
    'Ph.D. DETAILS:'                => 'FFEEECE1',
    'EMPLOYMENT EXPERIENCE:'        => 'FFE2EFDA',
    'PAYMENT DETAILS:'              => 'FFF8CBAD',
    'PUBLICATIONS:'                 => 'FFE4DFEC',
    'FILLED BY SCREENING COMMITTEE:' => 'FFD9E1F2'
];

$columns = [
    'Sno',
    'Registration ID',
    'Post Code',
    'Post Title',
    'Name',
    'DOB',
    'Age',
    'Gender',
    'Category',
    'Physically Disabled',
    'Ex Serviceman',
    'Govt Employee',
    'CSIR Employee',
    'Marital Status',
    'Primary Email',
    'Primary Phone',
    'Alt Email',
    'Alt Phone',
    'Relative in CSIR',
    'Correspondence Address',
    'Permanent Address',
    'SSC Marks',
    'HSC Marks',
    'UG Marks',
    'PG Marks',
    'Ph.D. Status',
    'Title of the Thesis',
    'Relevant Area',
    'Department',
    'University',
    'Ph.D. Completion Date',
    'Total Experience (Y, M, D)',
    'Challan Number',
    'Challan Date',
    'Total Publications',
    'Meets Screening Criteria',
    'Recommended',
    'Remarks',
    'Admin Remarks'
];

$section_ranges = [
    [1, 21, 'PRIMARY DETAILS:'],
    [22, 25, 'EDUCATIONAL QUALIFICATION:'],
    [26, 31, 'Ph.D. DETAILS:'],
    [32, 32, 'EMPLOYMENT EXPERIENCE:'],
    [33, 34, 'PAYMENT DETAILS:'],
    [35, 35, 'PUBLICATIONS:'],
    [36, 39, '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");
    $style = $sheet->getStyle("$cell1:$cell2");
    $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($section_styles[$label]);
    $style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $style->getFont()->getColor()->setARGB('FF000000');
    for ($i = $start; $i <= $end; $i++) {
        $cell = columnLetter($i) . '2';
        $sheet->getStyle($cell)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($section_styles[$label]);
    }
}

$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++);
    $regCell = columnLetter($col_num++) . $row_num;
    $viewUrl = "http://localhost/intern-project-copy/admin_html/view_application.php?application_id={$app['application_table_id']}&type=scientist";
    $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 = ?");
    $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 = ['SSC' => '', 'HSC' => '', 'UG' => '', 'PG' => ''];
    $qstmt = $conn->prepare("SELECT qualification_type, marks FROM scientist_qualifications WHERE application_id = ?");
    $qstmt->bind_param("i", $app['id']);
    $qstmt->execute();
    $qres = $qstmt->get_result();
    while ($q = $qres->fetch_assoc()) {
        $qt = strtoupper($q['qualification_type']);
        if (isset($qual[$qt])) $qual[$qt] = $q['marks'];
    }
    $qstmt->close();
    foreach (['SSC', 'HSC', 'UG', 'PG'] as $qtype) {
        $sheet->setCellValue(columnLetter($col_num++) . $row_num, $qual[$qtype]);
    }

    $phd = $conn->query("SELECT * FROM scientist_phd_details WHERE application_id = {$app['id']} LIMIT 1")->fetch_assoc() ?: [];
    foreach (['status', 'title', 'area', 'department', 'university', 'date'] as $field) {
        $sheet->setCellValue(columnLetter($col_num++) . $row_num, $phd[$field] ?? '');
    }

    $exp_res = $conn->query("SELECT from_date, to_date FROM scientist_experiences WHERE application_id = {$app['id']}");
    $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;
    }
    $years = floor($total_days / 365);
    $months = floor(($total_days % 365) / 30.44);
    $days = round($total_days - ($years * 365) - ($months * 30.44));
    $exp_str = trim("{$years} years, {$months} months, {$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'] ?? '');

    $pub_count = $conn->query("SELECT COUNT(*) as total FROM scientist_publications WHERE application_id = {$app['id']}")->fetch_assoc()['total'] ?? 0;
    $sheet->setCellValue(columnLetter($col_num++) . $row_num, $pub_count);

    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 . 'scientist_applications_ad' . $ad_id . '_post' . $post_id . '.xlsx';

$writer = new Xlsx($spreadsheet);
$writer->save($savePath);

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