<?php

namespace Database\Seeders;

use App\Models\Department;
use App\Models\Taxe;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class TaxeSeeder extends Seeder
{
    private array $departmentMap = [];

    /**
     * CSV column indices (0-based) from TRACE_REI.csv.
     * Variable numbers in TRACE are 1-based, so index = variable_number - 1.
     */
    private const COL_DEPARTMENT = 0;      // Variable 1: DEP
    private const COL_COMMUNE = 2;         // Variable 3: COM
    private const COL_COMMUNE_NAME = 9;    // Variable 10: LIBCOM
    private const COL_TFPNB_RATE = 12;     // Variable 13: B12 - FNB COMMUNE TAUX NET
    private const COL_TFPNB_AMOUNT = 13;   // Variable 14: B13 - FNB COMMUNE MONTANT REEL
    private const COL_TFPB_RATE = 67;      // Variable 68: E12 - FB COMMUNE TAUX NET
    private const COL_TFPB_AMOUNT = 68;    // Variable 69: E13 - FB COMMUNE MONTANT REEL
    private const COL_TH_RATE = 174;       // Variable 175: H12 - TH COMMUNE TAUX NET
    private const COL_TH_AMOUNT = 175;     // Variable 176: H13 - TH COMMUNE MONTANT REEL
    private const COL_CFE_RATE = 331;      // Variable 332: P12 - CFE COMMUNE TAUX NET
    private const COL_CFE_AMOUNT = 332;    // Variable 333: P13 - CFE COMMUNE PRODUIT REEL NET

    private const CHUNK_SIZE = 500;
    private const DATA_DIR = '/srv/data';

    /**
     * CSV files to import: year => path relative to DATA_DIR.
     * Add new years here as needed.
     */
    private const CSV_FILES = [
        2019 => 'rei-2019/REI_2019.csv',
        2020 => 'rei-2020/REI_2020.csv',
        2021 => 'rei-2021/REI_2021.csv',
        2022 => 'rei-2022/REI_2022.csv',
    ];

    public function run(): void
    {
        $this->departmentMap = Department::pluck('id', 'department_code')->toArray();
        $totalImported = 0;

        foreach (self::CSV_FILES as $year => $relativePath) {
            $csvPath = self::DATA_DIR . '/' . $relativePath;

            if (!file_exists($csvPath)) {
                $this->command->warn("Skipping {$year}: file not found at {$csvPath}");
                continue;
            }

            $count = $this->importYear($year, $csvPath);
            $totalImported += $count;
        }

        if ($totalImported === 0) {
            $this->command->error('No CSV files found. Make sure the data/ directory is mounted in the container.');
        } else {
            $this->command->info("Done. Total: {$totalImported} tax records imported.");
        }
    }

    private function importYear(int $year, string $csvPath): int
    {
        $handle = fopen($csvPath, 'r');
        if ($handle === false) {
            $this->command->error("Cannot open: {$csvPath}");
            return 0;
        }

        $header = fgetcsv($handle, 0, ';');
        if ($header === false) {
            $this->command->error("Empty CSV: {$csvPath}");
            fclose($handle);
            return 0;
        }

        $this->command->info("Importing {$year}...");

        Taxe::query()->where('year', $year)->delete();

        $batch = [];
        $count = 0;

        while (($row = fgetcsv($handle, 0, ';')) !== false) {
            $dep = trim($row[self::COL_DEPARTMENT] ?? '');
            $com = trim($row[self::COL_COMMUNE] ?? '');
            $communeName = trim($row[self::COL_COMMUNE_NAME] ?? '');

            if ($dep === '' || $com === '' || !isset($this->departmentMap[$dep])) {
                continue;
            }

            $communeCode = $dep . $com;
            if (strlen($communeCode) > 5) {
                $communeCode = substr($communeCode, 0, 5);
            }

            $batch[] = [
                'commune_code' => $communeCode,
                'commune_name' => $communeName,
                'department_id' => $this->departmentMap[$dep],
                'tfpnb_amount' => $this->parseDecimal($row[self::COL_TFPNB_AMOUNT] ?? ''),
                'tfpnb_percentage' => $this->parseDecimal($row[self::COL_TFPNB_RATE] ?? ''),
                'tfpb_amount' => $this->parseDecimal($row[self::COL_TFPB_AMOUNT] ?? ''),
                'tfpb_percentage' => $this->parseDecimal($row[self::COL_TFPB_RATE] ?? ''),
                'th_amount' => $this->parseDecimal($row[self::COL_TH_AMOUNT] ?? ''),
                'th_percentage' => $this->parseDecimal($row[self::COL_TH_RATE] ?? ''),
                'cfe_amount' => $this->parseDecimal($row[self::COL_CFE_AMOUNT] ?? ''),
                'cfe_percentage' => $this->parseDecimal($row[self::COL_CFE_RATE] ?? ''),
                'year' => $year,
            ];

            if (count($batch) >= self::CHUNK_SIZE) {
                DB::table('taxes')->insert($batch);
                $count += count($batch);
                $batch = [];
                $this->command->getOutput()->write("\r  {$year}: {$count} rows...");
            }
        }

        if (!empty($batch)) {
            DB::table('taxes')->insert($batch);
            $count += count($batch);
        }

        fclose($handle);

        $this->command->newLine();
        $this->command->info("  {$year}: {$count} records imported.");

        return $count;
    }

    private function parseDecimal(string $value): float
    {
        $value = trim($value);

        if ($value === '' || $value === '-') {
            return 0.0;
        }

        $value = str_replace(',', '.', $value);
        $value = str_replace(' ', '', $value);

        return (float) $value;
    }
}