<?php

namespace pictpostpersonal;

use Exception;
use PDO;
use PDOException;

/**
 * SQLiteによるデータベース操作補助クラス
 */
class SQLiteCRUD
{
    /**
     * @var PDO データベース接続オブジェクト
     */
    private $db;

    /**
     * @var string 操作対象のテーブル名
     */
    private $table = '';

    /**
     * @var string 選択するフィールド
     */
    private $select = '*';

    /**
     * @var string WHERE句の条件
     */
    private $where = '';

    /**
     * @var array WHERE句のパラメータ
     */
    private $where_conditions = [];

    /**
     * @var string ORDER BY句の条件
     */
    private $order_by = '';

    /**
     * @var string JOIN句のテーブル名
     */
    private $join_table = '';

    /**
     * @var string JOIN句の条件
     */
    private $join_condition = '';

    /**
     * @var string LIKE句のエスケープパターン
     */
    private const LIKE_ESCAPE_PATTERN = '/(\S+\s+LIKE\s+:[^\s\)]+)/i';

    /**
     * コンストラクタ
     *
     * @param PDO $db PDOインスタンス
     * @throws PDOException 接続エラー時
     */
    public function __construct(PDO $db)
    {
        $this->db = $db;
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->reset();
    }

    /**
     * クエリビルダーの状態をリセットする
     *
     * @return void
     */
    private function reset(): void
    {
        $this->select = '*';
        $this->table = '';
        $this->where = '';
        $this->where_conditions = [];
        $this->order_by = '';
        $this->join_table = '';
        $this->join_condition = '';
    }

    /**
     * トランザクションを開始する
     *
     * @return void
     */
    public function begin(): void
    {
        if (!$this->db->inTransaction()) {
            $this->db->beginTransaction();
        }
    }

    /**
     * トランザクションをコミットする
     *
     * @return void
     */
    public function commit(): void
    {
        if (!$this->db->inTransaction()) {
            throw new Exception('トランザクションが開始されていません。');
        }
        $this->db->commit();
    }

    /**
     * トランザクションをロールバックする
     *
     * @return void
     */
    public function rollback(): void
    {
        if (!$this->db->inTransaction()) {
            throw new Exception('トランザクションが開始されていません。');
        }
        $this->db->rollBack();
    }

