<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

final class SyncJobRepository
{
    private ?bool $supportsCancelRequested = null;

    public function __construct(
        private PDO $pdo,
        private string $tablePrefix
    ) {}

    private function table(): string
    {
        return '`' . str_replace('`', '``', $this->tablePrefix . 'sync_jobs') . '`';
    }

    private function supportsCancelRequested(): bool
    {
        if ($this->supportsCancelRequested !== null) {
            return $this->supportsCancelRequested;
        }
        $t = $this->table();
        try {
            $stmt = $this->pdo->query("SHOW COLUMNS FROM {$t} LIKE 'cancel_requested'");
            $row = $stmt ? $stmt->fetch(PDO::FETCH_ASSOC) : false;
            $this->supportsCancelRequested = is_array($row);
        } catch (\Throwable) {
            $this->supportsCancelRequested = false;
        }
        return $this->supportsCancelRequested;
    }

    /**
     * Claim the next runnable job for this user (pending/retry, next_run_at <= now).
     *
     * @return array{id:int,user_id:int,property_id:int,job_type:string,start_date:string,end_date:string,status:string,attempts:int}|null
     */
    public function claimNextForUser(int $userId): ?array
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');

        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare(
                "SELECT id, user_id, property_id, job_type, start_date, end_date, status, attempts
                 FROM {$t}
                 WHERE user_id = ?
                   AND is_active = 1
                   AND status IN ('pending','retry')
                   AND (next_run_at IS NULL OR next_run_at <= ?)
                 ORDER BY updated_at ASC, created_at ASC, id ASC
                 LIMIT 1
                 FOR UPDATE"
            );
            $stmt->execute([$userId, $now]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!is_array($row)) {
                $this->pdo->commit();
                return null;
            }

            $id = (int) ($row['id'] ?? 0);
            if ($id < 1) {
                $this->pdo->commit();
                return null;
            }

            $upd = $this->pdo->prepare(
                "UPDATE {$t}
                 SET status = 'running',
                     attempts = attempts + 1,
                     next_run_at = NULL,
                     updated_at = ?
                 WHERE id = ?
                 LIMIT 1"
            );
            $upd->execute([$now, $id]);

            $this->pdo->commit();

            return [
                'id' => $id,
                'user_id' => (int) ($row['user_id'] ?? 0),
                'property_id' => (int) ($row['property_id'] ?? 0),
                'job_type' => (string) ($row['job_type'] ?? ''),
                'start_date' => (string) ($row['start_date'] ?? ''),
                'end_date' => (string) ($row['end_date'] ?? ''),
                'status' => (string) ($row['status'] ?? ''),
                'attempts' => (int) ($row['attempts'] ?? 0) + 1,
            ];
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    /**
     * Recover jobs stuck in RUNNING (e.g., cron killed/timeout).
     *
     * Moves them back to RETRY with next_run_at = now.
     */
    public function recoverStaleRunningJobsForUser(int $userId, int $staleSeconds = 900): int
    {
        $staleSeconds = max(60, min(86400, $staleSeconds));
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $cutoff = date('Y-m-d H:i:s', time() - $staleSeconds);

        $stmt = $this->pdo->prepare(
            "UPDATE {$t}
             SET status = 'retry',
                 next_run_at = ?,
                 updated_at = ?
             WHERE user_id = ?
               AND is_active = 1
               AND status = 'running'
               AND updated_at < ?"
        );
        $stmt->execute([$now, $now, $userId, $cutoff]);
        return (int) $stmt->rowCount();
    }

