<?php
// Export Excel for Incoming Documents using PhpSpreadsheet
define('LUNA_APP', true);

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Check POST method
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    exit('Method Not Allowed');
}

// Include autoloader and database
require_once __DIR__ . '/../../vendor/autoload.php';
require_once __DIR__ . '/../../config/database.php';

// Load language messages
require_once __DIR__ . '/../../config/messages.php';
$messages = load_messages();

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

// Get export parameters
$exportType = $_POST['export_type'] ?? 'all';
$companyId = $_POST['company_id'] ?? '';
$documentNumber = $_POST['document_number'] ?? '';
$dateFrom = $_POST['date_from'] ?? '';
$dateTo = $_POST['date_to'] ?? '';

// Build SQL query
$sql = "SELECT 
    d.Id,
    d.ID_Symbol,
    d.TitleVi,
    c.Name_Vn as Company_Name,
    cat.Name_Vn as Category_Name,
    dt.Name_Vn as DocType_Name,
    dpt.Name_Vn as DocPageType_Name,
    d.EffectiveDate,
    d.Quantity,
    d.FileStorage,
    d.Notes,
    d.CreatedAt,
    dw.Hoten as Exporter_Name
FROM Document d
LEFT JOIN Company c ON d.ID_Company = c.ID_Company
LEFT JOIN Category cat ON d.ID_Category = cat.ID_Category
LEFT JOIN DocType dt ON d.DocType = dt.Id_Type
LEFT JOIN DocPageType dpt ON d.TypeDoc = dpt.Id_Type
LEFT JOIN DataWork dw ON d.ID_Export = dw.Maso_NV
WHERE 1=1";

$params = [];

// Apply filters
if ($exportType === 'company' && !empty($companyId)) {
    $sql .= " AND d.ID_Company = ?";
    $params[] = $companyId;
} elseif ($exportType === 'document_number' && !empty($documentNumber)) {
    $sql .= " AND d.ID_Symbol LIKE ?";
    $params[] = '%' . $documentNumber . '%';
} elseif ($exportType === 'date_range' && !empty($dateFrom) && !empty($dateTo)) {
    $sql .= " AND d.EffectiveDate BETWEEN ? AND ?";
    $params[] = $dateFrom;
    $params[] = $dateTo;
}

$sql .= " ORDER BY d.EffectiveDate DESC, d.Id DESC";

// Query database
$documents = [];
try {
    $conn = db_connect();
    if ($conn) {
        if (!empty($params)) {
            $stmt = sqlsrv_query($conn, $sql, $params);
        } else {
            $stmt = sqlsrv_query($conn, $sql);
        }
        
        if ($stmt !== false) {
            while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
                $documents[] = $row;
            }
            sqlsrv_free_stmt($stmt);
        }
        db_disconnect($conn);
    }
} catch (Exception $e) {
    // If error, just export empty file with headers
}

// Create new Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle($messages['excel_sheet_title'] ?? 'Công Văn Đến');

// Set header row
$headers = [
    $messages['excel_header_stt'] ?? 'STT',
    $messages['excel_header_doc_number'] ?? 'Mã số',
    $messages['excel_header_title'] ?? 'Tiêu đề',
    $messages['excel_header_company'] ?? 'Công ty',
    $messages['excel_header_category'] ?? 'Hạng mục',
    $messages['excel_header_doc_type'] ?? 'Thể loại',
    $messages['excel_header_page_type'] ?? 'Loạt giấy tờ',
    $messages['excel_header_effective_date'] ?? 'Ngày hiệu lực',
    $messages['excel_header_quantity'] ?? 'Số lượng',
    $messages['excel_header_storage'] ?? 'Nơi lưu',
    $messages['excel_header_exporter'] ?? 'Người xuất',
    $messages['excel_header_notes'] ?? 'Ghi chú',
    $messages['excel_header_created_at'] ?? 'Ngày tạo'
];
$columnLetters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M'];

// Write headers
foreach ($headers as $index => $header) {
    $cell = $columnLetters[$index] . '1';
    $sheet->setCellValue($cell, $header);
}

// Style header row
$headerStyle = [
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF'],
        'size' => 11
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['rgb' => '4472C4']
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['rgb' => '000000']
        ]
    ]
];

$sheet->getStyle('A1:M1')->applyFromArray($headerStyle);
$sheet->getRowDimension(1)->setRowHeight(25);

