<?php
declare(strict_types=1);

namespace App\Repositories;

use App\Support\Database;
use PDO;

final class TelegramRepository
{
    private PDO $db;

    public function __construct(?PDO $db = null)
    {
        $this->db = $db ?? Database::connection();
        $this->ensureTable();
    }

    public function status(int $ownerUserId): array
    {
        $stmt = $this->db->prepare(
            'SELECT owner_user_id, telegram_chat_id, telegram_user_id, telegram_username,
                    telegram_name, aktiv, baglanma_tarixi, yenilenme
             FROM erp_telegram_baglantilari WHERE owner_user_id=? LIMIT 1'
        );
        $stmt->execute([$ownerUserId]);
        $row = $stmt->fetch();
        return is_array($row) ? $row : ['owner_user_id' => $ownerUserId, 'aktiv' => 0];
    }

    public function createLinkCode(int $ownerUserId): string
    {
        $this->assertActiveUser($ownerUserId);
        $token = bin2hex(random_bytes(16));
        $hash = hash('sha256', $token);
        $stmt = $this->db->prepare(
            'INSERT INTO erp_telegram_baglantilari
                (owner_user_id, link_code_hash, link_expires_at, aktiv)
             VALUES (?, ?, DATE_ADD(NOW(), INTERVAL 15 MINUTE), 0)
             ON DUPLICATE KEY UPDATE
                link_code_hash=VALUES(link_code_hash),
                link_expires_at=VALUES(link_expires_at)'
        );
        $stmt->execute([$ownerUserId, $hash]);
        return 'link_' . $token;
    }

    public function bind(string $code, string $chatId, string $telegramUserId, string $username, string $name): array
    {
        $token = str_starts_with($code, 'link_') ? substr($code, 5) : $code;
        if (preg_match('/^[a-f0-9]{32}$/', $token) !== 1 || preg_match('/^-?\d+$/', $chatId) !== 1) {
            throw new \InvalidArgumentException('Invalid Telegram link code');
        }

        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare(
                'SELECT t.owner_user_id
                 FROM erp_telegram_baglantilari t
                 JOIN erp_istifadeci_profili u ON u.id=t.owner_user_id
                 WHERE t.link_code_hash=? AND t.link_expires_at>=NOW() AND u.aktiv=1
                 FOR UPDATE'
            );
            $stmt->execute([hash('sha256', $token)]);
            $ownerUserId = (int)$stmt->fetchColumn();
            if ($ownerUserId < 1) {
                throw new \RuntimeException('Link code is invalid or expired');
            }

            $conflict = $this->db->prepare(
                'SELECT owner_user_id FROM erp_telegram_baglantilari
                 WHERE telegram_chat_id=? AND owner_user_id<>? AND aktiv=1 LIMIT 1'
            );
            $conflict->execute([$chatId, $ownerUserId]);
            if ((int)$conflict->fetchColumn() > 0) {
                throw new \RuntimeException('This Telegram account is already linked to another ERP user');
            }

            $update = $this->db->prepare(
                'UPDATE erp_telegram_baglantilari
                 SET telegram_chat_id=?, telegram_user_id=?, telegram_username=?, telegram_name=?,
                     aktiv=1, baglanma_tarixi=NOW(), link_code_hash=NULL, link_expires_at=NULL
                 WHERE owner_user_id=?'
            );
            $update->execute([
                $chatId,
                $telegramUserId !== '' ? $telegramUserId : null,
                $username !== '' ? mb_substr($username, 0, 190) : null,
                $name !== '' ? mb_substr($name, 0, 190) : null,
                $ownerUserId,
            ]);
            $this->db->commit();
            return $this->status($ownerUserId);
        } catch (\Throwable $exception) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            throw $exception;
        }
    }

    public function revoke(int $ownerUserId): void
    {
        $stmt = $this->db->prepare(
            'UPDATE erp_telegram_baglantilari
             SET telegram_chat_id=NULL, telegram_user_id=NULL, telegram_username=NULL,
                 telegram_name=NULL, aktiv=0, link_code_hash=NULL, link_expires_at=NULL
             WHERE owner_user_id=?'
        );
        $stmt->execute([$ownerUserId]);
    }

    public function activeLinks(): array
    {
        return $this->db->query(
            'SELECT t.owner_user_id, t.telegram_chat_id, t.telegram_user_id,
                    t.telegram_username, t.telegram_name
             FROM erp_telegram_baglantilari t
             JOIN erp_istifadeci_profili u ON u.id=t.owner_user_id
             WHERE t.aktiv=1 AND t.telegram_chat_id IS NOT NULL AND u.aktiv=1
             ORDER BY t.owner_user_id'
        )->fetchAll();
    }

    public function chatIdForOwner(int $ownerUserId): ?string
    {
        $stmt = $this->db->prepare(
            'SELECT telegram_chat_id FROM erp_telegram_baglantilari
             WHERE owner_user_id=? AND aktiv=1 LIMIT 1'
        );
        $stmt->execute([$ownerUserId]);
        $chatId = $stmt->fetchColumn();
        return is_string($chatId) && $chatId !== '' ? $chatId : null;
    }

    private function assertActiveUser(int $ownerUserId): void
    {
        $stmt = $this->db->prepare('SELECT COUNT(*) FROM erp_istifadeci_profili WHERE id=? AND aktiv=1');
        $stmt->execute([$ownerUserId]);
        if ((int)$stmt->fetchColumn() !== 1) {
            throw new \RuntimeException('ERP user is not active');
        }
    }

    private function ensureTable(): void
    {
        $this->db->exec(
            "CREATE TABLE IF NOT EXISTS erp_telegram_baglantilari (
                owner_user_id INT UNSIGNED NOT NULL PRIMARY KEY,
                telegram_chat_id BIGINT NULL,
                telegram_user_id BIGINT NULL,
                telegram_username VARCHAR(190) NULL,
                telegram_name VARCHAR(190) NULL,
                link_code_hash CHAR(64) NULL,
                link_expires_at DATETIME NULL,
                aktiv TINYINT(1) NOT NULL DEFAULT 0,
                baglanma_tarixi DATETIME NULL,
                yenilenme TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uq_erp_telegram_chat (telegram_chat_id),
                KEY idx_erp_telegram_link_code (link_code_hash)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
    }
}