<?php

namespace FSPoster\App\Pages\Settings\Workflow\Repositories;

use FSPoster\App\Models\Workflow;
use FSPoster\App\Models\WorkflowAction;
use FSPoster\App\Models\WorkflowLog;
use FSPoster\App\Providers\DB\Collection;
use FSPoster\App\Providers\DB\DB;
use FSPoster\App\Providers\Helpers\Date;

class WorkflowQueueRepository
{
    public function insertOrIgnore(array $data): ?int
    {
        if (empty($data)) {
            return null;
        }

        if (!empty($data['dedupe_key'])) {
            $existing = WorkflowLog::query()
                ->select(['id'], true)
                ->where('dedupe_key', $data['dedupe_key'])
                ->fetch();

            if (!empty($existing)) {
                return null;
            }
        }

        WorkflowLog::insert($data);

        return WorkflowLog::lastId();
    }

    public function claimDue(string $lockToken, int $limit, int $staleLockSeconds, int $maxAgeDays = 7): array
    {
        $this->releaseStaleLocks($staleLockSeconds);
        $this->cancelInvalidPendingWithoutPlannedAt();

        $now = Date::dateTimeSQL();
        $windowStart = Date::dateTimeSQL('now', '-' . $maxAgeDays . ' days');

        $candidateRows = WorkflowLog::query()
            ->select(['id'], true)
            ->where('status', 'pending')
            ->where('planned_at', '>=', $windowStart)
            ->where('planned_at', '<=', $now)
            ->orderBy([WorkflowLog::getField('planned_at') . ' ASC', WorkflowLog::getField('id') . ' ASC'])
            ->limit($limit)
            ->fetchAll();

        if (empty($candidateRows)) {
            return [];
        }

        $ids = array_map(fn($row) => (int)$row['id'], $candidateRows);
        $lockedAt = Date::dateTimeSQL();

        WorkflowLog::query()
            ->where('id', 'in', $ids)
            ->where('status', 'pending')
            ->update([
                'status' => 'processing',
                'lock_token' => $lockToken,
                'locked_at' => $lockedAt,
            ]);

        if (DB::DB()->rows_affected < 1) {
            return [];
        }

        return WorkflowLog::query()
            ->where('lock_token', $lockToken)
            ->orderBy([WorkflowLog::getField('planned_at') . ' ASC', WorkflowLog::getField('id') . ' ASC'])
            ->fetchAll();
    }

    public function touchAttempt(int $id, int $attemptCount): void
    {
        WorkflowLog::query()
            ->where('id', $id)
            ->update([
                'attempt_count' => $attemptCount,
            ]);
    }

    public function markCompleted(int $id, string $status, ?string $errorMessage = null, ?string $responseData = null): void
    {
        WorkflowLog::query()
            ->where('id', $id)
            ->update([
                'status' => $status,
                'error_msg' => $errorMessage,
                'response_data' => $responseData,
                'executed_at' => Date::dateTimeSQL(),
                'lock_token' => null,
                'locked_at' => null,
            ]);
    }

    public function markRetry(int $id, int $attemptCount, int $retryDelaySeconds, ?string $errorMessage = null): void
    {
        WorkflowLog::query()
            ->where('id', $id)
            ->update([
                'status' => 'pending',
                'attempt_count' => $attemptCount,
                'error_msg' => $errorMessage,
                'planned_at' => Date::dateTimeSQL('now', '+' . $retryDelaySeconds . ' seconds'),
                'lock_token' => null,
                'locked_at' => null,
            ]);
    }

    public function markCanceled(int $id, ?string $errorMessage = null, string $status = 'canceled'): void
    {
        WorkflowLog::query()
            ->where('id', $id)
            ->update([
                'status' => $status,
                'error_msg' => $errorMessage,
                'executed_at' => Date::dateTimeSQL(),
                'lock_token' => null,
                'locked_at' => null,
            ]);
    }

    public function cancelPendingByWorkflowId(int $workflowId): void
    {
        WorkflowLog::query()
            ->where('workflow_id', $workflowId)
            ->where('status', 'pending')
            ->update([
                'status' => 'canceled',
                'error_msg' => fsp__('Workflow was removed before execution.'),
                'executed_at' => Date::dateTimeSQL(),
                'lock_token' => null,
                'locked_at' => null,
            ]);
    }

    public function cancelPendingByWorkflowActionId(int $workflowActionId): void
    {
        WorkflowLog::query()
            ->where('workflow_action_id', $workflowActionId)
            ->where('status', 'pending')
            ->update([
                'status' => 'canceled',
                'error_msg' => fsp__('Workflow action was removed before execution.'),
                'executed_at' => Date::dateTimeSQL(),
                'lock_token' => null,
                'locked_at' => null,
            ]);
    }

    public function get(int $id): ?Collection
    {
        return WorkflowLog::query()->where('id', $id)->fetch();
    }

    public function findWorkflow(int $workflowId): ?Collection
    {
        return Workflow::query()->where('id', $workflowId)->fetch();
    }

    public function findWorkflowAction(int $workflowActionId): ?Collection
    {
        return WorkflowAction::query()->where('id', $workflowActionId)->fetch();
    }

    private function releaseStaleLocks(int $staleLockSeconds): void
    {
        $threshold = Date::dateTimeSQL('now', '-' . $staleLockSeconds . ' seconds');

        WorkflowLog::query()
            ->where('status', 'processing')
            ->where('locked_at', '<', $threshold)
            ->update([
                'status' => 'pending',
                'lock_token' => null,
                'locked_at' => null,
            ]);
    }

    private function cancelInvalidPendingWithoutPlannedAt(): void
    {
        WorkflowLog::query()
            ->where('status', 'pending')
            ->whereIsNull('planned_at')
            ->update([
                'status' => 'canceled',
                'error_msg' => fsp__('Queue row is invalid: planned time is missing.'),
                'executed_at' => Date::dateTimeSQL(),
                'lock_token' => null,
                'locked_at' => null,
            ]);
    }
}
