<?php
namespace App\Services;

use App\Core\Database;
use PDO;
use RuntimeException;
use Throwable;

final class WhatsAppQueueService
{
    private PDO $pdo;
    private UltraMsgService $provider;

    public function __construct(?PDO $pdo = null, ?UltraMsgService $provider = null)
    {
        $this->pdo = $pdo ?? Database::connection();
        $this->provider = $provider ?? new UltraMsgService();
    }

    public function schemaReady(): bool
    {
        try {
            $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?');
            $stmt->execute(['whatsapp_message_queue']);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Throwable) {
            return false;
        }
    }

    public function overview(): array
    {
        $limits = $this->provider->limits();
        $today = ['queued' => 0, 'processing' => 0, 'sent' => 0, 'failed' => 0, 'cancelled' => 0, 'reserved' => 0];
        $all = $today;

        if ($this->schemaReady()) {
            $all = array_merge($all, $this->statusCounts(null));
            $today = array_merge($today, $this->statusCounts(date('Y-m-d')));
        }

        $today['reserved'] = (int)$today['queued'] + (int)$today['processing'] + (int)$today['sent'];
        $all['reserved'] = (int)$all['queued'] + (int)$all['processing'] + (int)$all['sent'];

        return [
            'schema_ready' => $this->schemaReady(),
            'today' => $today,
            'all' => $all,
            'remaining_today' => max(0, (int)$limits['max_per_day'] - (int)$today['reserved']),
            'limits' => $limits,
        ];
    }

