<?php
session_start();
include 'layouts/dbconn.php';

// Make sure autoloader is included
require_once __DIR__ . '/vendor/phpoffice/vendor/autoload.php';

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

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    $_SESSION['error_message'] = 'You must be logged in to download a template.';
    header('Location: import.php');
    exit();
}

// Check if import type is provided
if (!isset($_GET['type']) || empty($_GET['type'])) {
    $_SESSION['error_message'] = 'Import type is required.';
    header('Location: import.php');
    exit();
}

$importType = $_GET['type'];
$format = $_GET['format'] ?? 'xlsx'; // Default to Excel format

// Validate import type
if (!in_array($importType, ['transit', 'local'])) {
    $_SESSION['error_message'] = 'Invalid import type.';
    header('Location: import.php');
    exit();
}

// Validate format
if (!in_array($format, ['xlsx', 'csv'])) {
    $_SESSION['error_message'] = 'Invalid format. Only XLSX and CSV are supported.';
    header('Location: import.php');
    exit();
}

// Check if we should include container template
$includeContainers = isset($_GET['containers']) && $_GET['containers'] === '1';

try {
    // Get configuration columns for this import type
    $stmt = $conn->prepare("
        SELECT * FROM import_config 
        WHERE import_type = ? 
        ORDER BY ordinal_position
    ");
    
    $stmt->bind_param("s", $importType);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $columns = [];
    while ($row = $result->fetch_assoc()) {
        $columns[] = $row;
    }
    
    // Create a new Spreadsheet
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Import Template');
    
    // Add headers
    $col = 1;
    foreach ($columns as $column) {
        $cellValue = $column['display_name'];
        if ($column['is_required']) {
            $cellValue .= ' *';
        }
        $sheet->setCellValueByColumnAndRow($col, 1, $cellValue);
        
        // Mark required fields with a different style
        if ($column['is_required']) {
            $cell = $sheet->getCellByColumnAndRow($col, 1);
            $style = $cell->getStyle();
            $style->getFont()->setBold(true);
            $style->getFont()->getColor()->setRGB('FF0000');
        }
        
        $col++;
    }

    // Prepare sample data based on import type
    $sampleData = [];
    $sampleRow = [];
    
    // Build sample data based on the columns configuration
    foreach ($columns as $column) {
        $fieldName = $column['field_name'];
        $fieldType = $column['field_type'];
        
        switch ($fieldName) {
            case 'customer':
                $sampleRow[] = '1'; // Sample customer ID
                break;
            case 'consignee_name':
                $sampleRow[] = 'Sample Consignee';
                break;
            case 'destination':
                $sampleRow[] = 'DR-GOM';
                break;
            case 'obl_no':
                $sampleRow[] = 'OBL123456';
                break;
            case 'eta':
            case 'initial_eta':
            case 'pre_alert_received_date':
            case 'discharge_date':
                $sampleRow[] = date('Y-m-d');
                break;
            case 'container_code':
                $sampleRow[] = 'ABCD1234567';
                break;
            case 'container_size':
                $sampleRow[] = '20';
                break;
            case 'transporter':
                $sampleRow[] = '1'; // Sample transporter ID
                break;
            case 'vno':
                $sampleRow[] = 'VN12345';
                break;
            case 'port':
                $sampleRow[] = 'Dar Port';
                break;
            case 'free_detention':
                $sampleRow[] = '14';
                break;
            case 'currency':
                $sampleRow[] = '$';
                break;
            case 'load_date':
            case 'offload_date':
                $sampleRow[] = date('Y-m-d');
                break;
            case 'remarks':
                $sampleRow[] = 'Sample remarks';
                break;
            default:
                $sampleRow[] = '';
                break;
        }
    }
    
    $sampleData[] = $sampleRow;
    
    // Add sample data to main sheet
    $row = 2;
    foreach ($sampleData as $rowData) {
        $col = 1;
        foreach ($rowData as $cellValue) {
            $sheet->setCellValueByColumnAndRow($col, $row, $cellValue);
            $col++;
        }
        $row++;
    }
    
    // Add info row with field descriptions
    $row = 3;
    $col = 1;
    foreach ($columns as $column) {
        $info = '';
        if ($column['field_type'] === 'select') {
            $validationRules = json_decode($column['validation_rules'], true);
            if (isset($validationRules['values'])) {
                $info = 'Values: ' . implode(', ', $validationRules['values']);
            } elseif (isset($validationRules['source'])) {
                $info = 'From: ' . $validationRules['source'] . ' table';
            }
        } elseif ($column['field_type'] === 'date') {
            $info = 'Format: YYYY-MM-DD';
        }
        
        if (!empty($info)) {
            $sheet->setCellValueByColumnAndRow($col, $row, $info);
            // Set cell style to italic and gray
            $cell = $sheet->getCellByColumnAndRow($col, $row);
            $style = $cell->getStyle();
            $style->getFont()->setItalic(true);
            $style->getFont()->setSize(8);
            $style->getFont()->getColor()->setRGB('808080');
        }
        
        $col++;
    }

    // If containers are included, add a container template sheet
    if ($includeContainers) {
        // Add a new sheet for containers
        $containerSheet = $spreadsheet->createSheet();
        $containerSheet->setTitle('Container Template');
        
        // Define container column headers
        $containerColumns = [
            ['field_name' => 'file_number', 'display_name' => 'Parent File Number', 'is_required' => true],
            ['field_name' => 'container_code', 'display_name' => 'Container Code', 'is_required' => true],
            ['field_name' => 'container_size', 'display_name' => 'Container Size', 'is_required' => true],
            ['field_name' => 'transporter_name', 'display_name' => 'Transporter', 'is_required' => true],
            ['field_name' => 'driver_name', 'display_name' => 'Driver Name', 'is_required' => false],
            ['field_name' => 'driver_phone_number', 'display_name' => 'Driver Phone', 'is_required' => false],
            ['field_name' => 'driver_license', 'display_name' => 'Driver License', 'is_required' => false],
            ['field_name' => 'delivery_order_apply_date', 'display_name' => 'Delivery Order Apply Date', 'is_required' => false],
            ['field_name' => 'delivery_order_received_date', 'display_name' => 'Delivery Order Received Date', 'is_required' => false],
            ['field_name' => 'obr_sct_received_date', 'display_name' => 'OBR/SCT Received Date', 'is_required' => false],
            ['field_name' => 'obr_ti_validation_date', 'display_name' => 'OBR/TI Validation Date', 'is_required' => false],
            ['field_name' => 't1_c2_approval_date', 'display_name' => 'T1/C2 Approval Date', 'is_required' => false],
            ['field_name' => 'port_charges_paid_date', 'display_name' => 'Port Charges Paid Date', 'is_required' => false],
            ['field_name' => 'whafage_paid_date', 'display_name' => 'Whafage Paid Date', 'is_required' => false],
            ['field_name' => 'loading_permit_apply_date', 'display_name' => 'Loading Permit Apply Date', 'is_required' => false],
            ['field_name' => 'truck_exit_date', 'display_name' => 'Truck Exit Date', 'is_required' => false],
            ['field_name' => 'truck_at_border_date', 'display_name' => 'Truck At Border Date', 'is_required' => false],
            ['field_name' => 'border_crossing_date', 'display_name' => 'Border Crossing Date', 'is_required' => false],
            ['field_name' => 'delivery_date', 'display_name' => 'Delivery Date', 'is_required' => false],
            ['field_name' => 'empty_return_date', 'display_name' => 'Empty Return Date', 'is_required' => false],
            ['field_name' => 'remarks', 'display_name' => 'Remarks', 'is_required' => false],
        ];
        
        // Add container headers
        $col = 1;
        foreach ($containerColumns as $column) {
            $cellValue = $column['display_name'];
            if ($column['is_required']) {
                $cellValue .= ' *';
            }
            $containerSheet->setCellValueByColumnAndRow($col, 1, $cellValue);
            
            // Mark required fields with a different style
            if ($column['is_required']) {
                $cell = $containerSheet->getCellByColumnAndRow($col, 1);
                $style = $cell->getStyle();
                $style->getFont()->setBold(true);
                $style->getFont()->getColor()->setRGB('FF0000');
            }
            
            $col++;
        }
        
        // Add sample data for containers
        $sampleContainerData = [
            ['RFT/DR/GOM/001/24', 'ABCD1234567', '20', 'Sample Transporter', 'John Doe', '1234567890', 'DL12345', 
             date('Y-m-d'), date('Y-m-d', strtotime('+1 day')), date('Y-m-d', strtotime('+2 days')), 
             date('Y-m-d', strtotime('+3 days')), date('Y-m-d', strtotime('+4 days')), date('Y-m-d', strtotime('+5 days')), 
             date('Y-m-d', strtotime('+6 days')), date('Y-m-d', strtotime('+7 days')), date('Y-m-d', strtotime('+8 days')), 
             date('Y-m-d', strtotime('+9 days')), date('Y-m-d', strtotime('+10 days')), date('Y-m-d', strtotime('+11 days')), 
             date('Y-m-d', strtotime('+12 days')), 'Sample container remarks']
        ];
        
        // Add sample container data
        $row = 2;
        foreach ($sampleContainerData as $rowData) {
            $col = 1;
            foreach ($rowData as $cellValue) {
                $containerSheet->setCellValueByColumnAndRow($col, $row, $cellValue);
                $col++;
            }
            $row++;
        }
        
        // Add container help row
        $row = 3;
        $containerInfos = [
            'Parent file number must exist',
            'Container code e.g. ABCD1234567',
            'Options: 20, 40, 40HC',
            'Transporter name',
            'Driver full name',
            'Driver contact number',
            'Driver license number',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'YYYY-MM-DD format',
            'Additional notes'
        ];
        
        $col = 1;
        foreach ($containerInfos as $info) {
            $containerSheet->setCellValueByColumnAndRow($col, $row, $info);
            // Set cell style to italic and gray
            $cell = $containerSheet->getCellByColumnAndRow($col, $row);
            $style = $cell->getStyle();
            $style->getFont()->setItalic(true);
            $style->getFont()->setSize(8);
            $style->getFont()->getColor()->setRGB('808080');
            $col++;
        }
        
        // Auto-size columns for better readability
        foreach (range(1, count($containerColumns)) as $col) {
            $containerSheet->getColumnDimensionByColumn($col)->setAutoSize(true);
        }
    }
    
    // Auto-size columns for better readability
    foreach (range(1, count($columns)) as $col) {
        $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
    }
    
    // Add instructions sheet
    $instructionSheet = $spreadsheet->createSheet();
    $instructionSheet->setTitle('Instructions');
    
    // Add instruction content
    $instructions = [
        ['How to use this template:'],
        ['1. Fill in the required fields (marked with *)'],
        ['2. Keep the data format consistent with the examples'],
        ['3. For dates, use YYYY-MM-DD format (e.g., 2024-02-24)'],
        ['4. Do not delete any columns or change their order'],
        ['5. Save as Excel (.xlsx) or CSV (.csv) when finished'],
        ['6. Import the file from the import page'],
        [''],
        ['Notes:'],
        ["- Customer: Enter a valid customer ID from your system"],
        ["- Container Size: Use '20', '40', or '40HC'"],
        ["- For multiple containers, use the Container Template sheet (if provided)"],
    ];
    
    $row = 1;
    foreach ($instructions as $instruction) {
        $instructionSheet->setCellValueByColumnAndRow(1, $row, $instruction[0]);
        $row++;
    }
    
    // Format instruction sheet
    $instructionSheet->getStyle('A1')->getFont()->setBold(true);
    $instructionSheet->getStyle('A1')->getFont()->setSize(14);
    $instructionSheet->getStyle('A9')->getFont()->setBold(true);
    
    // Set column width
    $instructionSheet->getColumnDimension('A')->setWidth(80);
    
    // Set active sheet to first sheet
    $spreadsheet->setActiveSheetIndex(0);
    
    // Create the writer
    if ($format === 'xlsx') {
        $writer = new Xlsx($spreadsheet);
        $filename = $importType . '_import_template.xlsx';
        $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
    } else {
        $writer = new Csv($spreadsheet);
        $filename = $importType . '_import_template.csv';
        $contentType = 'text/csv';
    }
    
    // Set headers for download
    header('Content-Type: ' . $contentType);
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    
    // Save to output
    $writer->save('php://output');
    exit();
    
} catch (Exception $e) {
    $_SESSION['error_message'] = 'Error generating template: ' . $e->getMessage();
    header('Location: import.php');
    exit();
}
?>