<?php

// Load required dependencies
require_once APP_ROOT . '/helpers/Env.php';
require_once APP_ROOT . '/config/app.php';
require_once APP_ROOT . '/config/database.php';
require_once APP_ROOT . '/models/Item.php';
require_once APP_ROOT . '/models/Coin.php';
require_once APP_ROOT . '/models/Subcollection.php';

// Check if PhpSpreadsheet is available
if (!class_exists('PhpOffice\PhpSpreadsheet\Spreadsheet')) {
    // Try to autoload PhpSpreadsheet
    $autoloadPath = APP_ROOT . '/vendor/autoload.php';
    if (file_exists($autoloadPath)) {
        require_once $autoloadPath;
    } else {
        throw new Exception('PhpSpreadsheet not found. Please run: composer require phpoffice/phpspreadsheet');
    }
}

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelHelper
{
    private Item $itemModel;
    private Coin $coinModel;
    private Subcollection $subcollectionModel;

    public function __construct()
    {
        $this->itemModel = new Item();
        $this->coinModel = new Coin();
        $this->subcollectionModel = new Subcollection();
    }

    public function generateSampleFile(string $type): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        if ($type === 'coin') {
            $this->generateCoinSample($sheet);
        } else {
            $this->generateBanknoteSample($sheet);
        }

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

    private function generateCoinSample($sheet): void
    {
        $headers = [
            'Subcollection Name',
            'Country',
            'Year',
            'Value',
            'Catalog Number',
            'Material',
            'Alloy Details',
            'Weight',
            'Diameter',
            'Thickness',
            'Orientation',
            'Obverse Description',
            'Reverse Description',
            'Edge',
            'Mintage',
            'Condition',
            'Grade',
            'Price Paid',
            'Current Value',
            'Notes',
            'Origin'
        ];

        $sheet->fromArray($headers, null, 'A1');
        
        // Style the header row
        $sheet->getStyle('A1:Z1')->getFont()->setBold(true);
        $sheet->getStyle('A1:Z1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6E6FA');
        
        // Set column widths
        foreach (range('A', 'Z') as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }

        // Add sample data
        $sampleData = [
            ['Example Subcollection', 'United States', '2023', '1', '12345', 'Copper-Nickel', 'Cu-Ni 25%', '8.1', '26.5', '1.75', 'coin', 'Washington Portrait', 'Eagle', 'Reeded', '1000000', 'Uncirculated', 'MS-65', '1.00', '1.50', 'Sample note', 'Purchase']
        ];
        
        $sheet->fromArray($sampleData, null, 'A2');
    }

    private function generateBanknoteSample($sheet): void
    {
        $headers = [
            'Subcollection Name',
            'Country',
            'Year',
            'Denomination',
            'Dimensions',
            'Color',
            'Obverse Description',
            'Reverse Description',
            'Issue Date',
            'Additional Info',
            'Catalog Number',
            'Price Paid',
            'Notes'
        ];

        $sheet->fromArray($headers, null, 'A1');
        
        // Style the header row
        $sheet->getStyle('A1:T1')->getFont()->setBold(true);
        $sheet->getStyle('A1:T1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6E6FA');
        
        // Set column widths
        foreach (range('A', 'T') as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }

        // Add sample data
        $sampleData = [
            ['Example Subcollection', 'United States', '2021', '1', '156 x 66 mm', 'Green', 'Portrait of Washington', 'Great Seal of US', '2021', 'Federal Reserve Note', '12345', '1.00', 'Sample note']
        ];
        
        $sheet->fromArray($sampleData, null, 'A2');
    }

    public function importFromFile(string $filePath, int $collectionId, string $type, ?int $subcollectionId = null): array
    {
        try {
            // Check if file exists and is readable
            if (!file_exists($filePath)) {
                return ['success' => false, 'message' => 'Uploaded file not found', 'errors' => []];
            }
            
            if (!is_readable($filePath)) {
                return ['success' => false, 'message' => 'Uploaded file is not readable', 'errors' => []];
            }
            
            // Check file size
            $fileSize = filesize($filePath);
            if ($fileSize === 0) {
                return ['success' => false, 'message' => 'Uploaded file is empty', 'errors' => []];
            }
            
            $spreadsheet = IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            if (empty($data) || count($data) < 2) {
                return ['success' => false, 'message' => 'File is empty or invalid'];
            }

            $headers = array_shift($data); // Remove header row
            $importedCount = 0;
            $skippedCount = 0;
            $errors = [];

            // Get all subcollections for this collection
            $subcollections = $this->subcollectionModel->getAllByCollection($collectionId);
            $subcollectionMap = [];
            foreach ($subcollections as $sub) {
                $subcollectionMap[strtolower(trim($sub['name']))] = $sub['id'];
            }

            foreach ($data as $rowIndex => $row) {
                if ($this->isEmptyRow($row)) {
                    $skippedCount++;
                    continue;
                }

                try {
                    // Pass subcollectionMap by reference using &
                    $itemData = $this->parseRow($row, $headers, $type, $subcollectionMap, $collectionId, $subcollectionId);
                    
                    if ($itemData) {
                        if ($type === 'coin') {
                            $this->coinModel->create($itemData);
                        } else {
                            $this->itemModel->create($itemData);
                        }
                        $importedCount++;
                    } else {
                        $skippedCount++;
                        $errors[] = "Row " . ($rowIndex + 2) . ": Invalid data format";
                    }
                } catch (Exception $e) {
                    $skippedCount++;
                    $errors[] = "Row " . ($rowIndex + 2) . ": " . $e->getMessage();
                }
            }

            return [
                'success' => true,
                'message' => 'Import completed successfully',
                'imported_count' => $importedCount,
                'skipped_count' => $skippedCount,
                'errors' => $errors
            ];

        } catch (Exception $e) {
            return ['success' => false, 'message' => 'Error reading file: ' . $e->getMessage(), 'errors' => []];
        }
    }

    private function isEmptyRow(array $row): bool
    {
        foreach ($row as $cell) {
            if (!empty(trim($cell))) {
                return false;
            }
        }
        return true;
    }

    private function parseRow(array $row, array $headers, string $type, array &$subcollectionMap, int $collectionId, ?int $defaultSubcollectionId = null): ?array
    {
        $data = array_combine($headers, $row);
        
        // Use provided subcollection ID or find/create from data
        $subcollectionId = $defaultSubcollectionId;
        if ($subcollectionId === null && !empty($data['Subcollection Name'])) {
            $subName = trim($data['Subcollection Name']);
            $subNameLower = strtolower($subName);
            
            if (isset($subcollectionMap[$subNameLower])) {
                $subcollectionId = $subcollectionMap[$subNameLower];
            } else {
                // Create new subcollection
                $subcollectionId = $this->subcollectionModel->create([
                    'collection_id' => $collectionId,
                    'name' => $subName,
                    'description' => '',
                    'sort_order' => 999
                ]);
                $subcollectionMap[$subNameLower] = $subcollectionId;
            }
        }

        if ($type === 'coin') {
            $itemData = [
                'subcollection_id' => $subcollectionId,
                'country' => trim($data['Country'] ?? ''),
                'year_minted' => trim($data['Year'] ?? ''),
                'nominal_value' => $this->parseDecimal($data['Value'] ?? 0),
                'catalog_number' => trim($data['Catalog Number'] ?? ''),
                'material' => trim($data['Material'] ?? ''),
                'alloy_details' => trim($data['Alloy Details'] ?? ''),
                'weight' => $this->parseDecimal($data['Weight'] ?? 0),
                'diameter' => $this->parseDecimal($data['Diameter'] ?? 0),
                'thickness' => $this->parseDecimal($data['Thickness'] ?? 0),
                'orientation' => $this->parseOrientation($data['Orientation'] ?? ''),
                'description_obverse' => trim($data['Obverse Description'] ?? ''),
                'description_reverse' => trim($data['Reverse Description'] ?? ''),
                'legends' => trim($data['Edge'] ?? ''),
                'mintage' => $this->parseInt($data['Mintage'] ?? 0),
                'purchase_price' => $this->parseDecimal($data['Price Paid'] ?? 0),
                'current_value' => $this->parseDecimal($data['Current Value'] ?? 0),
                'additional_info' => trim($data['Notes'] ?? ''),
                'note' => trim($data['Notes'] ?? ''),
                'origin' => trim($data['Origin'] ?? ''),
                'owned' => 1,
            ];
        } else {
            $itemData = [
                'subcollection_id' => $subcollectionId,
                'country' => trim($data['Country'] ?? ''),
                'nominal_value' => $this->parseDecimal($data['Denomination'] ?? 0),
                'dimensions' => trim($data['Dimensions'] ?? ''),
                'color' => trim($data['Color'] ?? ''),
                'description_obverse' => trim($data['Obverse Description'] ?? ''),
                'description_reverse' => trim($data['Reverse Description'] ?? ''),
                'issue_date' => trim($data['Year'] ?? ''),
                'additional_info' => trim($data['Additional Info'] ?? ''),
                'catalog_number' => trim($data['Catalog Number'] ?? ''),
                'purchase_price' => $this->parseDecimal($data['Price Paid'] ?? 0),
                'note' => trim($data['Notes'] ?? ''),
            ];
        }

        return $itemData;
    }

    private function parseDecimal($value): float
    {
        if (empty($value)) return 0;
        return (float)preg_replace('/[^0-9.]/', '', $value);
    }

    private function parseInt($value): int
    {
        if (empty($value)) return 0;
        return (int)preg_replace('/[^0-9]/', '', $value);
    }

    private function parseOrientation($value): string
    {
        if (empty($value)) return '';
        
        $value = strtolower(trim($value));
        
        // Map common orientation descriptions to ENUM values
        $orientationMap = [
            'coin' => 'coin',
            'coin alignment' => 'coin',
            'coin orientation' => 'coin',
            'medal' => 'medal',
            'medal alignment' => 'medal',
            'medal orientation' => 'medal',
            'reverse' => 'medal', // Common term for medal alignment
            'obverse' => 'coin', // Common term for coin alignment
        ];
        
        return $orientationMap[$value] ?? '';
    }
}