<?php

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

/**
 * Excel Service
 * Handles Excel file import/export for contacts
 */
class ExcelService {
    
    private $uploadPath;
    private $batchSize;
    
    public function __construct() {
        $this->uploadPath = EXCEL_UPLOAD_PATH;
        $this->batchSize = EXCEL_BATCH_SIZE;
        
        // Create upload directory if it doesn't exist
        if (!is_dir($this->uploadPath)) {
            mkdir($this->uploadPath, 0755, true);
        }
    }
    
    /**
     * Import contacts from Excel file
     * 
     * @param array $file Uploaded file from $_FILES
     * @param int $userId User ID to assign contacts to
     * @return array Result with success/error counts and messages
     */
    public function importContacts($file, $userId) {
        $result = [
            'success' => false,
            'total_rows' => 0,
            'imported' => 0,
            'skipped' => 0,
            'errors' => []
        ];
        
        // Validate file
        if (!$this->validateExcelFile($file)) {
            $result['errors'][] = 'Invalid Excel file. Please upload a valid .xlsx, .xls, or .csv file.';
            return $result;
        }
        
        try {
            // Load spreadsheet
            $spreadsheet = IOFactory::load($file['tmp_name']);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();
            
            // Check if file has data
            if (count($rows) < 2) {
                $result['errors'][] = 'Excel file is empty or has no data rows.';
                return $result;
            }
            
            // Validate headers
            $headers = array_map('strtolower', array_map('trim', $rows[0]));
            $requiredHeaders = ['full_name', 'contact_number', 'date_of_birth'];
            
            foreach ($requiredHeaders as $required) {
                if (!in_array($required, $headers)) {
                    $result['errors'][] = "Missing required column: $required";
                    return $result;
                }
            }
            
            // Get column indexes
            $nameIndex = array_search('full_name', $headers);
            $phoneIndex = array_search('contact_number', $headers);
            $emailIndex = array_search('email', $headers);
            $dobIndex = array_search('date_of_birth', $headers);
            $noteIndex = array_search('note', $headers);
            
            // Process data rows
            $contactModel = new Contact();
            $result['total_rows'] = count($rows) - 1; // Exclude header
            
            for ($i = 1; $i < count($rows); $i++) {
                $row = $rows[$i];
                $rowNumber = $i + 1;
                
                // Skip empty rows
                if (empty($row[$nameIndex]) && empty($row[$phoneIndex])) {
                    continue;
                }
                
                // Validate required fields
                if (empty($row[$nameIndex])) {
                    $result['errors'][] = "Row $rowNumber: Full name is required";
                    $result['skipped']++;
                    continue;
                }
                
                if (empty($row[$phoneIndex])) {
                    $result['errors'][] = "Row $rowNumber: Contact number is required";
                    $result['skipped']++;
                    continue;
                }
                
                if (empty($row[$dobIndex])) {
                    $result['errors'][] = "Row $rowNumber: Date of birth is required";
                    $result['skipped']++;
                    continue;
                }
                
                // Validate phone number
                $phone = preg_replace('/[^0-9]/', '', $row[$phoneIndex]);
                if (!$contactModel->validatePhoneNumber($phone)) {
                    $result['errors'][] = "Row $rowNumber: Invalid phone number (must be exactly 10 digits)";
                    $result['skipped']++;
                    continue;
                }
                
                // Parse date of birth
                $dob = $this->parseDate($row[$dobIndex]);
                if (!$dob) {
                    $result['errors'][] = "Row $rowNumber: Invalid date format (use YYYY-MM-DD or DD-MM-YYYY)";
                    $result['skipped']++;
                    continue;
                }
                
                // Check for duplicate
                if ($contactModel->checkDuplicate($userId, $phone)) {
                    $result['errors'][] = "Row $rowNumber: Duplicate contact number ($phone)";
                    $result['skipped']++;
                    continue;
                }
                
                // Prepare contact data
                $contactData = [
                    'user_id' => $userId,
                    'full_name' => trim($row[$nameIndex]),
                    'contact_number' => $phone,
                    'email' => $emailIndex !== false ? trim($row[$emailIndex]) : '',
                    'date_of_birth' => $dob,
                    'note' => $noteIndex !== false ? trim($row[$noteIndex]) : '',
                    'status' => 'active'
                ];
                
                // Create contact
                $contactId = $contactModel->create($contactData);
                
                if ($contactId) {
                    $result['imported']++;
                } else {
                    $result['errors'][] = "Row $rowNumber: Failed to create contact";
                    $result['skipped']++;
                }
            }
            
            $result['success'] = $result['imported'] > 0;
            
        } catch (Exception $e) {
            $result['errors'][] = 'Error reading Excel file: ' . $e->getMessage();
            logError('Excel import failed', ['error' => $e->getMessage()]);
        }
        
        return $result;
    }
    