    public function enqueue(array $message): array
    {
        if (!$this->schemaReady()) {
            throw new RuntimeException('WhatsApp queue is not installed. Import database/migrations/041_ultramsg_whatsapp_queue.sql.');
        }

        $mobile = $this->provider->normalizeMobile((string)($message['mobile'] ?? ''));
        $body = trim((string)($message['message'] ?? ''));
        if ($mobile === null) return ['status' => 'invalid', 'error' => 'Invalid WhatsApp number.'];
        if ($body === '') return ['status' => 'invalid', 'error' => 'Empty WhatsApp message.'];

        $limits = $this->provider->limits();
        $hash = hash('sha256', $mobile . "\n" . $body);
        $duplicateAfter = date('Y-m-d H:i:s', time() - ((int)$limits['duplicate_window_hours'] * 3600));
        $lockName = 'pyrk_ultramsg_queue_guard';
        $lockAcquired = false;

        try {
            $lockStmt = $this->pdo->prepare('SELECT GET_LOCK(?, 5)');
            $lockStmt->execute([$lockName]);
            $lockAcquired = (int)$lockStmt->fetchColumn() === 1;
            if (!$lockAcquired) throw new RuntimeException('WhatsApp queue is busy. Please try again.');

            $duplicate = $this->pdo->prepare('SELECT id, status FROM whatsapp_message_queue WHERE mobile = ? AND message_hash = ? AND created_at >= ? AND status IN ("queued","processing","sent") ORDER BY id DESC LIMIT 1');
            $duplicate->execute([$mobile, $hash, $duplicateAfter]);
            $existing = $duplicate->fetch(PDO::FETCH_ASSOC);
            if ($existing) {
                return ['status' => 'duplicate', 'queue_id' => (int)$existing['id'], 'error' => null];
            }

            $overview = $this->overview();
            if ((int)$overview['remaining_today'] <= 0) {
                return ['status' => 'daily_limit', 'error' => 'The configured WhatsApp daily safety limit has been reached.'];
            }

            $stmt = $this->pdo->prepare('INSERT INTO whatsapp_message_queue
                (batch_id, recipient_log_id, user_id, walk_id, mobile, message, message_hash, provider, status, attempts, max_attempts, available_at, created_by, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, "ultramsg", "queued", 0, ?, NOW(), ?, NOW(), NOW())');
            $stmt->execute([
                (int)($message['batch_id'] ?? 0) ?: null,
                (int)($message['recipient_log_id'] ?? 0) ?: null,
                (int)($message['user_id'] ?? 0) ?: null,
                (int)($message['walk_id'] ?? 0) ?: null,
                $mobile,
                $body,
                $hash,
                (int)$limits['max_attempts'],
                (int)($message['created_by'] ?? 0) ?: null,
            ]);

            return ['status' => 'queued', 'queue_id' => (int)$this->pdo->lastInsertId(), 'mobile' => $mobile, 'error' => null];
        } finally {
            if ($lockAcquired) {
                try {
                    $this->pdo->prepare('SELECT RELEASE_LOCK(?)')->execute([$lockName]);
                } catch (Throwable) {
                }
            }
        }
    }

    public function cancelBatch(int $batchId): int
    {
        if (!$this->schemaReady() || $batchId <= 0) return 0;

        $stmt = $this->pdo->prepare('UPDATE whatsapp_message_queue SET status = "cancelled", updated_at = NOW(), error_message = "Cancelled by admin before sending" WHERE batch_id = ? AND status IN ("queued","failed")');
        $stmt->execute([$batchId]);
        $count = $stmt->rowCount();

        if ($this->columnExists('manual_notification_batch_recipients', 'whatsapp_status')) {
            $this->pdo->prepare('UPDATE manual_notification_batch_recipients r JOIN whatsapp_message_queue q ON q.recipient_log_id = r.id SET r.whatsapp_status = "cancelled" WHERE q.batch_id = ? AND q.status = "cancelled"')->execute([$batchId]);
        }
        $this->recalculateBatch($batchId);
        return $count;
    }

    public function processQueue(?int $limit = null): array
    {
        if (!$this->schemaReady()) {
            throw new RuntimeException('WhatsApp queue table is missing.');
        }

        // One global worker at a time. This prevents a cPanel cron run and a
        // manual run from processing the queue concurrently.
        $workerLockName = 'pyrk_ultramsg_worker_v1';
        $workerLockAcquired = false;

        try {
            $workerLock = $this->pdo->prepare('SELECT GET_LOCK(?, 0)');
            $workerLock->execute([$workerLockName]);
            $workerLockAcquired = (int)$workerLock->fetchColumn() === 1;

            if (!$workerLockAcquired) {
                return [
                    'processed' => 0,
                    'sent' => 0,
                    'failed' => 0,
                    'retried' => 0,
                    'message' => 'Another WhatsApp queue worker is already running. This run exited safely.',
                ];
            }

            $config = $this->provider->instanceStatus();
            if (empty($config['authenticated'])) {
                return [
                    'processed' => 0,
                    'sent' => 0,
                    'failed' => 0,
                    'retried' => 0,
                    'message' => $config['message'] ?? 'UltraMsg is not authenticated.',
                    'configuration' => $config,
                ];
            }

            $limits = $this->provider->limits();
            $sentTodayStmt = $this->pdo->query('SELECT COUNT(*) FROM whatsapp_message_queue WHERE status = "sent" AND sent_at >= CURDATE() AND sent_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)');
            $sentToday = (int)$sentTodayStmt->fetchColumn();
            $remainingSendToday = max(0, (int)$limits['max_per_day'] - $sentToday);
            $limit = max(1, min($limit ?? (int)$limits['messages_per_run'], (int)$limits['messages_per_run'], $remainingSendToday));
            if ($remainingSendToday <= 0) {
                return ['processed' => 0, 'sent' => 0, 'failed' => 0, 'retried' => 0, 'message' => 'Daily WhatsApp send limit reached.', 'configuration' => $config];
            }

            $this->pdo->exec('UPDATE whatsapp_message_queue SET status = "queued", locked_at = NULL, updated_at = NOW() WHERE status = "processing" AND locked_at < DATE_SUB(NOW(), INTERVAL 15 MINUTE)');

            $ids = $this->pdo->query('SELECT id FROM whatsapp_message_queue WHERE status = "queued" AND available_at <= NOW() AND attempts < max_attempts ORDER BY id ASC LIMIT ' . (int)$limit)->fetchAll(PDO::FETCH_COLUMN);
            $sent = 0;
            $failed = 0;
            $retried = 0;
            $processed = 0;
            $batchIds = [];

            foreach ($ids as $idValue) {
                $id = (int)$idValue;
                $claim = $this->pdo->prepare('UPDATE whatsapp_message_queue SET status = "processing", locked_at = NOW(), attempts = attempts + 1, updated_at = NOW() WHERE id = ? AND status = "queued"');
                $claim->execute([$id]);
                if ($claim->rowCount() !== 1) continue;

                $rowStmt = $this->pdo->prepare('SELECT * FROM whatsapp_message_queue WHERE id = ? LIMIT 1');
                $rowStmt->execute([$id]);
                $row = $rowStmt->fetch(PDO::FETCH_ASSOC);
                if (!$row) continue;

                $processed++;
                if (!empty($row['batch_id'])) $batchIds[(int)$row['batch_id']] = true;

                try {
                    $result = $this->provider->sendText((string)$row['mobile'], (string)$row['message'], 'pyrk-q-' . $id);
                    $done = $this->pdo->prepare('UPDATE whatsapp_message_queue SET status = "sent", provider_message_id = ?, sent_at = NOW(), locked_at = NULL, error_message = NULL, updated_at = NOW() WHERE id = ?');
                    $done->execute([(string)($result['provider_message_id'] ?? ''), $id]);
                    $this->updateRecipientStatus($row, 'sent', (string)($result['provider_message_id'] ?? ''), null);
                    $sent++;
                } catch (Throwable $e) {
                    $attempts = (int)$row['attempts'];
                    $maxAttempts = (int)$row['max_attempts'];
                    $final = $attempts >= $maxAttempts;
                    $nextAt = date('Y-m-d H:i:s', time() + (min(60, 5 * max(1, $attempts)) * 60));
                    $status = $final ? 'failed' : 'queued';
                    $update = $this->pdo->prepare('UPDATE whatsapp_message_queue SET status = ?, available_at = ?, locked_at = NULL, error_message = ?, updated_at = NOW() WHERE id = ?');
                    $update->execute([$status, $nextAt, (function_exists('mb_substr') ? mb_substr($e->getMessage(), 0, 1000, 'UTF-8') : substr($e->getMessage(), 0, 1000)), $id]);
                    $this->updateRecipientStatus($row, $final ? 'failed' : 'queued', null, $e->getMessage());
                    if ($final) $failed++; else $retried++;
                }
            }

            foreach (array_keys($batchIds) as $batchId) {
                $this->recalculateBatch((int)$batchId);
            }

            return [
                'processed' => $processed,
                'sent' => $sent,
                'failed' => $failed,
                'retried' => $retried,
                'message' => "Processed {$processed} queued WhatsApp message(s).",
                'configuration' => $config,
            ];
        } finally {
            if ($workerLockAcquired) {
                try {
                    $release = $this->pdo->prepare('SELECT RELEASE_LOCK(?)');
                    $release->execute([$workerLockName]);
                } catch (Throwable) {
                    // The database connection also releases advisory locks automatically.
                }
            }
        }
    }

    public function recalculateBatch(int $batchId): void
    {
        if (!$this->schemaReady() || $batchId <= 0) return;

        $stmt = $this->pdo->prepare('SELECT
            SUM(status = "sent") AS sent_count,
            SUM(status = "failed") AS failed_count,
            SUM(status IN ("queued","processing")) AS queued_count
            FROM whatsapp_message_queue WHERE batch_id = ?');
        $stmt->execute([$batchId]);
        $counts = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $sets = [];
        $params = [];
        foreach ([
            'whatsapp_sent_count' => (int)($counts['sent_count'] ?? 0),
            'whatsapp_failed_count' => (int)($counts['failed_count'] ?? 0),
            'whatsapp_queued_count' => (int)($counts['queued_count'] ?? 0),
        ] as $column => $value) {
            if ($this->columnExists('manual_notification_batches', $column)) {
                $sets[] = $column . ' = ?';
                $params[] = $value;
            }
        }

        if ($sets) {
            $params[] = $batchId;
            $this->pdo->prepare('UPDATE manual_notification_batches SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
        }
    }

    private function statusCounts(?string $date): array
    {
        $where = '';
        $params = [];
        if ($date !== null) {
            $where = ' WHERE created_at >= ? AND created_at < DATE_ADD(?, INTERVAL 1 DAY)';
            $params = [$date . ' 00:00:00', $date . ' 00:00:00'];
        }

        $stmt = $this->pdo->prepare('SELECT status, COUNT(*) AS total FROM whatsapp_message_queue' . $where . ' GROUP BY status');
        $stmt->execute($params);
        $counts = ['queued' => 0, 'processing' => 0, 'sent' => 0, 'failed' => 0, 'cancelled' => 0];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $status = (string)$row['status'];
            if (array_key_exists($status, $counts)) $counts[$status] = (int)$row['total'];
        }
        return $counts;
    }

    private function updateRecipientStatus(array $queueRow, string $status, ?string $providerId, ?string $error): void
    {
        $recipientLogId = (int)($queueRow['recipient_log_id'] ?? 0);
        if ($recipientLogId <= 0 || !$this->columnExists('manual_notification_batch_recipients', 'whatsapp_status')) return;

        $sets = ['whatsapp_status = ?'];
        $params = [$status];
        if ($providerId !== null && $this->columnExists('manual_notification_batch_recipients', 'whatsapp_provider_message_id')) {
            $sets[] = 'whatsapp_provider_message_id = ?';
            $params[] = $providerId;
        }
        if ($error !== null) {
            $sets[] = "error_message = CONCAT_WS(' | ', NULLIF(error_message, ''), ?)";
            $params[] = 'WhatsApp: ' . (function_exists('mb_substr') ? mb_substr($error, 0, 900, 'UTF-8') : substr($error, 0, 900));
        }
        $params[] = $recipientLogId;
        $this->pdo->prepare('UPDATE manual_notification_batch_recipients SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
    }

    private function columnExists(string $table, string $column): bool
    {
        static $cache = [];
        $key = $table . '.' . $column;
        if (array_key_exists($key, $cache)) return $cache[$key];
        try {
            $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?');
            $stmt->execute([$table, $column]);
            return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable) {
            return $cache[$key] = false;
        }
    }
}
