<?php

declare(strict_types=1);

namespace App\Controllers;

use Core\Request;
use Core\Database;
use PhpOffice\PhpSpreadsheet\IOFactory;

final class ImportController extends BaseController
{
    protected Database $db;

    public function __construct()
    {
        parent::__construct();
        $this->db = Database::getInstance();
    }

    // ── Show import page ──────────────────────────────────────────────────────

    public function index(): void
    {
        $this->requirePermission('import.view');
        $this->view('import.index');
    }

    // ── Download sample templates ─────────────────────────────────────────────

    public function sampleCategories(): void
    {
        $this->requirePermission('import.view');
        $this->sendCsv('categories_sample.csv', [
            ['name', 'description', 'is_active'],
            ['Burgers', 'Delicious burgers', '1'],
            ['Pizza', 'Wood-fired pizza', '1'],
            ['Drinks', 'Cold beverages', '1'],
        ]);
    }

    public function sampleMenu(): void
    {
        $this->requirePermission('import.view');
        $this->sendCsv('menu_items_sample.csv', [
            ['name', 'category_name', 'description', 'price', 'is_active'],
            ['Chicken Burger', 'Burgers', 'Crispy chicken burger', '350', '1'],
            ['Veggie Burger', 'Burgers', 'Fresh veggie patty', '250', '1'],
            ['Margherita Pizza', 'Pizza', 'Classic cheese pizza', '800', '1'],
        ]);
    }

    // ── Import categories ─────────────────────────────────────────────────────

    public function importCategories(): void
    {
        $this->requirePermission('import.view');
        Request::verifyCsrf();

        $file   = $_FILES['file'] ?? null;
        $errors = $this->validateUpload($file);

        if ($errors) {
            $this->flash('error', $errors);
            $this->redirect('/menu');
        }

        try {
            $rows      = $this->readSpreadsheet($file['tmp_name']);
            $header    = array_map('strtolower', array_map('trim', array_shift($rows)));
            $inserted  = 0;
            $skipped   = 0;
            $rowErrors = [];

            foreach ($rows as $i => $row) {
                $data = array_combine($header, array_pad($row, count($header), ''));
                $name = trim($data['name'] ?? '');

                if ($name === '') { $skipped++; continue; }

                // Skip if category already exists
                $exists = $this->db->fetchValue(
                    "SELECT id FROM categories WHERE name = ?", [$name]
                );
                if ($exists) { $skipped++; continue; }

                $this->db->insert('categories', [
                    'name'        => $name,
                    'slug'        => $this->uniqueSlug($name, 'categories'),
                    'is_active'   => ($data['is_active'] ?? '1') === '1' ? 1 : 0,
                    'created_at'  => date('Y-m-d H:i:s'),
                ]);
                $inserted++;
            }

            $msg = "Import complete: $inserted added, $skipped skipped (duplicates/empty).";
            $this->flash('success', $msg);
        } catch (\Throwable $e) {
            $this->flash('error', 'Failed to read file: ' . $e->getMessage());
        }

        $this->redirect('/menu');
    }

    // ── Import menu items ─────────────────────────────────────────────────────

    public function importMenu(): void
    {
        $this->requirePermission('import.view');
        Request::verifyCsrf();

        $file   = $_FILES['file'] ?? null;
        $errors = $this->validateUpload($file);

        if ($errors) {
            $this->flash('error', $errors);
            $this->redirect('/menu');
        }

        try {
            $rows     = $this->readSpreadsheet($file['tmp_name']);
            $header   = array_map('strtolower', array_map('trim', array_shift($rows)));
            $inserted = 0;
            $skipped  = 0;

            // Cache category name → id
            $catMap = [];
            foreach ($this->db->fetchAll("SELECT id, name FROM categories") as $c) {
                $catMap[strtolower($c['name'])] = $c['id'];
            }

            foreach ($rows as $row) {
                $data  = array_combine($header, array_pad($row, count($header), ''));
                $name  = trim($data['name'] ?? '');
                $price = (float) ($data['price'] ?? 0);

                if ($name === '' || $price <= 0) { $skipped++; continue; }

                // Resolve category
                $catName = strtolower(trim($data['category_name'] ?? ''));
                $catId   = $catMap[$catName] ?? null;

                // Auto-create category if not found
                if ($catId === null && $catName !== '') {
                    $catId = $this->db->insert('categories', [
                        'name'       => ucfirst($catName),
                        'slug'       => $this->uniqueSlug($catName, 'categories'),
                        'is_active'  => 1,
                        'created_at' => date('Y-m-d H:i:s'),
                    ]);
                    $catMap[$catName] = $catId;
                }

                // Skip duplicate item names in same category
                $exists = $this->db->fetchValue(
                    "SELECT id FROM menu_items WHERE name = ? AND category_id = ?",
                    [$name, $catId]
                );
                if ($exists) { $skipped++; continue; }

                if ($catId === null) { $skipped++; continue; }   // no category → skip

                $this->db->insert('menu_items', [
                    'category_id' => $catId,
                    'name'        => $name,
                    'slug'        => $this->uniqueSlug($name, 'menu_items'),
                    'description' => trim($data['description'] ?? ''),
                    'base_price'  => $price,
                    'is_available'=> ($data['is_active'] ?? '1') === '1' ? 1 : 0,
                    'created_at'  => date('Y-m-d H:i:s'),
                ]);
                $inserted++;
            }

            $msg = "Import complete: $inserted items added, $skipped skipped.";
            $this->flash('success', $msg);
        } catch (\Throwable $e) {
            $this->flash('error', 'Failed to read file: ' . $e->getMessage());
        }

        $this->redirect('/menu');
    }

    // ── Helpers ───────────────────────────────────────────────────────────────

    /// Build a URL-safe, unique slug for the given table.
    private function uniqueSlug(string $name, string $table): string
    {
        $base = preg_replace('/[^a-z0-9]+/', '-', strtolower(trim($name)));
        $base = trim($base, '-');
        if ($base === '') $base = 'item';
        $slug = $base;
        $n = 1;
        while ($this->db->fetchValue("SELECT id FROM {$table} WHERE slug = ?", [$slug])) {
            $slug = $base . '-' . (++$n);
        }
        return $slug;
    }

    private function readSpreadsheet(string $path): array
    {
        $spreadsheet = IOFactory::load($path);
        $sheet       = $spreadsheet->getActiveSheet();
        $rows        = [];

        foreach ($sheet->getRowIterator() as $row) {
            $cells = [];
            foreach ($row->getCellIterator() as $cell) {
                $cells[] = (string) $cell->getFormattedValue();
            }
            // Skip completely empty rows
            if (count(array_filter($cells, fn($c) => $c !== '')) === 0) continue;
            $rows[] = $cells;
        }

        return $rows;
    }

    private function validateUpload(?array $file): string
    {
        if (!$file || $file['error'] !== UPLOAD_ERR_OK) {
            return 'No file uploaded or upload error.';
        }

        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if (!in_array($ext, ['xlsx', 'xls', 'csv'], true)) {
            return 'Only .xlsx, .xls and .csv files are allowed.';
        }

        if ($file['size'] > 5 * 1024 * 1024) {
            return 'File size must be under 5 MB.';
        }

        return '';
    }

    private function sendCsv(string $filename, array $rows): never
    {
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: no-cache');

        $out = fopen('php://output', 'w');
        foreach ($rows as $row) {
            fputcsv($out, $row);
        }
        fclose($out);
        exit;
    }
}