    public function pauseActiveJobsForProperty(int $userId, int $propertyId): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "UPDATE {$t}
             SET status = 'paused', next_run_at = NULL, updated_at = ?
             WHERE user_id = ? AND property_id = ? AND is_active = 1 AND status IN ('pending','retry')
            "
        );
        $stmt->execute([$now, $userId, $propertyId]);
    }

    public function resumePausedJobsForProperty(int $userId, int $propertyId): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "UPDATE {$t}
             SET status = 'pending', next_run_at = NULL, updated_at = ?
             WHERE user_id = ? AND property_id = ? AND is_active = 1 AND status = 'paused'
            "
        );
        $stmt->execute([$now, $userId, $propertyId]);
    }

    public function cancelActiveJobsForProperty(int $userId, int $propertyId): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $sqlWithFlag =
            "UPDATE {$t}
             SET status = 'cancelled',
                 is_active = 0,
                 next_run_at = NULL,
                 cancel_requested = 0,
                 updated_at = ?
             WHERE user_id = ? AND property_id = ? AND is_active = 1";
        $sqlNoFlag =
            "UPDATE {$t}
             SET status = 'cancelled',
                 is_active = 0,
                 next_run_at = NULL,
                 updated_at = ?
             WHERE user_id = ? AND property_id = ? AND is_active = 1";

        $sql = $this->supportsCancelRequested() ? $sqlWithFlag : $sqlNoFlag;
        $stmt = $this->pdo->prepare($sql);
        try {
            $stmt->execute([$now, $userId, $propertyId]);
        } catch (\PDOException $e) {
            // Older schema uses UNIQUE(user_id, property_id, job_type, is_active) which blocks multiple is_active=0 rows.
            // If we hit that constraint, drop inactive history for the property and retry.
            $msg = $e->getMessage();
            if (str_contains($msg, 'Duplicate entry') && str_contains($msg, 'uniq_active_job')) {
                $del = $this->pdo->prepare(
                    "DELETE FROM {$t} WHERE user_id = ? AND property_id = ? AND is_active = 0"
                );
                $del->execute([$userId, $propertyId]);
                $stmt2 = $this->pdo->prepare($sql);
                $stmt2->execute([$now, $userId, $propertyId]);
            } else {
                throw $e;
            }
        }
    }

    public function isCancelRequested(int $jobId): bool
    {
        if (!$this->supportsCancelRequested()) {
            return false;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare("SELECT cancel_requested FROM {$t} WHERE id = ? LIMIT 1");
        $stmt->execute([$jobId]);
        $v = $stmt->fetchColumn();
        return (int) ($v ?: 0) === 1;
    }

    public function markCancelled(int $jobId): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        if ($this->supportsCancelRequested()) {
            $stmt = $this->pdo->prepare(
                "UPDATE {$t}
                 SET status = 'cancelled', is_active = 0, next_run_at = NULL, cancel_requested = 0, updated_at = ?
                 WHERE id = ?
                 LIMIT 1"
            );
            $stmt->execute([$now, $jobId]);
        } else {
            $stmt = $this->pdo->prepare(
                "UPDATE {$t}
                 SET status = 'cancelled', is_active = 0, next_run_at = NULL, updated_at = ?
                 WHERE id = ?
                 LIMIT 1"
            );
            $stmt->execute([$now, $jobId]);
        }
    }

    public function isActive(int $jobId): bool
    {
        $t = $this->table();
        $stmt = $this->pdo->prepare("SELECT is_active, status FROM {$t} WHERE id = ? LIMIT 1");
        $stmt->execute([$jobId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            return false;
        }
        $active = (int) ($row['is_active'] ?? 0) === 1;
        $status = strtolower((string) ($row['status'] ?? ''));
        return $active && $status !== 'cancelled';
    }

    public function markDone(int $jobId): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        if ($this->supportsCancelRequested()) {
            $stmt = $this->pdo->prepare(
                "UPDATE {$t}
                 SET status = 'done', is_active = 0, last_error = NULL, next_run_at = NULL, cancel_requested = 0, updated_at = ?
                 WHERE id = ?
                 LIMIT 1"
            );
            $stmt->execute([$now, $jobId]);
        } else {
            $stmt = $this->pdo->prepare(
                "UPDATE {$t}
                 SET status = 'done', is_active = 0, last_error = NULL, next_run_at = NULL, updated_at = ?
                 WHERE id = ?
                 LIMIT 1"
            );
            $stmt->execute([$now, $jobId]);
        }
    }

    public function markFailed(int $jobId, string $error): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        if ($this->supportsCancelRequested()) {
            $stmt = $this->pdo->prepare(
                "UPDATE {$t}
                 SET status = 'failed', is_active = 0, last_error = ?, next_run_at = NULL, cancel_requested = 0, updated_at = ?
                 WHERE id = ?
                 LIMIT 1"
            );
            $stmt->execute([mb_substr($error, 0, 255), $now, $jobId]);
        } else {
            $stmt = $this->pdo->prepare(
                "UPDATE {$t}
                 SET status = 'failed', is_active = 0, last_error = ?, next_run_at = NULL, updated_at = ?
                 WHERE id = ?
                 LIMIT 1"
            );
            $stmt->execute([mb_substr($error, 0, 255), $now, $jobId]);
        }
    }

    public function markRetry(int $jobId, string $error, string $nextRunAt): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "UPDATE {$t}
             SET status = 'retry', last_error = ?, next_run_at = ?, updated_at = ?
             WHERE id = ?
             LIMIT 1"
        );
        $stmt->execute([mb_substr($error, 0, 255), $nextRunAt, $now, $jobId]);
    }

    public function rescheduleFromDate(int $jobId, string $newStartDate): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "UPDATE {$t}
             SET start_date = ?, status = 'pending', next_run_at = NULL, last_error = NULL, updated_at = ?
             WHERE id = ?
             LIMIT 1"
        );
        $stmt->execute([$newStartDate, $now, $jobId]);
    }

    public function rescheduleToDate(int $jobId, string $newEndDate): void
    {
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "UPDATE {$t}
             SET end_date = ?, status = 'pending', next_run_at = NULL, last_error = NULL, updated_at = ?
             WHERE id = ?
             LIMIT 1"
        );
        $stmt->execute([$newEndDate, $now, $jobId]);
    }

    /**
     * Enqueue a full backfill job for a property (dedup by unique key on active jobs).
     */
    public function enqueueFullBackfill(int $userId, int $propertyId, string $startDate, string $endDate): bool
    {
        if ($userId < 1 || $propertyId < 1) {
            return false;
        }
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $endDate)) {
            return false;
        }
        if ($startDate > $endDate) {
            return false;
        }

        $t = $this->table();
        $now = date('Y-m-d H:i:s');

        // Dedup: if unique constraint blocks insert, treat as "already queued".
        try {
            $stmt = $this->pdo->prepare(
                "INSERT INTO {$t} (user_id, property_id, job_type, initial_start_date, initial_end_date, start_date, end_date, status, is_active, attempts, next_run_at, last_error, created_at, updated_at)
                 VALUES (?, ?, 'full_backfill', ?, ?, ?, ?, 'pending', 1, 0, NULL, NULL, ?, ?)"
            );
            $stmt->execute([$userId, $propertyId, $startDate, $endDate, $startDate, $endDate, $now, $now]);
            return true;
        } catch (\Throwable) {
            return false;
        }
    }

    /**
     * Enqueue a "last 28 days" bootstrap job (dedup by unique key on active jobs).
     */
    public function enqueueLast28Days(int $userId, int $propertyId): bool
    {
        $endDt = new \DateTimeImmutable('yesterday');
        $end = $endDt->format('Y-m-d');
        $start = $endDt->modify('-27 days')->format('Y-m-d');

        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        try {
            $stmt = $this->pdo->prepare(
                "INSERT INTO {$t} (user_id, property_id, job_type, initial_start_date, initial_end_date, start_date, end_date, status, is_active, attempts, next_run_at, last_error, created_at, updated_at)
                 VALUES (?, ?, 'bootstrap_28', ?, ?, ?, ?, 'pending', 1, 0, NULL, NULL, ?, ?)"
            );
            $stmt->execute([$userId, $propertyId, $start, $end, $start, $end, $now, $now]);
            return true;
        } catch (\Throwable) {
            return false;
        }
    }

    /**
     * @return null|array{completed_at:string,initial_start_date:?string,initial_end_date:?string}
     */
    public function lastCompletedFullBackfillForProperty(int $userId, int $propertyId): ?array
    {
        if ($userId < 1 || $propertyId < 1) {
            return null;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT updated_at AS completed_at, initial_start_date, initial_end_date
             FROM {$t}
             WHERE user_id = ? AND property_id = ? AND job_type = 'full_backfill' AND status = 'done'
             ORDER BY updated_at DESC, id DESC
             LIMIT 1"
        );
        $stmt->execute([$userId, $propertyId]);
        $r = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($r)) {
            return null;
        }
        return [
            'completed_at' => (string) ($r['completed_at'] ?? ''),
            'initial_start_date' => $r['initial_start_date'] !== null ? (string) $r['initial_start_date'] : null,
            'initial_end_date' => $r['initial_end_date'] !== null ? (string) $r['initial_end_date'] : null,
        ];
    }

    /**
     * @return array{status:string,next_run_at:?string,last_error:?string,attempts:int,start_date:string,end_date:string}|null
     */
    public function activeJobStatusForProperty(int $userId, int $propertyId): ?array
    {
        if ($userId < 1 || $propertyId < 1) {
            return null;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT job_type, status, next_run_at, last_error, attempts, start_date, end_date, initial_start_date, initial_end_date
             FROM {$t}
             WHERE user_id = ? AND property_id = ? AND is_active = 1
             ORDER BY (job_type = 'bootstrap_28') DESC, created_at ASC, id ASC
             LIMIT 1"
        );
        $stmt->execute([$userId, $propertyId]);
        $r = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($r)) {
            return null;
        }
        return [
            'status' => (string) ($r['status'] ?? ''),
            'job_type' => (string) ($r['job_type'] ?? ''),
            'next_run_at' => $r['next_run_at'] !== null ? (string) $r['next_run_at'] : null,
            'last_error' => $r['last_error'] !== null ? (string) $r['last_error'] : null,
            'attempts' => (int) ($r['attempts'] ?? 0),
            'start_date' => (string) ($r['start_date'] ?? ''),
            'end_date' => (string) ($r['end_date'] ?? ''),
            'initial_start_date' => $r['initial_start_date'] !== null ? (string) $r['initial_start_date'] : null,
            'initial_end_date' => $r['initial_end_date'] !== null ? (string) $r['initial_end_date'] : null,
        ];
    }

    /**
     * @return list<array<string,mixed>>
     */
    public function recentJobsForUser(int $userId, int $limit = 20): array
    {
        $limit = max(1, min(50, $limit));
        $t = $this->table();
        $props = '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_properties') . '`';
        $stmt = $this->pdo->prepare(
            "SELECT j.id, j.property_id, p.site_url,
                    j.job_type, j.status, j.attempts, j.next_run_at, j.last_error,
                    j.start_date, j.end_date, j.initial_start_date, j.initial_end_date, j.created_at, j.updated_at
             FROM {$t} j
             LEFT JOIN {$props} p ON p.id = j.property_id
             WHERE j.user_id = ?
             ORDER BY id DESC
             LIMIT {$limit}"
        );
        $stmt->execute([$userId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            if (is_array($r)) {
                $out[] = $r;
            }
        }
        return $out;
    }

    /**
     * True if there is any active job for the property (bootstrap/backfill).
     */
    public function hasActiveJobForProperty(int $userId, int $propertyId): bool
    {
        if ($userId < 1 || $propertyId < 1) {
            return false;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT 1
             FROM {$t}
             WHERE user_id = ? AND property_id = ? AND is_active = 1
             LIMIT 1"
        );
        $stmt->execute([$userId, $propertyId]);
        return (bool) $stmt->fetchColumn();
    }
}

