<?php
declare(strict_types=1);

/**
 * 模型基类
 * 数据库连接支持构造函数注入（方便测试时替换为 mock），不传时默认使用 Db::instance() 的 default 连接
 */
abstract class Model
{
    /** 表名，子类用 dxDataGrid 相关方法（grid/gridInsert/gridUpdate/gridRemove）时需要声明 */
    protected string $table = '';

    /** 主键字段名，配合 gridUpdate/gridRemove 使用 */
    protected string $key = '';

    protected ?Db $db = null;

    public function __construct(?Db $db = null)
    {
        $this->db = $db ?? Db::instance();
    }

    /**
     * 主键字段名，导出等通用功能需要按主键过滤时使用
     */
    public function getKey(): string
    {
        return $this->key;
    }

    /**
     * grid() 查询用的 FROM 子句，默认就是 $table；需要 JOIN 关联表展示时子类覆盖此方法
     */
    protected function gridFrom(): string
    {
        return DxFilter::quoteColumn($this->table);
    }

    /**
     * grid() 查询用的 SELECT 列，默认 *；需要附加关联字段时子类覆盖此方法
     */
    protected function gridSelect(): string
    {
        return '*';
    }

    /**
     * grid() 查询用的 GROUP BY 子句，默认不分组；联表后用聚合函数（如 GROUP_CONCAT/JSON_ARRAYAGG）展示多对多关系时子类覆盖
     */
    protected function gridGroupBy(): string
    {
        return '';
    }

    /**
     * 供 dxDataGrid CustomStore.load(loadOptions) 使用的通用查询
     * @param array $loadOptions 含 filter / sort / skip / take / requireTotalCount
     * @return array{data:array,totalCount?:int}
     */
    public function grid(array $loadOptions): array
    {
        [$whereSql, $params] = DxFilter::toSql($loadOptions['filter'] ?? null);
        $where = $whereSql !== '' ? "WHERE {$whereSql}" : '';
        $from = $this->gridFrom();
        $groupBy = $this->gridGroupBy();
        $group = $groupBy !== '' ? "GROUP BY {$groupBy}" : '';

        $sql = "SELECT {$this->gridSelect()} FROM {$from} {$where} {$group} " . $this->buildOrderSql($loadOptions['sort'] ?? null);

        $take = (int) ($loadOptions['take'] ?? 0);
        if ($take > 0) {
            $skip = max(0, (int) ($loadOptions['skip'] ?? 0));
            $sql .= " LIMIT {$skip}, {$take}";
        }

        $result = ['data' => $this->db->rawQuery($sql, $params)];

        if (!empty($loadOptions['requireTotalCount'])) {
            $countExpr = $groupBy !== '' ? "COUNT(DISTINCT {$groupBy})" : 'COUNT(*)';
            $countRows = $this->db->rawQuery("SELECT {$countExpr} AS cnt FROM {$from} {$where}", $params);
            $result['totalCount'] = (int) ($countRows[0]['cnt'] ?? 0);
        }

        return $result;
    }

    /**
     * 供 dxDataGrid CustomStore.insert(values) 使用，返回新增记录的主键值
     */
    public function gridInsert(array $values): mixed
    {
        $this->db->insert($this->table, $values);

        return $this->db->id();
    }

    /**
     * 供 dxDataGrid CustomStore.update(key, values) 使用，返回受影响行数
     */
    public function gridUpdate(mixed $key, array $values): int
    {
        return $this->db->update($this->table, $values, [$this->key => $key])->rowCount();
    }

    /**
     * 供 dxDataGrid CustomStore.remove(key) 使用，返回受影响行数
     */
    public function gridRemove(mixed $key): int
    {
        return $this->db->delete($this->table, [$this->key => $key])->rowCount();
    }

    /**
     * 把 DevExtreme loadOptions.sort 转成 SQL 的 ORDER BY 子句
     */
    private function buildOrderSql(?array $sort): string
    {
        if (empty($sort)) {
            return '';
        }

        $parts = [];
        foreach ($sort as $item) {
            if (empty($item['selector'])) {
                continue;
            }
            $direction = !empty($item['desc']) ? 'DESC' : 'ASC';
            $parts[] = DxFilter::quoteColumn($item['selector']) . " {$direction}";
        }

        return $parts ? 'ORDER BY ' . implode(', ', $parts) : '';
    }

    /**
     * 分页查询
     * @param string $table 表名
     * @param array $where 查询条件（medoo 风格）
     * @param int $page 当前页，从 1 开始
     * @param int $pagesize 每页条数
     * @param array|null $join 关联查询条件（medoo 风格），不需要关联时为 null
     * @param array|string|null $column 查询列，配合 $join 使用
     * @return array{page:int,pagesize:int,total:int,records:int,limit:int}
     */
    public function paging(
        string $table,
        array $where,
        int $page = 1,
        int $pagesize = 50,
        ?array $join = null,
        $column = null
    ): array {
        $page = max(1, $page);
        $pagesize = $pagesize ?: 50;

        if (!empty($where['GROUP'])) {
            $records = $this->countGroupedRows($table, $where, $join, $column);
        } else {
            $records = $join
                ? $this->db->count($table, $join, $column, $where)
                : $this->db->count($table, $where);
        }

        return [
            'page'     => $page,
            'pagesize' => $pagesize,
            'total'    => (int) ceil($records / $pagesize),
            'records'  => (int) $records,
            'limit'    => ($page - 1) * $pagesize,
        ];
    }

    /**
     * 带 GROUP BY 的查询，统计去重分组后的总记录数
     */
    private function countGroupedRows(string $table, array $where, ?array $join, $column): int
    {
        $where['LIMIT'] = [0, 1];
        $join
            ? $this->db->select($table, $join, $column ?? '1', $where)
            : $this->db->select($table, $column ?? '1', $where);

        $query = $this->db->last();
        $query = substr($query, 0, strrpos($query, 'LIMIT'));

        return (int) $this->db->query("SELECT COUNT(1) FROM ({$query}) groupCount")->fetchColumn();
    }
}