<?php
namespace App\Database;

/**
 * MySQL Database Adapter
 * DatabaseInterface'i PDO/MySQL ile uygular.
 * JSON → MySQL geçişi için sadece config/database.php'de
 * driver'ı 'mysql' yapmak yeterlidir.
 */
class MysqlDatabase implements DatabaseInterface {

    private \PDO $pdo;
    private string $prefix;

    public function __construct() {
        $config = require __DIR__ . '/../../config/database.php';
        $mysql  = $config['mysql'];

        $dsn = sprintf(
            'mysql:host=%s;port=%d;dbname=%s;charset=%s',
            $mysql['host'],
            $mysql['port'],
            $mysql['database'],
            $mysql['charset']
        );

        $this->pdo    = new \PDO($dsn, $mysql['username'], $mysql['password'], [
            \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES   => false,
        ]);
        $this->prefix = $mysql['prefix'] ?? '';
    }

    // ─── Helpers ─────────────────────────────────────────────────

    private function tbl(string $table): string {
        return $this->prefix . $table;
    }

    /**
     * Build a simple WHERE clause from criteria array.
     * Returns [sql_string, bindings].
     */
    private function buildWhere(array $criteria): array {
        if (empty($criteria)) {
            return ['1', []];
        }

        $parts    = [];
        $bindings = [];
        foreach ($criteria as $key => $value) {
            if (is_array($value)) {
                $placeholders = implode(',', array_fill(0, count($value), '?'));
                $parts[]      = "`{$key}` IN ({$placeholders})";
                foreach ($value as $v) {
                    $bindings[] = $v;
                }
            } elseif ($value === null) {
                $parts[] = "`{$key}` IS NULL";
            } else {
                $parts[]    = "`{$key}` = ?";
                $bindings[] = $value;
            }
        }

        return [implode(' AND ', $parts), $bindings];
    }

    // ─── Interface Methods ───────────────────────────────────────

    public function selectAll(string $table, array $criteria = []): array {
        [$where, $bindings] = $this->buildWhere($criteria);
        $sql  = "SELECT * FROM `{$this->tbl($table)}` WHERE {$where}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bindings);
        $rows = $stmt->fetchAll();

        // Decode JSON columns back to arrays (mirrors JsonDatabase behavior)
        return array_map([$this, 'decodeJsonColumns'], $rows);
    }

    public function selectOne(string $table, array $criteria): ?array {
        $results = $this->selectAll($table, $criteria);
        return $results[0] ?? null;
    }

    public function insert(string $table, array $data): bool {
        if (!isset($data['id'])) {
            $data['id'] = uniqid($table . '_');
        }

        $data = $this->encodeJsonColumns($data);

        $cols   = array_keys($data);
        $places = array_fill(0, count($cols), '?');
        $sql    = "INSERT INTO `{$this->tbl($table)}` (`" . implode('`,`', $cols) . "`) VALUES (" . implode(',', $places) . ")";

        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute(array_values($data));
    }

    public function update(string $table, array $criteria, array $data): bool {
        $data = $this->encodeJsonColumns($data);

        $setParts = [];
        $bindings = [];
        foreach ($data as $key => $value) {
            $setParts[] = "`{$key}` = ?";
            $bindings[] = $value;
        }

        [$where, $whereBindings] = $this->buildWhere($criteria);
        $sql = "UPDATE `{$this->tbl($table)}` SET " . implode(', ', $setParts) . " WHERE {$where}";

        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute(array_merge($bindings, $whereBindings));
    }

    public function delete(string $table, array $criteria): bool {
        [$where, $bindings] = $this->buildWhere($criteria);
        $sql  = "DELETE FROM `{$this->tbl($table)}` WHERE {$where}";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($bindings);
    }

    // ─── JSON Column Helpers ─────────────────────────────────────

    /**
     * Columns that store arrays/objects as JSON strings in the DB.
     */
    private static array $jsonColumns = ['images', 'categories', 'specs', 'tags', 'meta'];

    private function encodeJsonColumns(array $data): array {
        foreach (self::$jsonColumns as $col) {
            if (isset($data[$col]) && is_array($data[$col])) {
                $data[$col] = json_encode($data[$col], JSON_UNESCAPED_UNICODE);
            }
        }
        return $data;
    }

    private function decodeJsonColumns(array $row): array {
        foreach (self::$jsonColumns as $col) {
            if (isset($row[$col]) && is_string($row[$col])) {
                $decoded = json_decode($row[$col], true);
                if (json_last_error() === JSON_ERROR_NONE) {
                    $row[$col] = $decoded;
                }
            }
        }
        return $row;
    }

    // ─── Extra Utility ───────────────────────────────────────────

    /**
     * Returns the underlying PDO instance for advanced use (migrations etc.)
     */
    public function getPdo(): \PDO {
        return $this->pdo;
    }
}
