<?php

namespace XZD\AI\V5\Database;

/**
 * CRUD 基类 — $wpdb->prepare 安全封装
 */
abstract class Repository
{
    protected \wpdb $db;
    protected string $table;
    protected string $primaryKey = 'id';

    public function __construct()
    {
        global $wpdb;
        $this->db = $wpdb;
        $this->table = $wpdb->prefix . $this->tableName();
    }

    /** 子类必须返回表名（不含前缀） */
    abstract protected function tableName(): string;

    // 查询

    /**
     * 按主键查询
     */
    public function find(int $id): ?array
    {
        $row = $this->db->get_row(
            $this->db->prepare("SELECT * FROM {$this->table} WHERE {$this->primaryKey} = %d", $id),
            ARRAY_A
        );
        return $row ?: null;
    }

    /**
     * 按单个字段查询
     */
    public function findBy(string $field, mixed $value): ?array
    {
        $row = $this->db->get_row(
            $this->db->prepare("SELECT * FROM {$this->table} WHERE {$field} = %s", $value),
            ARRAY_A
        );
        return $row ?: null;
    }

    /**
     * 按条件查询多条
     */
    public function findWhere(array $conditions, string $orderBy = '', int $limit = 0, int $offset = 0): array
    {
        $where = [];
        $bind = [];

        foreach ($conditions as $field => $value) {
            if (is_array($value)) {
                $placeholders = implode(',', array_fill(0, count($value), is_int($value[0]) ? '%d' : '%s'));
                $where[] = "{$field} IN ({$placeholders})";
                $bind = array_merge($bind, $value);
            } elseif (is_int($value)) {
                $where[] = "{$field} = %d";
                $bind[] = $value;
            } elseif ($value === null) {
                $where[] = "{$field} IS NULL";
            } else {
                $where[] = "{$field} = %s";
                $bind[] = $value;
            }
        }

        $sql = "SELECT * FROM {$this->table}";
        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        if ($orderBy) {
            $sql .= " ORDER BY {$orderBy}";
        }
        if ($limit > 0) {
            $sql .= $this->db->prepare(" LIMIT %d", $limit);
            if ($offset > 0) {
                $sql .= $this->db->prepare(" OFFSET %d", $offset);
            }
        }

        if (!empty($bind)) {
            $sql = $this->db->prepare($sql, $bind);
        }

        return $this->db->get_results($sql, ARRAY_A) ?: [];
    }

    /**
     * 获取所有记录
     */
    public function all(string $orderBy = ''): array
    {
        $sql = "SELECT * FROM {$this->table}";
        if ($orderBy) {
            $sql .= " ORDER BY {$orderBy}";
        }
        return $this->db->get_results($sql, ARRAY_A) ?: [];
    }

    /**
     * 分页查询
     */
    public function paginate(int $page = 1, int $perPage = 20, array $conditions = [], string $orderBy = ''): array
    {
        $where = ['1=1'];
        $bind = [];

        foreach ($conditions as $field => $value) {
            if (is_array($value)) {
                $placeholders = implode(',', array_fill(0, count($value), is_int($value[0]) ? '%d' : '%s'));
                $where[] = "{$field} IN ({$placeholders})";
                $bind = array_merge($bind, $value);
            } elseif (is_int($value)) {
                $where[] = "{$field} = %d";
                $bind[] = $value;
            } elseif ($value === null) {
                $where[] = "{$field} IS NULL";
            } else {
                $where[] = "{$field} = %s";
                $bind[] = $value;
            }
        }

        $whereSql = implode(' AND ', $where);
        $offset = ($page - 1) * $perPage;

        $countSql = "SELECT COUNT(*) FROM {$this->table} WHERE {$whereSql}";
        $total = (int) $this->db->get_var(
            empty($bind) ? $countSql : $this->db->prepare($countSql, $bind)
        );

        $dataSql = "SELECT * FROM {$this->table} WHERE {$whereSql}";
        if ($orderBy) {
            $dataSql .= " ORDER BY {$orderBy}";
        }
        if (!empty($bind)) {
            $dataSql = $this->db->prepare($dataSql, $bind);
        }
        $dataSql .= sprintf(" LIMIT %d OFFSET %d", $perPage, $offset);

        $items = $this->db->get_results($dataSql, ARRAY_A) ?: [];

        return [
            'total' => $total,
            'page'  => $page,
            'pages' => (int) ceil($total / $perPage),
            'items' => $items,
        ];
    }

    // 写入

    /**
     * 插入记录
     *
     * @return int|false 插入ID或false
     */
    public function insert(array $data): int|false
    {
        $result = $this->db->insert($this->table, $this->castData($data));
        if ($result === false) {
            return false;
        }
        return $this->db->insert_id;
    }

    /**
     * 更新记录
     *
     * @return int|false 影响行数或false
     */
    public function update(int $id, array $data): int|false
    {
        return $this->db->update($this->table, $this->castData($data), [$this->primaryKey => $id]);
    }

    /**
     * 按条件更新
     */
    public function updateWhere(array $data, array $conditions): int|false
    {
        return $this->db->update($this->table, $this->castData($data), $conditions);
    }

    /**
     * 删除记录
     */
    public function delete(int $id): int|false
    {
        return $this->db->delete($this->table, [$this->primaryKey => $id]);
    }

    /**
     * 按条件删除
     */
    public function deleteWhere(array $conditions): int|false
    {
        return $this->db->delete($this->table, $conditions);
    }

    /**
     * 插入或更新（主键存在则更新）
     */
    public function upsert(array $data): int|false
    {
        $id = $data[$this->primaryKey] ?? 0;
        if ($id && $this->find((int) $id)) {
            $this->update((int) $id, $data);
            return (int) $id;
        }
        return $this->insert($data);
    }

    // 工具

    public function count(array $conditions = []): int
    {
        $where = ['1=1'];
        $bind = [];

        foreach ($conditions as $field => $value) {
            if (is_int($value)) {
                $where[] = "{$field} = %d";
                $bind[] = $value;
            } elseif ($value === null) {
                $where[] = "{$field} IS NULL";
            } else {
                $where[] = "{$field} = %s";
                $bind[] = $value;
            }
        }

        $sql = "SELECT COUNT(*) FROM {$this->table} WHERE " . implode(' AND ', $where);
        return (int) $this->db->get_var(
            empty($bind) ? $sql : $this->db->prepare($sql, $bind)
        );
    }

    /**
     * 将数据类型标准化
     */
    protected function castData(array $data): array
    {
        $result = [];
        foreach ($data as $key => $value) {
            if (is_bool($value)) {
                $result[$key] = $value ? 1 : 0;
            } elseif (is_array($value) || is_object($value)) {
                $result[$key] = wp_json_encode($value, JSON_UNESCAPED_UNICODE);
            } else {
                $result[$key] = $value;
            }
        }
        return $result;
    }

    /**
     * JSON字段解码
     */
    protected function decodeJson(?string $value): ?array
    {
        if ($value === null || $value === '') {
            return null;
        }
        $decoded = json_decode($value, true);
        return json_last_error() === JSON_ERROR_NONE ? $decoded : null;
    }
}
