<?php

class ExcelImport {
    private $conn;
    private $filePath;
    private $fileType;
    private $importType;
    private $requiredFields;
    private $optionalFields;
    private $data = [];
    private $errors = [];

    public function __construct($conn, $filePath, $importType = 'simple') {
        $this->conn = $conn;
        $this->filePath = $filePath;
        $this->importType = $importType;
        $this->fileType = pathinfo($filePath, PATHINFO_EXTENSION);
        
        $this->requiredFields = [
            'item_name',
            'condition_status',
            'category',
            'unit_price',
            'quantity'
        ];
        
        $this->optionalFields = [
            'unique_code',
            'item_details',
            'warranty_months'
        ];
        
        if ($importType === 'detailed') {
            $this->optionalFields = array_merge($this->optionalFields, [
                'brand',
                'model',
                'serial_number',
                'description',
                'technical_specs'
            ]);
        }
    }

    public function processFile() {
        if (!class_exists('\PhpOffice\PhpSpreadsheet\IOFactory')) {
            $this->errors[] = "PhpSpreadsheet library is not installed. Please run 'composer require phpoffice/phpspreadsheet' to install it.";
            return false;
        }
        
        try {
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($this->filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            
            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            
            $headers = [];
            for ($col = 1; $col <= $highestColumnIndex; $col++) {
                $headers[$col] = strtolower(trim($worksheet->getCellByColumnAndRow($col, 1)->getValue()));
            }
            
            $missingRequiredFields = array_diff($this->requiredFields, $headers);
            if (!empty($missingRequiredFields)) {
                $this->errors[] = "Missing required columns: " . implode(', ', $missingRequiredFields);
                return false;
            }
            
            for ($row = 2; $row <= $highestRow; $row++) {
                $rowData = [];
                $emptyRow = true;
                
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $columnName = $headers[$col];
                    $cellValue = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
                    
                    if ($cellValue !== null && $cellValue !== '') {
                        $emptyRow = false;
                    }
                    
                    $rowData[$columnName] = $cellValue;
                }
                
                if ($emptyRow) {
                    continue;
                }
                
                $valid = true;
                foreach ($this->requiredFields as $field) {
                    if (!isset($rowData[$field]) || $rowData[$field] === '' || $rowData[$field] === null) {
                        $this->errors[] = "Row {$row}: Missing required field '{$field}'";
                        $valid = false;
                    }
                }
                
                if (isset($rowData['unit_price']) && !is_numeric($rowData['unit_price'])) {
                    $this->errors[] = "Row {$row}: Unit price must be a number";
                    $valid = false;
                }
                
                if (isset($rowData['quantity']) && (!is_numeric($rowData['quantity']) || $rowData['quantity'] <= 0)) {
                    $this->errors[] = "Row {$row}: Quantity must be a positive number";
                    $valid = false;
                }
                
                if (isset($rowData['warranty_months']) && $rowData['warranty_months'] !== '' && !is_numeric($rowData['warranty_months'])) {
                    $this->errors[] = "Row {$row}: Warranty months must be a number";
                    $valid = false;
                }
                
                if ($valid) {
                    $this->data[] = $rowData;
                }
            }
            
            if (empty($this->data)) {
                $this->errors[] = "No valid data found in the Excel file";
                return false;
            }
            
            return true;
            
        } catch (\Exception $e) {
            $this->errors[] = "Error processing Excel file: " . $e->getMessage();
            return false;
        }
    }

    public function validateUniqueCodes() {
        $uniqueCodes = [];
        $existingCodes = [];
        
        foreach ($this->data as $index => $item) {
            if (isset($item['unique_code']) && $item['unique_code'] !== '') {
                $code = $item['unique_code'];
                
                if (in_array($code, $uniqueCodes)) {
                    $this->errors[] = "Duplicate unique code '{$code}' found in the Excel file";
                    return false;
                }
                
                $uniqueCodes[] = $code;
            }
        }
        
        if (!empty($uniqueCodes)) {
            $placeholders = implode(',', array_fill(0, count($uniqueCodes), '?'));
            $stmt = $this->conn->prepare("SELECT unique_code FROM items WHERE unique_code IN ({$placeholders})");
            $stmt->execute($uniqueCodes);
            $existingCodes = $stmt->fetchAll(PDO::FETCH_COLUMN);
            
            if (!empty($existingCodes)) {
                $this->errors[] = "The following unique codes already exist in the database: " . implode(', ', $existingCodes);
                return false;
            }
        }
        
        return true;
    }

    public function getData() {
        return $this->data;
    }

    public function getErrors() {
        return $this->errors;
    }

    public function formatDataForImport() {
        $formattedData = [];
        
        foreach ($this->data as $item) {
            $quantity = isset($item['quantity']) ? (int)$item['quantity'] : 1;
            
            $itemData = [
                'item_name' => $item['item_name'],
                'item_details' => trim((string)($item['item_details'] ?? '')),
                'condition_status' => $item['condition_status'],
                'category' => $item['category'],
                'unit_price' => $item['unit_price'],
                'import_type' => $this->importType
            ];
            
            if (isset($item['unique_code']) && $item['unique_code'] !== '') {
                $itemData['unique_code'] = $item['unique_code'];
            }
            
            if (isset($item['warranty_months']) && $item['warranty_months'] !== '') {
                $itemData['warranty_months'] = $item['warranty_months'];
            }
            
            if ($this->importType === 'detailed') {
                foreach (['brand', 'model', 'serial_number', 'description', 'technical_specs'] as $field) {
                    if (isset($item[$field]) && $item[$field] !== '') {
                        $itemData[$field] = $item[$field];
                    }
                }
            }
            
            for ($i = 0; $i < $quantity; $i++) {
                $formattedData[] = $itemData;
            }
        }
        
        return $formattedData;
    }
}
?>