<?php

namespace App\Models;

use CodeIgniter\Model;
use App\Libraries\Categories;

class DataModel extends Model
{
    protected $returnType = 'array';

    // -----------------------------------------------------------------------
    // Ensure dynamic table exists
    // -----------------------------------------------------------------------

    public function ensureTable(string $tableName, array $fields): void
    {
        if (! preg_match('/^[a-z0-9_]+$/', $tableName)) {
            throw new \RuntimeException("Nama tabel tidak valid: $tableName");
        }
        if ($this->db->tableExists($tableName)) return;

        $cols = '`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY';
        foreach ($fields as $f) {
            $name  = preg_replace('/[^a-z0-9_]/', '_', strtolower($f));
            $cols .= ", `$name` TEXT DEFAULT NULL";
        }
        $this->db->query("CREATE TABLE IF NOT EXISTS `$tableName` ($cols) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    // -----------------------------------------------------------------------
    // Server-side DataTables list
    // -----------------------------------------------------------------------

    public function getList(string $catKey, int $draw, int $start, int $length, string $search, int $orderCol, string $orderDir): array
    {
        $cat = Categories::resolve($catKey);
        if (! $cat) return ['draw'=>$draw,'recordsTotal'=>0,'recordsFiltered'=>0,'data'=>[]];

        $dispCols  = Categories::getDisplayColumns($cat);
        $importCols = Categories::getImportColumns($cat);
        $fields     = array_column($importCols, 'field');

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

        $table = $cat['table'];
        $dispFields = array_column($dispCols, 'field');
        $selectFields = implode(', ', array_map(fn($f) => "`$f`", array_unique(array_merge(['id'], $dispFields))));

        $countTotal = $this->db->query("SELECT COUNT(*) AS c FROM `$table`")->getRow()->c;

        $where = '';
        $binds = [];
        if ($search !== '') {
            $clauses = [];
            foreach ($dispFields as $f) {
                $clauses[] = "`$f` LIKE ?";
                $binds[]   = "%$search%";
            }
            $where = 'WHERE ' . implode(' OR ', $clauses);
        }

        $countFiltered = $this->db->query("SELECT COUNT(*) AS c FROM `$table` $where", $binds)->getRow()->c;

        $orderDir = strtoupper($orderDir) === 'ASC' ? 'ASC' : 'DESC';
        $allFields = array_values(array_unique(array_merge(['id'], $dispFields)));
        $orderField = isset($allFields[$orderCol]) ? '`' . $allFields[$orderCol] . '`' : '`id`';

        $rows = $this->db->query(
            "SELECT $selectFields FROM `$table` $where ORDER BY $orderField $orderDir LIMIT ? OFFSET ?",
            array_merge($binds, [$length, $start])
        )->getResultArray();

        return [
            'draw'            => $draw,
            'recordsTotal'    => (int) $countTotal,
            'recordsFiltered' => (int) $countFiltered,
            'data'            => $rows,
        ];
    }

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

    public function getStats(string $catKey): array
    {
        $cat = Categories::resolve($catKey);
        if (! $cat) return ['total' => 0];

        $fields = array_column(Categories::getImportColumns($cat), 'field');
        $this->ensureTable($cat['table'], $fields);

        $total = $this->db->query("SELECT COUNT(*) AS c FROM `{$cat['table']}`")->getRow()->c;
        return ['total' => (int) $total];
    }

    // -----------------------------------------------------------------------
    // Field stats (for QRIS stat cards)
    // -----------------------------------------------------------------------

    public function getFieldStats(string $catKey, string $field, string $value): int
    {
        $cat = Categories::resolve($catKey);
        if (! $cat) return 0;
        $table = $cat['table'];
        if (! $this->db->tableExists($table)) return 0;

        return (int) $this->db->query(
            "SELECT COUNT(*) AS c FROM `$table` WHERE `$field` = ?", [$value]
        )->getRow()->c;
    }

    // -----------------------------------------------------------------------
    // Insert batch
    // -----------------------------------------------------------------------

    public function insertBatch(string $tableName, array $fields, array $rows): int
    {
        if (empty($rows)) return 0;

        $placeholders = implode(', ', array_fill(0, count($fields), '?'));
        $colNames     = implode(', ', array_map(fn($f) => "`$f`", $fields));
        $sql          = "INSERT INTO `$tableName` ($colNames) VALUES ($placeholders)";

        $count = 0;
        foreach (array_chunk($rows, 500) as $batch) {
            foreach ($batch as $row) {
                $vals = array_map(fn($f) => (string)($row[$f] ?? ''), $fields);
                $this->db->query($sql, $vals);
                $count++;
            }
        }
        return $count;
    }

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

    public function deleteRow(string $catKey, int $id): bool
    {
        $cat = Categories::resolve($catKey);
        if (! $cat) return false;
        return (bool) $this->db->query("DELETE FROM `{$cat['table']}` WHERE id = ?", [$id]);
    }

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

    public function deleteAll(string $catKey): void
    {
        $cat = Categories::resolve($catKey);
        if (! $cat) return;
        $this->db->query("DELETE FROM `{$cat['table']}`");
        try { $this->db->query("ALTER TABLE `{$cat['table']}` AUTO_INCREMENT = 1"); } catch (\Throwable) {}
    }

    // -----------------------------------------------------------------------
    // Update single row
    // -----------------------------------------------------------------------

    public function updateRow(string $catKey, int $id, array $fields): bool
    {
        $cat = Categories::resolve($catKey);
        if (! $cat) return false;

        $allowed  = array_column(Categories::getImportColumns($cat), 'field');
        $allowed  = array_flip($allowed);
        $valid    = [];
        foreach ($fields as $k => $v) {
            if (isset($allowed[$k])) $valid[$k] = (string) $v;
        }
        if (empty($valid)) return false;

        $setClauses = implode(', ', array_map(fn($k) => "`$k` = ?", array_keys($valid)));
        $vals       = array_values($valid);
        $vals[]     = $id;

        return (bool) $this->db->query(
            "UPDATE `{$cat['table']}` SET $setClauses WHERE id = ?", $vals
        );
    }
}
