<?php

namespace App\Controllers;

use App\Libraries\Categories;
use App\Models\DataModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Dashboard extends BaseController
{
    private DataModel $dataModel;

    public function __construct()
    {
        $this->dataModel = new DataModel();
    }

    // -----------------------------------------------------------------------
    // Main dashboard page
    // -----------------------------------------------------------------------

    public function index(): string
    {
        $catKey   = $this->request->getGet('cat') ?? 'edc';
        $cat      = Categories::resolve($catKey);
        if (! $cat || $cat['hidden']) {
            $catKey = 'edc';
            $cat    = Categories::resolve('edc');
        }

        $dualMap  = Categories::getDualMap();
        $isDual   = isset($dualMap[$catKey]);
        $subKeys  = $isDual ? $dualMap[$catKey] : [];

        $navCats  = Categories::getNavCategories();

        return view('dashboard/index', $this->viewData([
            'catKey'   => $catKey,
            'cat'      => $cat,
            'isDual'   => $isDual,
            'subKeys'  => $subKeys,
            'navCats'  => $navCats,
            'dispCols' => Categories::getDisplayColumns($cat),
            'isAdmin'  => $this->isAdmin(),
        ]));
    }

    // -----------------------------------------------------------------------
    // DataTables server-side list
    // -----------------------------------------------------------------------

    public function listData(): \CodeIgniter\HTTP\ResponseInterface
    {
        $catKey   = $this->request->getPost('cat') ?? 'edc';
        $draw     = (int) ($this->request->getPost('draw') ?? 1);
        $start    = (int) ($this->request->getPost('start') ?? 0);
        $length   = (int) ($this->request->getPost('length') ?? 25);
        $search   = trim($this->request->getPost('search[value]') ?? '');
        $orderCol = (int) ($this->request->getPost('order[0][column]') ?? 0);
        $orderDir = $this->request->getPost('order[0][dir]') ?? 'asc';

        $result = $this->dataModel->getList($catKey, $draw, $start, $length, $search, $orderCol, $orderDir);
        return $this->response->setJSON($result);
    }

    // -----------------------------------------------------------------------
    // Stats (row count)
    // -----------------------------------------------------------------------

    public function stats(): \CodeIgniter\HTTP\ResponseInterface
    {
        $catKey = $this->request->getGet('cat') ?? 'edc';
        $stats  = $this->dataModel->getStats($catKey);
        return $this->response->setJSON(['success' => true, 'data' => $stats]);
    }

    // -----------------------------------------------------------------------
    // Field stats (status cards for QRIS/EDC)
    // -----------------------------------------------------------------------

    public function fieldStats(): \CodeIgniter\HTTP\ResponseInterface
    {
        $catKey = $this->request->getGet('cat') ?? 'edc';
        $field  = $this->request->getGet('field') ?? '';
        $value  = $this->request->getGet('value') ?? '';

        if ($field === '') return $this->jsonError('Field tidak boleh kosong.');

        $count = $this->dataModel->getFieldStats($catKey, $field, $value);
        return $this->response->setJSON(['success' => true, 'count' => $count]);
    }

    // -----------------------------------------------------------------------
    // Import Excel
    // -----------------------------------------------------------------------

    public function import(): \CodeIgniter\HTTP\ResponseInterface
    {
        $catKey = $this->request->getPost('cat') ?? '';
        $cat    = Categories::resolve($catKey);
        if (! $cat) return $this->jsonError('Kategori tidak valid.');

        $file = $this->request->getFile('file');
        if (! $file || ! $file->isValid()) {
            return $this->jsonError('File tidak valid atau tidak ditemukan.');
        }
        if ($file->getClientExtension() !== 'xlsx') {
            return $this->jsonError('Hanya file .xlsx yang didukung.');
        }

        $tempPath = $file->getTempName();
        $importCols = Categories::getImportColumns($cat);
        $fields     = array_column($importCols, 'field');
        $labels     = array_column($importCols, 'label');

        // Ensure table exists
        $this->dataModel->ensureTable($cat['table'], $fields);

        try {
            $spreadsheet = IOFactory::load($tempPath);
            $sheet       = $spreadsheet->getActiveSheet();
            $headerRow   = $cat['header_row'] ?? 1;
            $maxRow      = $sheet->getHighestRow();
            $maxCol      = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($sheet->getHighestColumn());

            // Read header row → map column indices to field names
            $colMap = [];
            for ($c = 1; $c <= $maxCol; $c++) {
                $headerVal = strtolower(trim((string) $sheet->getCellByColumnAndRow($c, $headerRow)->getValue()));
                $headerVal = preg_replace('/\s+/', '_', $headerVal);
                // Match by normalized label or field name
                foreach ($importCols as $idx => $colDef) {
                    $normalLabel = strtolower(preg_replace('/\s+/', '_', $colDef['label']));
                    $normalField = strtolower($colDef['field']);
                    if ($headerVal === $normalField || $headerVal === $normalLabel) {
                        $colMap[$c] = $colDef['field'];
                        break;
                    }
                }
            }

            // Fallback: if no column header matched at all, use positional mapping
            if (empty($colMap)) {
                for ($c = 1; $c <= min($maxCol, count($fields)); $c++) {
                    $colMap[$c] = $fields[$c - 1];
                }
            }

            $rows     = [];
            $dataRow  = $headerRow + 1;
            for ($r = $dataRow; $r <= $maxRow; $r++) {
                $row = [];
                $hasData = false;
                foreach ($colMap as $c => $field) {
                    $val = $sheet->getCellByColumnAndRow($c, $r)->getFormattedValue();
                    $row[$field] = (string) $val;
                    if ($val !== '' && $val !== null) $hasData = true;
                }
                if ($hasData) $rows[] = $row;
            }

            $imported = $this->dataModel->insertBatch($cat['table'], $fields, $rows);

            $this->logActivity(
                'import',
                "Import {$imported} baris ke kategori {$cat['label']} ({$cat['table']})"
            );

            return $this->jsonSuccess("Berhasil mengimpor $imported baris data.", ['imported' => $imported]);

        } catch (\Throwable $e) {
            return $this->jsonError('Gagal membaca file Excel: ' . $e->getMessage());
        }
    }

    // -----------------------------------------------------------------------
    // Export Excel
    // -----------------------------------------------------------------------

    public function export(): void
    {
        $catKey = $this->request->getGet('cat') ?? 'edc';
        $cat    = Categories::resolve($catKey);
        if (! $cat) {
            die('Kategori tidak valid.');
        }

        $importCols = Categories::getImportColumns($cat);
        $fields     = array_column($importCols, 'field');
        $labels     = array_column($importCols, 'label');

        $this->dataModel->ensureTable($cat['table'], $fields);

        $selectFields = implode(', ', array_map(fn($f) => "`$f`", $fields));
        $db   = \Config\Database::connect();
        $rows = $db->query("SELECT $selectFields FROM `{$cat['table']}` ORDER BY id ASC")->getResultArray();

        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->setTitle($cat['label']);

        // Header row (bold)
        foreach ($labels as $i => $label) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i + 1);
            $sheet->setCellValue("{$col}1", $label);
            $sheet->getStyle("{$col}1")->getFont()->setBold(true);
        }

        // Data rows
        foreach ($rows as $ri => $row) {
            foreach ($fields as $ci => $field) {
                $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($ci + 1);
                $sheet->setCellValueExplicit(
                    "{$col}" . ($ri + 2),
                    $row[$field] ?? '',
                    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
                );
            }
        }

        // Auto-size first 10 columns
        for ($i = 1; $i <= min(10, count($fields)); $i++) {
            $sheet->getColumnDimensionByColumn($i)->setAutoSize(true);
        }

        $filename = 'export_' . $cat['key'] . '_' . date('YmdHis') . '.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = new XlsxWriter($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    // -----------------------------------------------------------------------
    // Delete row
    // -----------------------------------------------------------------------

    public function deleteRow(): \CodeIgniter\HTTP\ResponseInterface
    {
        $catKey = $this->request->getPost('cat') ?? '';
        $id     = (int) ($this->request->getPost('id') ?? 0);

        if (! $catKey || $id < 1) return $this->jsonError('Parameter tidak valid.');

        $cat = Categories::resolve($catKey);
        if (! $cat) return $this->jsonError('Kategori tidak valid.');

        if (! $cat['row_editable'] && Categories::isReadonly($cat)) {
            return $this->jsonError('Data ini tidak dapat dihapus baris per baris. Gunakan fitur import ulang.');
        }

        $this->dataModel->deleteRow($catKey, $id);
        $this->logActivity('delete_row', "Hapus baris id=$id dari {$cat['table']}");

        return $this->jsonSuccess('Baris berhasil dihapus.');
    }

    // -----------------------------------------------------------------------
    // Delete all
    // -----------------------------------------------------------------------

    public function deleteAll(): \CodeIgniter\HTTP\ResponseInterface
    {
        $catKey = $this->request->getPost('cat') ?? '';
        $cat    = Categories::resolve($catKey);
        if (! $cat) return $this->jsonError('Kategori tidak valid.');

        $this->dataModel->deleteAll($catKey);
        $this->logActivity('delete_all', "Hapus semua data dari {$cat['table']}");

        return $this->jsonSuccess('Semua data berhasil dihapus.');
    }

    // -----------------------------------------------------------------------
    // Update row (for editable dual tables)
    // -----------------------------------------------------------------------

    public function updateRow(): \CodeIgniter\HTTP\ResponseInterface
    {
        $catKey = $this->request->getPost('cat') ?? '';
        $id     = (int) ($this->request->getPost('id') ?? 0);
        $cat    = Categories::resolve($catKey);

        if (! $cat) return $this->jsonError('Kategori tidak valid.');
        if (! $cat['row_editable']) return $this->jsonError('Kategori ini tidak mendukung edit baris.');
        if ($id < 1) return $this->jsonError('ID tidak valid.');

        $fields = $this->request->getPost('fields') ?? [];
        if (! is_array($fields) || empty($fields)) {
            return $this->jsonError('Tidak ada data yang dikirim.');
        }

        $ok = $this->dataModel->updateRow($catKey, $id, $fields);
        if (! $ok) return $this->jsonError('Gagal memperbarui baris.');

        $this->logActivity('update_row', "Update baris id=$id di {$cat['table']}");

        return $this->jsonSuccess('Baris berhasil diperbarui.');
    }
}
