<?php
namespace App\Models;

class ReferralModel extends BaseModel
{
    private function hasCodeColumn(): bool
    {
        static $has;
        if ($has !== null) {
            return $has;
        }
        $stmt = $this->db->query("SHOW COLUMNS FROM referrals LIKE 'code'");
        $has = (bool)$stmt->fetch();
        return $has;
    }

    private function normalizeCodeInput(string $input): array
    {
        $input = trim($input);
        if ($input === '') {
            return ['', ''];
        }
        if (preg_match('/^[a-fA-F0-9]{64}$/', $input)) {
            return ['code_hash', strtolower($input)];
        }
        if ($this->hasCodeColumn()) {
            return ['code', strtoupper($input)];
        }
        return ['code_hash', hash('sha256', strtoupper($input))];
    }

    public function create(array $data): int
    {
        if ($this->hasCodeColumn()) {
            $stmt = $this->db->prepare('INSERT INTO referrals (code, code_hash, set_balance, created_by, used_by, used_at, created_at, updated_at) VALUES (?, ?, ?, ?, NULL, NULL, NOW(), NOW())');
            $stmt->execute([$data['code'] ?? null, $data['code_hash'], $data['set_balance'], $data['created_by']]);
        } else {
            $stmt = $this->db->prepare('INSERT INTO referrals (code_hash, set_balance, created_by, used_by, used_at, created_at, updated_at) VALUES (?, ?, ?, NULL, NULL, NOW(), NOW())');
            $stmt->execute([$data['code_hash'], $data['set_balance'], $data['created_by']]);
        }
        return (int)$this->db->lastInsertId();
    }

    public function findByCodeHash(string $hash): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM referrals WHERE code_hash = ? LIMIT 1');
        $stmt->execute([$hash]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findByInput(string $input): ?array
    {
        [$field, $value] = $this->normalizeCodeInput($input);
        if ($field === '' || $value === '') {
            return null;
        }
        $stmt = $this->db->prepare("SELECT * FROM referrals WHERE {$field} = ? LIMIT 1");
        $stmt->execute([$value]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function redeemForUser(string $input, int $userId): ?float
    {
        [$field, $value] = $this->normalizeCodeInput($input);
        if ($field === '' || $value === '') {
            return null;
        }

        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare("SELECT * FROM referrals WHERE {$field} = ? LIMIT 1 FOR UPDATE");
            $stmt->execute([$value]);
            $row = $stmt->fetch();
            if (!$row || !empty($row['used_by'])) {
                $this->db->rollBack();
                return null;
            }

            $upd = $this->db->prepare('UPDATE referrals SET used_by = ?, used_at = NOW() WHERE id = ? AND used_by IS NULL');
            $upd->execute([$userId, (int)$row['id']]);
            if ($upd->rowCount() < 1) {
                $this->db->rollBack();
                return null;
            }

            $balance = (float)($row['set_balance'] ?? 0);
            $u = $this->db->prepare('UPDATE users SET balance = balance + ?, updated_at = NOW() WHERE id = ?');
            $u->execute([$balance, $userId]);

            $this->db->commit();
            return $balance;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            return null;
        }
    }

    public function markUsed(int $id, int $userId): bool
    {
        $stmt = $this->db->prepare('UPDATE referrals SET used_by = ?, used_at = NOW() WHERE id = ? AND used_by IS NULL');
        return $stmt->execute([$userId, $id]);
    }

    public function listLatest(int $limit = 50): array
    {
        $stmt = $this->db->prepare('SELECT r.*, u.username AS created_by_name FROM referrals r LEFT JOIN users u ON u.id = r.created_by ORDER BY r.id DESC LIMIT ?');
        $stmt->bindValue(1, $limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
}