    public function exportContacts($userId = null) {
        $result = [
            'success' => false,
            'file' => null,
            'message' => null
        ];
        
        try {
            $contactModel = new Contact();
            
            // Get contacts
            if ($userId) {
                $contacts = $contactModel->getByUser($userId);
            } else {
                $contacts = $contactModel->getAll();
            }
            
            if (empty($contacts)) {
                $result['message'] = 'No contacts found to export';
                return $result;
            }
            
            // Generate filename
            $filename = 'contacts_export_' . ($userId ? 'user_' . $userId . '_' : '') . date('Y-m-d_His') . '.xlsx';
            $filePath = $this->uploadPath . '/' . $filename;
            
            // Create spreadsheet
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            
            // Set headers
            $headers = ['Full Name', 'Contact Number', 'Email', 'Date of Birth', 'Note', 'Status', 'Username'];
            $sheet->fromArray($headers, null, 'A1');
            
            // Style headers
            $headerStyle = [
                'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
            ];
            $sheet->getStyle('A1:G1')->applyFromArray($headerStyle);
            
            // Set column widths
            $sheet->getColumnDimension('A')->setWidth(25);
            $sheet->getColumnDimension('B')->setWidth(15);
            $sheet->getColumnDimension('C')->setWidth(30);
            $sheet->getColumnDimension('D')->setWidth(15);
            $sheet->getColumnDimension('E')->setWidth(30);
            $sheet->getColumnDimension('F')->setWidth(10);
            $sheet->getColumnDimension('G')->setWidth(15);
            
            // Add data rows
            $rowNumber = 2;
            foreach ($contacts as $contact) {
                $sheet->setCellValue('A' . $rowNumber, $contact['full_name']);
                $sheet->setCellValue('B' . $rowNumber, $contact['contact_number']);
                $sheet->setCellValue('C' . $rowNumber, $contact['email'] ?? '');
                $sheet->setCellValue('D' . $rowNumber, $contact['date_of_birth']);
                $sheet->setCellValue('E' . $rowNumber, $contact['note'] ?? '');
                $sheet->setCellValue('F' . $rowNumber, $contact['status']);
                $sheet->setCellValue('G' . $rowNumber, $contact['username'] ?? '');
                $rowNumber++;
            }
            
            // Add borders to data
            $lastRow = $rowNumber - 1;
            $sheet->getStyle('A1:G' . $lastRow)->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
            ]);
            
            // Save file
            $writer = new Xlsx($spreadsheet);
            $writer->save($filePath);
            
            $result['success'] = true;
            $result['file'] = $filePath;
            $result['message'] = 'Export completed successfully';
            
        } catch (Exception $e) {
            $result['message'] = 'Error creating Excel file: ' . $e->getMessage();
            logError('Excel export failed', ['error' => $e->getMessage()]);
        }
        
        return $result;
    }
    
    /**
     * Validate Excel file
     * 
     * @param array $file
     * @return bool
     */
    private function validateExcelFile($file) {
        // Check for upload errors
        if (!isset($file['error']) || $file['error'] !== UPLOAD_ERR_OK) {
            return false;
        }
        
        // Check file size
        if ($file['size'] > MAX_EXCEL_SIZE) {
            return false;
        }
        
        // Check file extension
        $allowedExtensions = ['xlsx', 'xls', 'csv'];
        $extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        
        if (!in_array($extension, $allowedExtensions)) {
            return false;
        }
        
        return true;
    }
    
    /**
     * Parse date from various formats
     * 
     * @param string $dateString
     * @return string|false Date in Y-m-d format or false
     */
    private function parseDate($dateString) {
        if (empty($dateString)) {
            return false;
        }
        
        // Try different date formats
        $formats = [
            'Y-m-d',
            'd-m-Y',
            'd/m/Y',
            'm/d/Y',
            'Y/m/d',
            'd.m.Y'
        ];
        
        foreach ($formats as $format) {
            $date = DateTime::createFromFormat($format, $dateString);
            if ($date && $date->format($format) === $dateString) {
                return $date->format('Y-m-d');
            }
        }
        
        // Try strtotime as fallback
        $timestamp = strtotime($dateString);
        if ($timestamp !== false) {
            return date('Y-m-d', $timestamp);
        }
        
        return false;
    }
    
    /**
     * Delete Excel file
     * 
     * @param string $filename
     * @return bool
     */
    public function deleteFile($filename) {
        $filePath = $this->uploadPath . '/' . $filename;
        
        if (file_exists($filePath)) {
            return unlink($filePath);
        }
        
        return false;
    }
    
    /**
     * Get file path
     * 
     * @param string $filename
     * @return string
     */
    public function getFilePath($filename) {
        return $this->uploadPath . '/' . $filename;
    }
    
    /**
     * Check if file exists
     * 
     * @param string $filename
     * @return bool
     */
    public function fileExists($filename) {
        return file_exists($this->uploadPath . '/' . $filename);
    }
    
    /**
     * Create sample Excel template
     * 
     * @return string|false Template file path or false on failure
     */
    public function createSampleTemplate() {
        try {
            $filename = 'contacts_import_template.xlsx';
            $filePath = $this->uploadPath . '/' . $filename;
            
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            
            // Set headers
            $headers = ['full_name', 'contact_number', 'email', 'date_of_birth', 'note'];
            $sheet->fromArray($headers, null, 'A1');
            
            // Style headers
            $headerStyle = [
                'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
            ];
            $sheet->getStyle('A1:E1')->applyFromArray($headerStyle);
            
            // Add sample data
            $sampleData = [
                ['John Doe', '9876543210', 'john@example.com', '1990-01-15', 'Friend from college'],
                ['Jane Smith', '9876543211', 'jane@example.com', '1985-05-20', 'Colleague'],
                ['Bob Johnson', '9876543212', 'bob@example.com', '1992-12-10', 'Family member']
            ];
            $sheet->fromArray($sampleData, null, 'A2');
            
            // Set column widths
            $sheet->getColumnDimension('A')->setWidth(20);
            $sheet->getColumnDimension('B')->setWidth(15);
            $sheet->getColumnDimension('C')->setWidth(25);
            $sheet->getColumnDimension('D')->setWidth(15);
            $sheet->getColumnDimension('E')->setWidth(30);
            
            // Save file
            $writer = new Xlsx($spreadsheet);
            $writer->save($filePath);
            
            return $filePath;
            
        } catch (Exception $e) {
            logError('Failed to create sample template', ['error' => $e->getMessage()]);
            return false;
        }
    }
}
