<?php

namespace App\Services;

use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelImportService
{
    private InvoiceCalculatorService $calculator;

    public function __construct()
    {
        $this->calculator = new InvoiceCalculatorService();
    }

    /**
     * Import invoice from Excel file matching S.A.T.T template
     */
    public function importInvoice(string $filePath): array
    {
        $spreadsheet = IOFactory::load($filePath);
        $worksheet = $spreadsheet->getActiveSheet();

        $invoice = [];
        $lines = [];

        // Extract company info (header)
        $invoice['company_name'] = $worksheet->getCell('A1')->getValue();
        $invoice['company_full'] = $worksheet->getCell('A3')->getValue();
        $invoice['company_address'] = $worksheet->getCell('A4')->getValue();

        // Extract client info
        $clientCode = $this->extractValue($worksheet->getCell('I2')->getValue());
        $invoice['client_code'] = str_replace(': ', '', $clientCode);
        $invoice['client_name'] = str_replace(': ', '', $this->extractValue($worksheet->getCell('I3')->getValue()));
        $invoice['client_address'] = str_replace(': ', '', $this->extractValue($worksheet->getCell('I4')->getValue()));

        // Extract invoice details
        $dateValue = $worksheet->getCell('A9')->getValue();
        $invoice['date'] = $this->parseDate($dateValue);
        $invoice['invoice_number'] = str_replace('FACTURE N° : ', '', $worksheet->getCell('F9')->getValue());

        $invoice['reper_number'] = $worksheet->getCell('F10')->getValue();
        $invoice['designation'] = str_replace(': ', '', $worksheet->getCell('C11')->getValue());
        $invoice['gross_weight'] = $worksheet->getCell('I11')->getValue();
        $invoice['package_count'] = $worksheet->getCell('C12')->getValue();
        $invoice['currency_value'] = $worksheet->getCell('I12')->getValue();
        $invoice['shipper'] = $worksheet->getCell('C13')->getValue();

        // Extract lines (rows 17-41, column A for designation, column J for amount)
        $lineMappings = [
            17 => 'Droit de Douane',
            18 => 'Frais OACA/O.M.M.P',
            19 => 'Frais STAM',
            20 => 'Timbrage - Frêt',
            21 => 'Location Grue',
            22 => 'Tirage',
            23 => 'Assurance',
            24 => 'Chargement Déchargement',
            25 => 'Camionage',
            26 => 'Formalités',
            27 => 'Frais informatique',
            28 => 'Timbre et permis',
            29 => 'Honoraire d'agrée en Douanes',
            30 => 'Répertoire',
            31 => 'Intérêt obligation',
            32 => 'Taxes Fixes',
            33 => 'Visite Douane',
            34 => 'Photocopies',
            35 => 'Vacations',
            36 => 'Consignations',
            37 => 'Frais de surestaries',
            38 => 'Heures supplémentaires',
            39 => 'P.V de dépôts',
            40 => 'P.V d'enlévement',
            41 => 'Autres frais (contrôle technique)',
        ];

        foreach ($lineMappings as $row => $defaultDesignation) {
            $designation = $worksheet->getCell("A{$row}")->getValue() ?? $defaultDesignation;
            $amount = $worksheet->getCell("J{$row}")->getValue();

            if ($amount !== null && $amount != 0) {
                $lines[] = [
                    'designation' => $designation,
                    'quantity' => 1,
                    'unit' => 'U',
                    'unit_price' => (float) $amount,
                    'amount' => (float) $amount,
                    'line_type' => 'frais',
                    'is_taxable' => 0,
                ];
            }
        }

        // Extract totals
        $vatRate = (float) $worksheet->getCell('A44')->getValue();
        $vatBase = (float) $worksheet->getCell('B44')->getValue();
        $stampDuty = (float) $worksheet->getCell('K46')->getValue();
        $advance = (float) ($worksheet->getCell('K48')->getValue() ?? 0);

        // Recalculate to verify
        $calculated = $this->calculator->calculate($lines, $vatRate, 0, $advance, $stampDuty);

        return [
            'invoice' => $invoice,
            'lines' => $lines,
            'calculated' => $calculated,
            'raw_vat_rate' => $vatRate,
            'raw_vat_base' => $vatBase,
            'raw_stamp_duty' => $stampDuty,
        ];
    }

    private function extractValue($value): string
    {
        if (is_string($value)) {
            return trim($value);
        }
        return (string) $value;
    }

    private function parseDate($value): string
    {
        if (is_string($value) && strpos($value, 'Date :') !== false) {
            $dateStr = str_replace('Date : ', '', $value);
            $parts = explode('/', $dateStr);
            if (count($parts) == 3) {
                return "{$parts[2]}-{$parts[1]}-{$parts[0]}";
            }
        }
        return date('Y-m-d');
    }

    /**
     * Map Excel columns to database fields
     */
    public function getColumnMapping(): array
    {
        return [
            'A1' => ['field' => 'company_name', 'type' => 'string'],
            'A3' => ['field' => 'company_full_name', 'type' => 'string'],
            'A4' => ['field' => 'company_address', 'type' => 'string'],
            'A5' => ['field' => 'company_phone', 'type' => 'string'],
            'A6' => ['field' => 'company_email', 'type' => 'string'],
            'A7' => ['field' => 'company_tax_id', 'type' => 'string'],
            'I2' => ['field' => 'client_code', 'type' => 'string'],
            'I3' => ['field' => 'client_name', 'type' => 'string'],
            'I4' => ['field' => 'client_address', 'type' => 'string'],
            'A9' => ['field' => 'invoice_date', 'type' => 'date'],
            'F9' => ['field' => 'invoice_number', 'type' => 'string'],
            'F10' => ['field' => 'reper_number', 'type' => 'string'],
            'C11' => ['field' => 'designation', 'type' => 'string'],
            'I11' => ['field' => 'gross_weight', 'type' => 'decimal'],
            'C12' => ['field' => 'package_count', 'type' => 'integer'],
            'I12' => ['field' => 'currency_value', 'type' => 'decimal'],
            'C13' => ['field' => 'shipper', 'type' => 'string'],
            'A44' => ['field' => 'vat_rate', 'type' => 'decimal'],
            'B44' => ['field' => 'vat_base', 'type' => 'decimal'],
            'K46' => ['field' => 'stamp_duty', 'type' => 'decimal'],
        ];
    }
}