// Write data rows
$rowIndex = 2;
foreach ($documents as $index => $doc) {
    // Format dates
    $dateEffective = '';
    if (isset($doc['EffectiveDate']) && $doc['EffectiveDate']) {
        if ($doc['EffectiveDate'] instanceof DateTime) {
            $dateEffective = $doc['EffectiveDate']->format('d/m/Y');
        } else {
            $dateEffective = date('d/m/Y', strtotime($doc['EffectiveDate']));
        }
    }
    
    $createdAt = '';
    if (isset($doc['CreatedAt']) && $doc['CreatedAt']) {
        if ($doc['CreatedAt'] instanceof DateTime) {
            $createdAt = $doc['CreatedAt']->format('d/m/Y H:i:s');
        } else {
            $createdAt = date('d/m/Y H:i:s', strtotime($doc['CreatedAt']));
        }
    }
    
    // Write data
    $sheet->setCellValue('A' . $rowIndex, $index + 1);
    $sheet->setCellValue('B' . $rowIndex, $doc['ID_Symbol'] ?? '');
    $sheet->setCellValue('C' . $rowIndex, $doc['TitleVi'] ?? '');
    $sheet->setCellValue('D' . $rowIndex, $doc['Company_Name'] ?? '');
    $sheet->setCellValue('E' . $rowIndex, $doc['Category_Name'] ?? '');
    $sheet->setCellValue('F' . $rowIndex, $doc['DocType_Name'] ?? '');
    $sheet->setCellValue('G' . $rowIndex, $doc['DocPageType_Name'] ?? '');
    $sheet->setCellValue('H' . $rowIndex, $dateEffective);
    $sheet->setCellValue('I' . $rowIndex, $doc['Quantity'] ?? '');
    $sheet->setCellValue('J' . $rowIndex, $doc['FileStorage'] ?? '');
    $sheet->setCellValue('K' . $rowIndex, $doc['Exporter_Name'] ?? '');
    $sheet->setCellValue('L' . $rowIndex, $doc['Notes'] ?? '');
    $sheet->setCellValue('M' . $rowIndex, $createdAt);
    
    $rowIndex++;
}

// Apply borders to all data
if (count($documents) > 0) {
    $lastRow = $rowIndex - 1;
    $dataStyle = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['rgb' => 'CCCCCC']
            ]
        ]
    ];
    $sheet->getStyle('A1:M' . $lastRow)->applyFromArray($dataStyle);
    
    // Zebra striping (alternate row colors)
    for ($i = 2; $i <= $lastRow; $i++) {
        if ($i % 2 == 0) {
            $sheet->getStyle('A' . $i . ':M' . $i)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'startColor' => ['rgb' => 'F2F2F2']
                ]
            ]);
        }
    }
}

// Auto-size columns
foreach ($columnLetters as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

// Set minimum width for some columns
$sheet->getColumnDimension('C')->setWidth(40); // Tiêu đề
$sheet->getColumnDimension('D')->setWidth(25); // Công ty
$sheet->getColumnDimension('E')->setWidth(20); // Hạng mục
$sheet->getColumnDimension('F')->setWidth(20); // Thể loại
$sheet->getColumnDimension('G')->setWidth(20); // Loạt giấy tờ
$sheet->getColumnDimension('J')->setWidth(25); // Nơi lưu
$sheet->getColumnDimension('K')->setWidth(20); // Người xuất
$sheet->getColumnDimension('L')->setWidth(30); // Ghi chú

// Freeze header row
$sheet->freezePane('A2');

// Center align for STT, Mã số, Ngày, Số lượng
$sheet->getStyle('A2:A' . ($rowIndex - 1))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('B2:B' . ($rowIndex - 1))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('H2:H' . ($rowIndex - 1))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('I2:I' . ($rowIndex - 1))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('M2:M' . ($rowIndex - 1))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Generate filename
$filename = 'CongVanDen_';
switch ($exportType) {
    case 'company':
        $filename .= 'CongTy_';
        break;
    case 'document_number':
        $filename .= 'MaSo_';
        break;
    case 'date_range':
        if (!empty($dateFrom)) {
            $filename .= str_replace('-', '', $dateFrom) . '_';
            if (!empty($dateTo)) {
                $filename .= str_replace('-', '', $dateTo) . '_';
            }
        }
        break;
    default:
        $filename .= 'TatCa_';
}
$filename .= date('Ymd_His') . '.xlsx';

// Set headers for Excel download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: max-age=0');
header('Pragma: public');

// Write file to output
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');

// Clean up
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;