    /**
     * 操作対象とするテーブル名を指定する。
     *
     * @param string $table テーブル名
     * @return self
     */
    public function table(string $table): self
    {
        //セキュリティチェック
        if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $table)) {
            throw new Exception('Invalid table name format');
        }
        $this->validWords($table);

        $this->reset();
        $this->table = $table;
        return $this;
    }

    /**
     * Select対象とするフィールドを指定する。
     *
     * @param string $fields カンマ区切りのフィールド名
     * @return self
     */
    public function select(string $fields): self
    {
        //セキュリティチェック
        $this->validWords($fields);

        $this->select = $fields;
        return $this;
    }

    /**
     * WHERE条件を指定する。
     *
     * @param string $condition WHERE句の条件
     * @param array $parameters バインディングするパラメータ
     * @return self
     */
    public function where(string $condition, array $parameters = []): self
    {
        //セキュリティチェック
        $this->validWords($condition);

        $this->where = $condition;
        $this->where_conditions = $parameters;
        return $this;
    }

    /**
     * ORDER BY条件を指定する。
     *
     * @param string $orderBy ORDER BY句の条件
     * @return self
     */
    public function orderBy(string $order_by): self
    {
        //セキュリティチェック
        $this->validWords($order_by);

        $this->order_by = $order_by;
        return $this;
    }

    /**
     * JOIN条件を指定する。
     *
     * @param string $table JOINするテーブル名
     * @param string $condition JOINの条件
     * @return self
     */
    public function join(string $table, string $condition): self
    {
        //セキュリティチェック
        $this->validWords($table);
        $this->validWords($condition);

        $this->join_table = $table;
        $this->join_condition = $condition;
        return $this;
    }


    /**
     * テーブルの存在確認を実行する
     *
     * @return bool
     * 
     */
    public function exists(): bool
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }

        // テーブルの存在を確認
        $stmt = $this->db->prepare('SELECT name FROM sqlite_master WHERE type=\'table\' AND name = :table');
        $stmt->execute([':table' => $this->table]);

        return $stmt->fetchColumn();
    }


    /**
     * テーブルを作成する。
     *
     * @param array $fields フィールド定義の配列
     * @param array $keys キーの配列
     * @return bool 作成に成功した場合true、既に存在していた場合false
     * @throws Exception 条件未満またはテーブル名未指定時
     */
    public function createTable(array $fields, array $keys = []): bool
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }

        if (empty($fields)) {
            throw new Exception('1つ以上のフィールドを指定する必要があります。');
        }

        // テーブルの存在を確認
        $stmt = $this->db->prepare('SELECT name FROM sqlite_master WHERE type=\'table\' AND name = :table');
        $stmt->execute([':table' => $this->table]);

        if ($stmt->fetchColumn() !== false) {
            return false; // 既に存在する場合は処理を終了
        }

        // フィールド定義の作成
        $field_sql = implode(', ', $fields);
        $field_sql .= ', sys_created_at TEXT NOT NULL DEFAULT (DATETIME(\'now\', \'localtime\')), ';
        $field_sql .= 'sys_updated_at TEXT NOT NULL DEFAULT (DATETIME(\'now\', \'localtime\'))';

        // テーブル作成クエリ
        $escaped_table = $this->escapeIdentifier($this->table);
        $create_table_sql = "CREATE TABLE {$escaped_table} ({$field_sql});";

        // トリガー作成クエリ
        $escaped_trigger_name = 'trigger_' . str_replace('"', '', $escaped_table) . '_sys_updated_at';
        $trigger_sql = "CREATE TRIGGER $escaped_trigger_name AFTER UPDATE ON {$this->table} 
                       BEGIN 
                           UPDATE {$this->table} SET sys_updated_at = DATETIME('now', 'localtime') 
                           WHERE rowid = NEW.rowid; 
                       END;";

        // インデックス作成クエリ
        $index_sql = '';
        foreach ($keys as $key) {
            if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*(,\s*[a-zA-Z_][a-zA-Z0-9_]*)*$/', $key)) {
                throw new Exception('Invalid column name in keys');
            }
            $escaped_key = str_replace(',', '_', preg_replace('/[^a-zA-Z0-9_,\s]/', '', $key));
            $index_name = 'idx_' . str_replace('"', '', $this->table) . '_' . $escaped_key;
            $index_sql .= "CREATE INDEX {$index_name} ON {$this->escapeIdentifier($this->table)} ({$key});";
        }


        // 全てのSQLを実行
        $sql = $create_table_sql . $trigger_sql . $index_sql;
        $this->db->exec($sql);

        return true;
    }

    /**
     * データを1件作成する。
     *
     * @param array $data フィールド名と値の連想配列
     * @return int 作成されたデータのID
     * @throws Exception テーブル名未指定時
     */
    public function create(array $data): int
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }

        $fields = array_keys($data);
        $placeholders = array_map(function ($field) {
            return ":$field";
        }, $fields);

        $field_sql = implode(', ', $fields);
        $placeholder_sql = implode(', ', $placeholders);

        $sql = "INSERT INTO {$this->table} ({$field_sql}) VALUES ({$placeholder_sql})";
        $stmt = $this->db->prepare($sql);

        foreach ($data as $field => $value) {
            $param_type = is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR;
            $stmt->bindValue(":$field", $value, $param_type);
        }

        $stmt->execute();
        $last_insert_id = (int)$this->db->lastInsertId();

        $this->reset();

        return $last_insert_id;
    }

    /**
     * データを削除する。
     *
     * @param int|null $id IDが指定された場合、そのIDのデータを削除
     * @return int 削除された行数
     * @throws Exception テーブル名未指定時
     */
    public function delete(?int $id = null): int
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }

        if ($id !== null) {
            //IDが指定された場合は指定ID行を削除
            $sql = "DELETE FROM {$this->table} WHERE id = :id";
            $stmt = $this->db->prepare($sql);
            $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        } else {
            //IDが指定されなかった場合はWHERE句に基づいて削除
            $where_clause = $this->buildWhereClause();
            $join_clause = $this->buildJoinClause();

            $sql = "DELETE FROM {$this->table} {$join_clause} {$where_clause}";
            $stmt = $this->db->prepare($sql);

            foreach ($this->where_conditions as $key => $value) {
                $stmt->bindValue(":$key", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
        }

        $stmt->execute();
        $affected_rows = $stmt->rowCount();

        $this->reset();

        return $affected_rows;
    }

    /**
     * データを更新する。
     *
     * @param array $data フィールド名と値の連想配列
     * @return int 更新された行数
     * @throws Exception テーブル名未指定時
     */
    public function update(array $data): int
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }


        $set_clauses = [];
        foreach ($data as $field => $value) {
            $set_clauses[] = "{$field} = :set_{$field}";
        }
        $set_sql = implode(', ', $set_clauses);

        $where_clause = $this->buildWhereClause();
        $join_clause = $this->buildJoinClause();

        $sql = "UPDATE {$this->table} {$join_clause} SET {$set_sql} {$where_clause}";
        $stmt = $this->db->prepare($sql);

        // バインディング
        foreach ($this->where_conditions as $key => $value) {
            if (strpos($where_clause, ":$key") !== false) {
                $stmt->bindValue(":$key", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
        }

        foreach ($data as $field => $value) {
            $param_type = is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR;
            $stmt->bindValue(":set_$field", $value, $param_type);
        }

        $stmt->execute();
        $affected_rows = $stmt->rowCount();

        $this->reset();

        return $affected_rows;
    }

    /**
     * データを取得する。
     *
     * @param int|null $limit 取得する件数
     * @param int|null $offset 取得するオフセット
     * @return array 取得したデータの配列
     * @throws Exception テーブル名未指定時
     */
    public function fetch(?int $limit = null, ?int $offset = null): array
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }

        $sql = "SELECT {$this->select} FROM {$this->table}";

        // JOIN句の追加
        if (!empty($this->join_table) && !empty($this->join_condition)) {
            $sql .= " LEFT JOIN {$this->join_table} ON {$this->join_condition}";
        }

        // WHERE句の追加
        if (!empty($this->where)) {
            $where_clause = $this->buildWhereClause();
            $sql .= " {$where_clause}";
        }

        // ORDER BY句の追加
        if (!empty($this->order_by)) {
            $sql .= " ORDER BY {$this->order_by}";
        }

        // LIMIT句の追加
        if ($limit !== null) {
            $sql .= ' LIMIT :limit';
        }

        // OFFSET句の追加
        if ($offset !== null) {
            $sql .= ' OFFSET :offset';
        }

        $stmt = $this->db->prepare($sql);

        // WHERE条件のバインディング
        foreach ($this->where_conditions as $key => $value) {
            $stmt->bindValue(":$key", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }

        // LIMITのバインディング
        if ($limit !== null) {
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        }

        // OFFSETのバインディング
        if ($offset !== null) {
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        }

        $stmt->execute();
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $this->reset();

        return $data;
    }

    /**
     * データの件数を取得する。
     *
     * @return int データの件数
     * @throws Exception テーブル名未指定時
     */
    public function count(): int
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }

        $sql = "SELECT COUNT(*) as count FROM {$this->table}";

        // JOIN句の追加
        if (!empty($this->join_table) && !empty($this->join_condition)) {
            $sql .= " LEFT JOIN {$this->join_table} ON {$this->join_condition}";
        }

        // WHERE句の追加
        if (!empty($this->where)) {
            $where_clause = $this->buildWhereClause();
            $sql .= " {$where_clause}";
        }

        $stmt = $this->db->prepare($sql);

        // WHERE条件のバインディング
        foreach ($this->where_conditions as $key => $value) {
            $stmt->bindValue(":$key", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }

        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        $this->reset();

        return (int)($result['count'] ?? 0);
    }

    /**
     * テーブルのID値連番をリセットする。
     *
     * @return void
     * @throws Exception テーブル名未指定時
     */
    public function reseed(): void
    {
        if (empty($this->table)) {
            throw new Exception('操作は「table(...)」から開始する必要があります。');
        }

        $sql = 'DELETE FROM sqlite_sequence WHERE name = :table';
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':table', $this->table, PDO::PARAM_STR);
        $stmt->execute();
    }

    /**
     * 生のSQLクエリでデータを取得する。
     *
     * @param string $query SQLクエリ
     * @param array $parameters バインディングするパラメータ
     * @param int|null $limit 取得する件数
     * @param int|null $offset 取得するオフセット
     * @return array 取得したデータの配列
     */
    public function rawQuery(string $query, array $parameters = [], ?int $limit = null, ?int $offset = null): array
    {
        if ($limit !== null) {
            $query .= ' LIMIT :limit';
        }

        if ($offset !== null) {
            $query .= ' OFFSET :offset';
        }

        $stmt = $this->db->prepare($query);

        foreach ($parameters as $key => $value) {
            // プレースホルダにコロンが付いているか確認
            $placeholder = strpos($key, ':') === 0 ? $key : ":$key";
            $stmt->bindValue($placeholder, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }

        if ($limit !== null) {
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        }

        if ($offset !== null) {
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        }

        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 生のSQLクエリを実行する。
     *
     * @param string $query SQLクエリ
     * @param array $parameters バインディングするパラメータ
     * @return void
     */
    public function execute(string $query, array $parameters = []): void
    {
        $stmt = $this->db->prepare($query);

        foreach ($parameters as $key => $value) {
            // プレースホルダにコロンが付いているか確認
            $placeholder = strpos($key, ':') === 0 ? $key : ":$key";
            $stmt->bindValue($placeholder, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }

        $stmt->execute();
    }

    /**
     * LIKE構文用に値をエスケープする
     *
     * @param string $value エスケープ対象の値
     * @return string エスケープ済みの値
     */
    public static function likeEscape(string $value): string
    {
        return preg_replace('/([!_%])/', '!$1', $value);
    }

    /**
     * テーブル名/カラム名等のエスケープを行う
     *
     * @param string $identifier
     * 
     * @return string
     * 
     */
    private function escapeIdentifier(string $identifier): string
    {
        return '"' . str_replace('"', '""', $identifier) . '"';
    }

    /**
     * WHERE句を構築する
     *
     * @return string WHERE句
     */
    private function buildWhereClause(): string
    {
        if (strpos($this->where, ' ESCAPE ') === false) {
            $escaped_where = preg_replace(self::LIKE_ESCAPE_PATTERN, '$1 ESCAPE \'!\'', $this->where);
        } else {
            $escaped_where = $this->where;
        }
        if ($escaped_where == '') {
            return '';
        }
        return "WHERE {$escaped_where}";
    }

    /**
     * JOIN句を構築する
     *
     * @return string JOIN句
     */
    private function buildJoinClause(): string
    {
        if (!empty($this->join_table) && !empty($this->join_condition)) {
            return "LEFT JOIN {$this->join_table} ON {$this->join_condition}";
        }
        return '';
    }



    /**
     * SQLインジェクション防止のため、禁止ワードをチェックする
     *
     * @param string $clause チェック対象の文字列
     * @return void
     * @throws Exception 禁止ワードが含まれている場合
     */
    public function validWords(string  $clause): void
    {
        $blacklist_patterns = [
            '/;/',                          // セミコロン
            '/--/',                         // SQLコメント
            '/\/\*.*\*\//',                 // ブロックコメント  
            '/\bUNION\b.*\bSELECT\b/i',    // UNION SELECT
            '/\bDROP\b|\bDELETE\b.*\bFROM\b|\bTRUNCATE\b/i',  // 破壊的操作
        ];

        foreach ($blacklist_patterns as $pattern) {
            if (preg_match($pattern, $clause)) {
                throw new Exception('Invalid WHERE clause');
            }
        }

        if (strlen($clause) > 500) {
            throw new Exception('WHERE clause too long');
        }
    }
}
