<?php
/**
 * OpenVPN | L2TP Credentials Management
 * مدیریت اکانت‌های OpenVPN | L2TP با قفل‌گذاری و FIFO
 */

namespace GemAcc;

use PDO;

class Credentials
{
    private PDO $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * افزودن اکانت جدید
     * @param string $email ایمیل
     * @param string $password پسورد
     * @param string $duration '1M' یا '1Y'
     * @param int $createdBy آیدی ادمین
     */
    public function add(string $email, string $password, string $duration, int $createdBy, string $trafficType = 'unlimited', ?int $categoryId = null, ?int $trafficLimitGb = null, string $protocol = 'openvpn_l2tp', ?int $maxSeatsOverride = null): int
    {
        // Important: do not reuse the same named placeholder more than once.
        // Some hosts run PDO/MySQL with native prepared statements and will throw
        // SQLSTATE[HY093] when a placeholder such as :email or :duration is repeated.
        $stmt = $this->pdo->prepare("
            INSERT INTO credentials (category_id, email, username, password, duration, months, max_seats, protocol, traffic_type, traffic_limit_gb, used_seats, sellable_until, is_active, is_sold, status, sold_to_user_id, order_id, assigned_user_id, assigned_order_id, sold_at, delivered_at, created_by)
            VALUES (:category_id, :email, :username, :password, :duration, :months, :max_seats, :protocol, :traffic_type, :traffic_limit_gb, 0, DATE_ADD(NOW(), INTERVAL 20 YEAR), 1, 0, 'available', NULL, NULL, NULL, NULL, NULL, NULL, :created_by)
        ");
        $trafficType = in_array($trafficType, ['limited','unlimited'], true) ? $trafficType : 'unlimited';
        $protocolRaw = strtolower(trim($protocol));
        $protocol = in_array($protocolRaw, ['windscribe','expressvpn','v2ray'], true) ? $protocolRaw : 'openvpn_l2tp';
        $maxSeats = max(1, min(100, (int)($maxSeatsOverride ?? ($protocol === 'windscribe' ? 15 : 1))));
        $months = ($duration === '1Y') ? 12 : (preg_match('/^(\d+)M$/', $duration, $m) ? (int)$m[1] : 1);
        $stmt->execute([
            ':category_id' => $categoryId,
            ':email' => $email,
            ':username' => $email,
            ':password' => $password,
            ':duration' => $duration,
            ':months' => $months,
            ':max_seats' => $maxSeats,
            ':protocol' => $protocol,
            ':traffic_type' => $trafficType,
            ':traffic_limit_gb' => $trafficType === 'limited' ? $trafficLimitGb : null,
            ':created_by' => $createdBy,
        ]);
        return (int)$this->pdo->lastInsertId();
    }
    
    /**
     * تخصیص صندلی با قفل‌گذاری (FIFO)
     * @param string $duration '1M' یا '1Y'
     * @param int $requestedSeats تعداد صندلی درخواستی
     * @return array|null اطلاعات اکانت یا null اگر موجود نباشد
     */
    public function allocateSeats(string $duration, int $requestedSeats): ?array
    {
        $requestedSeats = max(1, (int)$requestedSeats);
        $serviceType = 'openvpn_l2tp';
        try {
            $st = $this->pdo->prepare("SELECT service_type FROM plan_categories WHERE code=? LIMIT 1");
            $st->execute([$duration]);
            $svcRaw = strtolower((string)($st->fetchColumn() ?: 'openvpn_l2tp'));
            $serviceType = in_array($svcRaw, ['windscribe','expressvpn','v2ray'], true) ? $svcRaw : 'openvpn_l2tp';
        } catch (\Throwable $e) {
            if (stripos($duration, 'WS_') === 0) $serviceType = 'windscribe';
            if (stripos($duration, 'EXP_') === 0) $serviceType = 'expressvpn';
            if (stripos($duration, 'V2_') === 0) $serviceType = 'v2ray';
        }

        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare("
                SELECT id, email, username, password, config_text, qr_code_file_id, qr_code_path, traffic_type, traffic_limit_gb, max_seats, used_seats, protocol
                FROM credentials
                WHERE duration = :duration
                  AND LOWER(COALESCE(protocol,'openvpn_l2tp')) = :protocol
                  AND is_active = 1
                  AND COALESCE(sales_paused,0)=0
                  AND COALESCE(used_seats,0) + :requested <= COALESCE(max_seats,1)
                  AND LOWER(COALESCE(status,'available')) IN ('available','ready','partial')
                ORDER BY created_at ASC
                LIMIT 1
                FOR UPDATE
            ");
            $stmt->execute([':duration'=>$duration, ':protocol'=>$serviceType, ':requested'=>$requestedSeats]);
            $credential = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$credential) { $this->pdo->rollBack(); return null; }
            $maxSeats = max(1, (int)($credential['max_seats'] ?? 1));
            $newUsed = (int)($credential['used_seats'] ?? 0) + $requestedSeats;
            $isFull = $newUsed >= $maxSeats ? 1 : 0;
            $status = $isFull ? 'sold' : 'partial';
            $updateStmt = $this->pdo->prepare("UPDATE credentials SET used_seats=used_seats+?, is_sold=?, status=?, sold_to_user_id=COALESCE(sold_to_user_id,0), updated_at=NOW(), delivered_at=NOW() WHERE id=? AND is_active=1 AND COALESCE(sales_paused,0)=0 AND COALESCE(used_seats,0)+?<=COALESCE(max_seats,1)");
            $updateStmt->execute([$requestedSeats, $isFull, $status, $credential['id'], $requestedSeats]);
            if ($updateStmt->rowCount() === 0) { $this->pdo->rollBack(); return null; }
            $this->pdo->commit();
            return [
                'id'=>(int)$credential['id'],
                'email'=>$credential['email'],
                'username'=>$credential['username'] ?: $credential['email'],
                'password'=>$credential['password'],
                'config_text'=>$credential['config_text'] ?? null,
                'qr_code_file_id'=>$credential['qr_code_file_id'] ?? null,
                'qr_code_path'=>$credential['qr_code_path'] ?? null,
                'traffic_type'=>$credential['traffic_type'] ?? 'unlimited',
                'traffic_limit_gb'=>$credential['traffic_limit_gb'] ?? null,
                'used_seats'=>$newUsed,
                'max_seats'=>$maxSeats,
                'seats_allocated'=>$requestedSeats,
                'remaining'=>max(0, $maxSeats - $newUsed),
                'service_type'=>$serviceType,
                'protocol'=>$serviceType,
            ];
        } catch (\Exception $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack(); error_log("allocateSeats error: " . $e->getMessage()); return null;
        }
    }

