<?php
namespace EasyChat\Model;

use EasyChat\Library\AppClock;







class ScheduledMessage extends Base {
    public const ACTIVE_STATUSES = ['scheduled', 'releasing', 'failed'];
    private $table_exists;

    public static function tableSql(string $prefix): string {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}scheduled_messages` (
            `scheduled_message_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `public_id` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `client_request_id` VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `payload_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `chat_id` INT(11) NOT NULL,
            `source_provider` VARCHAR(32) NOT NULL DEFAULT 'widget',
            `message_text` TEXT NOT NULL,
            `status` VARCHAR(16) NOT NULL DEFAULT 'scheduled',
            `scheduled_at` DATETIME NOT NULL,
            `timezone` VARCHAR(64) NOT NULL DEFAULT 'UTC',
            `created_by_manager_id` INT(11) NOT NULL,
            `created_by_manager_name` VARCHAR(255) NOT NULL DEFAULT '',
            `updated_by_manager_id` INT(11) NOT NULL,
            `updated_by_manager_name` VARCHAR(255) NOT NULL DEFAULT '',
            `revision` INT UNSIGNED NOT NULL DEFAULT 1,
            `message_id` INT(11) DEFAULT NULL,
            `attempt_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `last_error_code` VARCHAR(64) NOT NULL DEFAULT '',
            `last_error` VARCHAR(255) NOT NULL DEFAULT '',
            `claim_token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
            `claimed_at` DATETIME DEFAULT NULL,
            `released_at` DATETIME DEFAULT NULL,
            `cancelled_at` DATETIME DEFAULT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_scheduled_public_id` (`public_id`),
            UNIQUE KEY `uniq_scheduled_client_request` (`created_by_manager_id`, `client_request_id`),
            KEY `idx_scheduled_due` (`status`, `scheduled_at`, `scheduled_message_id`),
            KEY `idx_scheduled_chat` (`chat_id`, `status`, `scheduled_at`, `scheduled_message_id`),
            KEY `idx_scheduled_message` (`message_id`),
            KEY `idx_scheduled_claim` (`status`, `claimed_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    public static function eventTableSql(string $prefix): string {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}scheduled_message_events` (
            `event_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `scheduled_message_id` BIGINT UNSIGNED NOT NULL,
            `event_type` VARCHAR(32) NOT NULL,
            `manager_id` INT(11) NOT NULL DEFAULT 0,
            `idempotency_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
            `from_status` VARCHAR(16) NOT NULL DEFAULT '',
            `to_status` VARCHAR(16) NOT NULL DEFAULT '',
            `old_scheduled_at` DATETIME DEFAULT NULL,
            `new_scheduled_at` DATETIME DEFAULT NULL,
            `context_json` TEXT NOT NULL,
            `date_added` DATETIME NOT NULL,
            UNIQUE KEY `uniq_scheduled_event_idempotency` (`idempotency_hash`),
            KEY `idx_scheduled_event` (`scheduled_message_id`, `event_id`),
            KEY `idx_scheduled_event_manager` (`manager_id`, `date_added`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    public function begin(): void {
        $this->db->query('START TRANSACTION');
    }

    public function commit(): void {
        $this->db->query('COMMIT');
    }

    public function rollback(): void {
        $this->db->query('ROLLBACK');
    }

    public function findByClientRequest(int $manager_id, string $client_request_id, bool $for_update = false): array {
        if ($manager_id <= 0 || $client_request_id === '') {
            return [];
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}scheduled_messages`
             WHERE `created_by_manager_id` = " . (int)$manager_id . "
               AND `client_request_id` = '" . $this->db->escape($client_request_id) . "'
             LIMIT 1" . ($for_update ? ' FOR UPDATE' : '')
        );
        return ($query && $query->num_rows) ? $query->row : [];
    }

    public function findByPublicId(string $public_id, bool $for_update = false): array {
        if ($public_id === '') {
            return [];
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}scheduled_messages`
             WHERE `public_id` = '" . $this->db->escape($public_id) . "'
             LIMIT 1" . ($for_update ? ' FOR UPDATE' : '')
        );
        return ($query && $query->num_rows) ? $query->row : [];
    }

    public function findById(int $scheduled_message_id, bool $for_update = false): array {
        if ($scheduled_message_id <= 0) {
            return [];
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}scheduled_messages`
             WHERE `scheduled_message_id` = " . (int)$scheduled_message_id . "
             LIMIT 1" . ($for_update ? ' FOR UPDATE' : '')
        );
        return ($query && $query->num_rows) ? $query->row : [];
    }

    public function create(array $data): int {
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "INSERT INTO `{$this->prefix}scheduled_messages` SET
                `public_id` = '" . $this->db->escape((string)$data['public_id']) . "',
                `client_request_id` = '" . $this->db->escape((string)$data['client_request_id']) . "',
                `payload_hash` = '" . $this->db->escape((string)$data['payload_hash']) . "',
                `chat_id` = " . (int)$data['chat_id'] . ",
                `source_provider` = '" . $this->db->escape((string)$data['source_provider']) . "',
                `message_text` = '" . $this->db->escape((string)$data['message_text']) . "',
                `status` = 'scheduled',
                `scheduled_at` = '" . $this->db->escape((string)$data['scheduled_at']) . "',
                `timezone` = '" . $this->db->escape((string)$data['timezone']) . "',
                `created_by_manager_id` = " . (int)$data['manager_id'] . ",
                `created_by_manager_name` = '" . $this->db->escape((string)$data['manager_name']) . "',
                `updated_by_manager_id` = " . (int)$data['manager_id'] . ",
                `updated_by_manager_name` = '" . $this->db->escape((string)$data['manager_name']) . "',
                `revision` = 1,
                `date_added` = '" . $this->db->escape($now) . "',
                `date_modified` = '" . $this->db->escape($now) . "'"
        );
        return (int)$this->db->getLastId();
    }

    public function updateScheduled(int $id, int $revision, array $data): bool {
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "UPDATE `{$this->prefix}scheduled_messages` SET
                `message_text` = '" . $this->db->escape((string)$data['message_text']) . "',
                `scheduled_at` = '" . $this->db->escape((string)$data['scheduled_at']) . "',
                `timezone` = '" . $this->db->escape((string)$data['timezone']) . "',
                `status` = 'scheduled',
                `updated_by_manager_id` = " . (int)$data['manager_id'] . ",
                `updated_by_manager_name` = '" . $this->db->escape((string)$data['manager_name']) . "',
                `last_error_code` = '',
                `last_error` = '',
                `claim_token_hash` = '',
                `claimed_at` = NULL,
                `revision` = `revision` + 1,
                `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `scheduled_message_id` = " . (int)$id . "
               AND `revision` = " . (int)$revision . "
               AND `status` IN ('scheduled', 'failed')"
        );
        return (int)$this->db->countAffected() === 1;
    }

    public function cancel(int $id, int $revision, int $manager_id, string $manager_name): bool {
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "UPDATE `{$this->prefix}scheduled_messages` SET
                `status` = 'cancelled',
                `updated_by_manager_id` = " . (int)$manager_id . ",
                `updated_by_manager_name` = '" . $this->db->escape($manager_name) . "',
                `cancelled_at` = '" . $this->db->escape($now) . "',
                `claim_token_hash` = '',
                `claimed_at` = NULL,
                `revision` = `revision` + 1,
                `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `scheduled_message_id` = " . (int)$id . "
               AND `revision` = " . (int)$revision . "
               AND `status` IN ('scheduled', 'failed')"
        );
        return (int)$this->db->countAffected() === 1;
    }

    public function dueIds(int $limit = 100): array {
        $limit = max(1, min(500, $limit));
        $now = (new AppClock($this->registry))->nowSql();
        $query = $this->db->query(
            "SELECT `scheduled_message_id`
             FROM `{$this->prefix}scheduled_messages`
             WHERE `status` = 'scheduled'
               AND `scheduled_at` <= '" . $this->db->escape($now) . "'
             ORDER BY `scheduled_at` ASC, `scheduled_message_id` ASC
             LIMIT {$limit}"
        );
        return array_map('intval', array_column($query ? ($query->rows ?: []) : [], 'scheduled_message_id'));
    }

    public function claim(int $id, string $claim_token_hash): array {
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "UPDATE `{$this->prefix}scheduled_messages` SET
                `status` = 'releasing',
                `claim_token_hash` = '" . $this->db->escape($claim_token_hash) . "',
                `claimed_at` = '" . $this->db->escape($now) . "',
                `attempt_count` = `attempt_count` + 1,
                `revision` = `revision` + 1,
                `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `scheduled_message_id` = " . (int)$id . "
               AND `status` = 'scheduled'
               AND `scheduled_at` <= '" . $this->db->escape($now) . "'"
        );
        if ((int)$this->db->countAffected() !== 1) {
            return [];
        }
        return $this->findById($id, true);
    }

