<?php
declare(strict_types=1);

/**
 * Database layer — SQLite via PDO
 */
class Db
{
    private static ?PDO $instance = null;

    public static function get(): PDO
    {
        if (self::$instance === null) {
            self::$instance = new PDO('sqlite:' . DB_PATH, null, null, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]);
            self::$instance->exec('PRAGMA journal_mode=WAL');
            self::$instance->exec('PRAGMA synchronous=NORMAL');
            self::$instance->exec('PRAGMA foreign_keys=ON');
            self::migrate(self::$instance);
        }
        return self::$instance;
    }

    private static function migrate(PDO $db): void
    {
        $db->exec("
            CREATE TABLE IF NOT EXISTS jobs (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                token         TEXT    NOT NULL UNIQUE,
                filename      TEXT    NOT NULL,
                original_name TEXT    NOT NULL,
                total_rows    INTEGER NOT NULL DEFAULT 0,
                processed_rows INTEGER NOT NULL DEFAULT 0,
                success_rows  INTEGER NOT NULL DEFAULT 0,
                failed_rows   INTEGER NOT NULL DEFAULT 0,
                status        TEXT    NOT NULL DEFAULT 'pending',
                url_column    TEXT    NOT NULL DEFAULT 'A',
                timeout_s     INTEGER NOT NULL DEFAULT 20,
                delay_ms      INTEGER NOT NULL DEFAULT 500,
                max_retries   INTEGER NOT NULL DEFAULT 3,
                user_agent    TEXT    NOT NULL DEFAULT '',
                created_at    TEXT    NOT NULL DEFAULT (datetime('now')),
                completed_at  TEXT    NULL,
                error_message TEXT    NULL
            );

            CREATE TABLE IF NOT EXISTS results (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                job_id        INTEGER NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
                row_number    INTEGER NOT NULL,
                product_url   TEXT    NOT NULL,
                image_url     TEXT    NULL,
                status        TEXT    NOT NULL DEFAULT 'pending',
                error_message TEXT    NULL,
                fallback_level INTEGER NULL,
                processed_at  TEXT    NULL
            );

            CREATE INDEX IF NOT EXISTS idx_results_job_id ON results(job_id);
            CREATE INDEX IF NOT EXISTS idx_results_status  ON results(status);

            CREATE TABLE IF NOT EXISTS logs (
                id        INTEGER PRIMARY KEY AUTOINCREMENT,
                job_id    INTEGER NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
                level     TEXT    NOT NULL DEFAULT 'info',
                message   TEXT    NOT NULL,
                timestamp TEXT    NOT NULL DEFAULT (datetime('now'))
            );

            CREATE INDEX IF NOT EXISTS idx_logs_job_id ON logs(job_id);
        ");
    }

    // --- Jobs ---

    public static function createJob(array $data): int
    {
        $db = self::get();
        $stmt = $db->prepare("
            INSERT INTO jobs (token, filename, original_name, url_column, timeout_s, delay_ms, max_retries, user_agent)
            VALUES (:token, :filename, :original_name, :url_column, :timeout_s, :delay_ms, :max_retries, :user_agent)
        ");
        $stmt->execute($data);
        return (int)$db->lastInsertId();
    }

    public static function getJob(int $id): ?array
    {
        $stmt = self::get()->prepare("SELECT * FROM jobs WHERE id = ?");
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getJobByToken(string $token): ?array
    {
        $stmt = self::get()->prepare("SELECT * FROM jobs WHERE token = ?");
        $stmt->execute([$token]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function updateJobStatus(int $id, string $status, ?string $errorMessage = null): void
    {
        $completed = in_array($status, ['done', 'failed']) ? "datetime('now')" : 'NULL';
        $errClause = $errorMessage !== null ? ", error_message = :err" : '';
        $sql = "UPDATE jobs SET status = :status, completed_at = {$completed}{$errClause} WHERE id = :id";
        $params = ['status' => $status, 'id' => $id];
        if ($errorMessage !== null) $params['err'] = $errorMessage;
        self::get()->prepare($sql)->execute($params);
    }

    public static function updateJobProgress(int $id, int $processed, int $success, int $failed): void
    {
        self::get()->prepare("
            UPDATE jobs SET processed_rows = ?, success_rows = ?, failed_rows = ? WHERE id = ?
        ")->execute([$processed, $success, $failed, $id]);
    }

    public static function setJobTotalRows(int $id, int $total): void
    {
        self::get()->prepare("UPDATE jobs SET total_rows = ? WHERE id = ?")->execute([$total, $id]);
    }

    public static function listJobs(int $limit = 20): array
    {
        $stmt = self::get()->prepare("SELECT * FROM jobs ORDER BY created_at DESC LIMIT ?");
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }

    // --- Results ---

    public static function insertResultBatch(int $jobId, array $rows): void
    {
        $db = self::get();
        $db->beginTransaction();
        $stmt = $db->prepare("
            INSERT INTO results (job_id, row_number, product_url, status)
            VALUES (:job_id, :row_number, :product_url, 'pending')
        ");
        foreach ($rows as $row) {
            $stmt->execute(['job_id' => $jobId, 'row_number' => $row['row_number'], 'product_url' => $row['product_url']]);
        }
        $db->commit();
    }

    public static function updateResult(int $jobId, int $rowNumber, string $imageUrl, string $status, ?string $error, ?int $fallbackLevel): void
    {
        self::get()->prepare("
            UPDATE results
            SET image_url = ?, status = ?, error_message = ?, fallback_level = ?, processed_at = datetime('now')
            WHERE job_id = ? AND row_number = ?
        ")->execute([$imageUrl ?: null, $status, $error, $fallbackLevel, $jobId, $rowNumber]);
    }

    public static function getResultsForJob(int $jobId): array
    {
        $stmt = self::get()->prepare("SELECT * FROM results WHERE job_id = ? ORDER BY row_number ASC");
        $stmt->execute([$jobId]);
        return $stmt->fetchAll();
    }

    // --- Logs ---

    public static function addLog(int $jobId, string $level, string $message): void
    {
        self::get()->prepare("
            INSERT INTO logs (job_id, level, message) VALUES (?, ?, ?)
        ")->execute([$jobId, $level, $message]);
    }

    public static function getLogsForJob(int $jobId, int $limit = 200): array
    {
        $stmt = self::get()->prepare("SELECT * FROM logs WHERE job_id = ? ORDER BY id DESC LIMIT ?");
        $stmt->execute([$jobId, $limit]);
        return array_reverse($stmt->fetchAll());
    }
}