    /**
     * بررسی موجودی برای یک duration
     */
    public function hasAvailableCredential(string $duration, int $seats): bool
    {
        $stmt = $this->pdo->prepare("
            SELECT COUNT(*) 
            FROM credentials 
            WHERE duration = :duration 
              AND is_active = 1 
              AND COALESCE(sales_paused,0)=0
              AND COALESCE(used_seats,0)+:seats<=COALESCE(max_seats,1)
              AND LOWER(COALESCE(status,'available')) IN ('available','ready','partial')
        ");
        
        $stmt->execute([
            ':duration' => $duration,
            ':seats' => max(1, $seats),
        ]);
        
        return (int)$stmt->fetchColumn() > 0;
    }
    
    /**
     * گرفتن لیست اکانت‌ها برای ادمین
     */
    public function getList(int $limit = 50, int $offset = 0): array
    {
        $stmt = $this->pdo->prepare("
            SELECT c.*, 
                   GREATEST(COALESCE(c.max_seats,1) - COALESCE(c.used_seats,0),0) as remaining_seats,
                   CASE WHEN c.is_active=1 AND COALESCE(c.sales_paused,0)=0 AND COALESCE(c.used_seats,0)<COALESCE(c.max_seats,1) AND LOWER(COALESCE(c.status,'available')) IN ('available','ready','partial') THEN 1 ELSE 0 END as is_sellable,
                   CASE WHEN COALESCE(c.used_seats,0)>0 OR COALESCE(c.is_sold,0)>0 OR LOWER(COALESCE(c.status,'available')) IN ('sold','delivered','assigned','used') THEN 1 ELSE 0 END as is_sold_calc,
                   DATEDIFF(c.sellable_until, NOW()) as days_left
            FROM credentials c
            WHERE COALESCE(c.deleted_at, '1970-01-01') = '1970-01-01'
              AND NOT (COALESCE(c.is_sold,0)=1 AND COALESCE(c.used_seats,0) >= COALESCE(c.max_seats,1))
              AND LOWER(COALESCE(c.status,'available')) NOT IN ('archived','removed','sold','delivered','assigned','used')
              AND COALESCE(c.archived_at, '1970-01-01') = '1970-01-01'
            ORDER BY COALESCE(c.sales_paused,0) ASC, is_sold_calc ASC, c.created_at DESC
            LIMIT :limit OFFSET :offset
        ");
        
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * گرفتن آمار کلی
     */
    public function getStats(): array
    {
        $stmt = $this->pdo->query("
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN is_active = 1 AND sellable_until >= NOW() AND LOWER(COALESCE(status,'available')) NOT IN ('archived','removed') THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN duration = '1M' THEN 1 ELSE 0 END) as monthly,
                SUM(CASE WHEN duration = '1Y' THEN 1 ELSE 0 END) as yearly,
                SUM(used_seats) as total_used_seats,
                SUM(max_seats - used_seats) as total_remaining_seats
            FROM credentials
        ");
        
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }
    
    /**
     * گرفتن اکانت‌هایی که نیاز به هشدار دارند
     */
    public function getCredentialsNeedingWarning(): array
    {
        $result = [];
        
        // هشدار 5 روزه (اکانت‌هایی که بیش از 5 روز از ایجادشان گذشته)
        $stmt = $this->pdo->query("
            SELECT * FROM credentials 
            WHERE DATEDIFF(NOW(), created_at) >= 5 
              AND warning_5d_sent = 0 
              AND is_active = 1
        ");
        $result['5_day'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // هشدار ظرفیت (بیش از 9 صندلی فروخته شده)
        $stmt = $this->pdo->query("
            SELECT * FROM credentials 
            WHERE used_seats >= 10 
              AND capacity_warn_sent = 0 
              AND is_active = 1
        ");
        $result['capacity'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return $result;
    }
    
    /**
     * علامت‌گذاری هشدار ارسال شده
     */
    public function markWarningSent(int $credentialId, string $type): bool
    {
        $column = $type === '5_day' ? 'warning_5d_sent' : 'capacity_warn_sent';
        
        $stmt = $this->pdo->prepare("UPDATE credentials SET {$column} = 1 WHERE id = ?");
        return $stmt->execute([$credentialId]);
    }
    
    /**
     * غیرفعال‌سازی اکانت
     */
    public function disable(int $id): bool
    {
        $stmt = $this->pdo->prepare("UPDATE credentials SET is_active = 0 WHERE id = ?");
        return $stmt->execute([$id]);
    }
    
    /**
     * فعال‌سازی اکانت
     */
    public function enable(int $id): bool
    {
        $stmt = $this->pdo->prepare("UPDATE credentials SET is_active = 1 WHERE id = ?");
        return $stmt->execute([$id]);
    }
    
    /**
     * حذف اکانت (فقط اگر استفاده نشده)
     */
    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM credentials WHERE id = ? AND used_seats = 0");
        return $stmt->execute([$id]) && $stmt->rowCount() > 0;
    }
    
    /**
     * گرفتن اکانت با آیدی
     */
    public function getById(int $id): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM credentials WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }
}

