<?php
namespace App\Core;

/** Model dasar dengan soft delete, paginasi, dan pencarian. */
abstract class Model
{
    protected string $table = '';
    protected string $primaryKey = 'id';
    protected bool $softDelete = true;
    protected bool $timestamps = true;
    protected array $searchable = [];
    protected array $fillable = [];

    protected function db(): Database { return Database::i(); }
    public function table(): string   { return $this->table; }

    protected function notDeleted(string $alias = ''): string
    {
        if (!$this->softDelete) return '1=1';
        $p = $alias ? $alias . '.' : '';
        return "{$p}deleted_at IS NULL";
    }

    public function find(int $id, bool $withTrashed = false): ?array
    {
        $sql = "SELECT * FROM `{$this->table}` WHERE `{$this->primaryKey}` = :id";
        if ($this->softDelete && !$withTrashed) $sql .= ' AND deleted_at IS NULL';
        return $this->db()->one($sql, ['id' => $id]);
    }

    public function findBy(string $col, $val, bool $withTrashed = false): ?array
    {
        $sql = "SELECT * FROM `{$this->table}` WHERE `$col` = :v";
        if ($this->softDelete && !$withTrashed) $sql .= ' AND deleted_at IS NULL';
        $sql .= ' LIMIT 1';
        return $this->db()->one($sql, ['v' => $val]);
    }

    public function all(array $where = [], string $orderBy = 'id ASC', ?int $limit = null): array
    {
        $sql = "SELECT * FROM `{$this->table}` WHERE " . $this->notDeleted();
        $p = [];
        foreach ($where as $k => $v) {
            if ($v === null) { $sql .= " AND `$k` IS NULL"; }
            else { $sql .= " AND `$k` = :w_$k"; $p["w_$k"] = $v; }
        }
        $sql .= " ORDER BY $orderBy";
        if ($limit) $sql .= ' LIMIT ' . (int)$limit;
        return $this->db()->all($sql, $p);
    }

    /** Daftar aktif untuk dropdown: [id => nama]. */
    public function options(string $labelCol = 'name', string $where = '1=1', string $orderBy = 'name ASC'): array
    {
        $extra = $this->softDelete ? ' AND deleted_at IS NULL' : '';
        $active = $this->hasColumn('is_active') ? ' AND is_active = 1' : '';
        $rows = $this->db()->all(
            "SELECT `{$this->primaryKey}` AS id, `$labelCol` AS label FROM `{$this->table}` WHERE $where$extra$active ORDER BY $orderBy"
        );
        $out = [];
        foreach ($rows as $r) $out[(int)$r['id']] = $r['label'];
        return $out;
    }

    public function hasColumn(string $col): bool
    {
        static $cache = [];
        $key = $this->table;
        if (!isset($cache[$key])) {
            $cache[$key] = $this->db()->column(
                'SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ?',
                [$this->table]
            );
        }
        return in_array($col, $cache[$key], true);
    }

    public function create(array $data): int
    {
        $data = $this->filterFillable($data);
        if ($this->timestamps && $this->hasColumn('created_at') && !isset($data['created_at'])) {
            $data['created_at'] = Dates::now();
        }
        return $this->db()->insert($this->table, $data);
    }

    public function update(int $id, array $data): int
    {
        $data = $this->filterFillable($data);
        if (!$data) return 0;
        return $this->db()->update($this->table, $data, "`{$this->primaryKey}` = :pk_id", ['pk_id' => $id]);
    }

    public function delete(int $id): int
    {
        if ($this->softDelete) return $this->db()->softDelete($this->table, $id);
        return $this->db()->exec("DELETE FROM `{$this->table}` WHERE `{$this->primaryKey}` = :id", ['id' => $id]);
    }

    public function restore(int $id): int
    {
        if (!$this->softDelete) return 0;
        return $this->db()->exec("UPDATE `{$this->table}` SET deleted_at = NULL WHERE `{$this->primaryKey}` = :id", ['id' => $id]);
    }

    protected function filterFillable(array $data): array
    {
        if (!$this->fillable) return $data;
        return array_intersect_key($data, array_flip($this->fillable));
    }

    public function count(string $where = '1=1', array $params = []): int
    {
        $extra = $this->softDelete ? ' AND deleted_at IS NULL' : '';
        return (int)$this->db()->value("SELECT COUNT(*) FROM `{$this->table}` WHERE $where$extra", $params);
    }

    /**
     * Paginasi generik.
     * @return array{data:array,total:int,halaman:int,per_halaman:int,total_halaman:int,dari:int,sampai:int}
     */
    public function paginate(string $sql, array $params, int $page = 1, int $perPage = 50, ?string $countSql = null): array
    {
        $page    = max(1, $page);
        $perPage = max(1, min(1000, $perPage));
        $countSql = $countSql ?: 'SELECT COUNT(*) FROM (' . $sql . ') AS _cnt';
        $total = (int)$this->db()->value($countSql, $params);
        $offset = ($page - 1) * $perPage;
        $rows = $this->db()->all($sql . " LIMIT $perPage OFFSET $offset", $params);
        $totalPages = max(1, (int)ceil($total / $perPage));
        return [
            'data' => $rows, 'total' => $total, 'halaman' => $page, 'per_halaman' => $perPage,
            'total_halaman' => $totalPages,
            'dari' => $total ? $offset + 1 : 0,
            'sampai' => min($offset + $perPage, $total),
        ];
    }
}