    public function markReleased(int $id, string $claim_token_hash, int $message_id): bool {
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "UPDATE `{$this->prefix}scheduled_messages` SET
                `status` = 'released',
                `message_id` = " . (int)$message_id . ",
                `released_at` = '" . $this->db->escape($now) . "',
                `claim_token_hash` = '',
                `claimed_at` = NULL,
                `revision` = `revision` + 1,
                `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `scheduled_message_id` = " . (int)$id . "
               AND `status` = 'releasing'
               AND `claim_token_hash` = '" . $this->db->escape($claim_token_hash) . "'"
        );
        return (int)$this->db->countAffected() === 1;
    }

    public function markFailed(int $id, string $claim_token_hash, string $code, string $message): bool {
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "UPDATE `{$this->prefix}scheduled_messages` SET
                `status` = 'failed',
                `last_error_code` = '" . $this->db->escape(substr($code, 0, 64)) . "',
                `last_error` = '" . $this->db->escape(mb_substr($message, 0, 255, 'UTF-8')) . "',
                `claim_token_hash` = '',
                `claimed_at` = NULL,
                `revision` = `revision` + 1,
                `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `scheduled_message_id` = " . (int)$id . "
               AND `status` = 'releasing'
               AND `claim_token_hash` = '" . $this->db->escape($claim_token_hash) . "'"
        );
        return (int)$this->db->countAffected() === 1;
    }

    public function recoverStaleClaims(int $lease_seconds = 300): int {
        $lease_seconds = max(60, min(3600, $lease_seconds));
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "UPDATE `{$this->prefix}scheduled_messages` SET
                `status` = 'scheduled',
                `claim_token_hash` = '',
                `claimed_at` = NULL,
                `last_error_code` = 'release_lease_expired',
                `last_error` = 'Отправка была восстановлена после прерывания worker.',
                `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `status` = 'releasing'
               AND `claimed_at` < DATE_SUB('" . $this->db->escape($now) . "', INTERVAL {$lease_seconds} SECOND)"
        );
        return (int)$this->db->countAffected();
    }

    public function activeForChat(int $chat_id): array {
        if ($chat_id <= 0 || !$this->tableExists()) {
            return [];
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}scheduled_messages`
             WHERE `chat_id` = " . (int)$chat_id . "
               AND `status` IN ('scheduled', 'releasing', 'failed')
             ORDER BY `scheduled_at` ASC, `scheduled_message_id` ASC"
        );
        return $query ? ($query->rows ?: []) : [];
    }

    public function countsForChats(array $chat_ids): array {
        $ids = array_values(array_unique(array_filter(array_map('intval', $chat_ids))));
        if (!$ids || !$this->tableExists()) {
            return [];
        }
        $query = $this->db->query(
            "SELECT `chat_id`, COUNT(*) AS `scheduled_count`
             FROM `{$this->prefix}scheduled_messages`
             WHERE `chat_id` IN (" . implode(',', $ids) . ")
               AND `status` IN ('scheduled', 'releasing', 'failed')
             GROUP BY `chat_id`"
        );
        $result = [];
        foreach ($query ? ($query->rows ?: []) : [] as $row) {
            $result[(int)$row['chat_id']] = (int)$row['scheduled_count'];
        }
        return $result;
    }

    public function cancelForChats(array $chat_ids): int {
        $ids = array_values(array_unique(array_filter(array_map('intval', $chat_ids))));
        if (!$ids || !$this->tableExists()) {
            return 0;
        }
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "UPDATE `{$this->prefix}scheduled_messages`
             SET `status` = 'cancelled',
                 `cancelled_at` = '" . $this->db->escape($now) . "',
                 `claim_token_hash` = '',
                 `claimed_at` = NULL,
                 `revision` = `revision` + 1,
                 `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `chat_id` IN (" . implode(',', $ids) . ")
               AND `status` IN ('scheduled', 'failed')"
        );
        return (int)$this->db->countAffected();
    }

    public function recordEvent(
        int $scheduled_message_id,
        string $event_type,
        int $manager_id,
        string $from_status,
        string $to_status,
        string $old_scheduled_at,
        string $new_scheduled_at,
        string $idempotency_key = '',
        array $context = []
    ): void {
        $idempotency_hash = $idempotency_key !== ''
            ? hash('sha256', $manager_id . '|' . $event_type . '|' . $idempotency_key)
            : hash('sha256', bin2hex(random_bytes(16)));
        $context_json = json_encode($context, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $now = (new AppClock($this->registry))->nowSql();
        $this->db->query(
            "INSERT IGNORE INTO `{$this->prefix}scheduled_message_events` SET
                `scheduled_message_id` = " . (int)$scheduled_message_id . ",
                `event_type` = '" . $this->db->escape(substr($event_type, 0, 32)) . "',
                `manager_id` = " . (int)$manager_id . ",
                `idempotency_hash` = '" . $this->db->escape($idempotency_hash) . "',
                `from_status` = '" . $this->db->escape(substr($from_status, 0, 16)) . "',
                `to_status` = '" . $this->db->escape(substr($to_status, 0, 16)) . "',
                `old_scheduled_at` = " . ($old_scheduled_at !== '' ? "'" . $this->db->escape($old_scheduled_at) . "'" : 'NULL') . ",
                `new_scheduled_at` = " . ($new_scheduled_at !== '' ? "'" . $this->db->escape($new_scheduled_at) . "'" : 'NULL') . ",
                `context_json` = '" . $this->db->escape(is_string($context_json) ? $context_json : '{}') . "',
                `date_added` = '" . $this->db->escape($now) . "'"
        );
    }

    private function tableExists(): bool {
        if ($this->table_exists !== null) {
            return $this->table_exists;
        }
        try {
            $query = $this->db->query(
                "SELECT 1
                 FROM information_schema.tables
                 WHERE table_schema = DATABASE()
                   AND table_name = '" . $this->db->escape($this->prefix . 'scheduled_messages') . "'
                 LIMIT 1"
            );
            $this->table_exists = (bool)($query && $query->num_rows);
        } catch (\Throwable $e) {
            $this->table_exists = false;
        }
        return $this->table_exists;
    }
}
