<?php
/**
 * Subscription Manager
 * Version: 2.0
 * PHP: 7.4+
 */

declare(strict_types=1);

class SubscriptionManager {
    private static ?SubscriptionManager $instance = null;
    private Database $db;
    
    private function __construct() {
        $this->db = Database::getInstance();
        $this->initTables();
    }
    
    public static function getInstance(): SubscriptionManager {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Initialize subscription tables
     */
    private function initTables(): void {
        // Plans table
        $this->db->query("CREATE TABLE IF NOT EXISTS `subscription_plans` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(100) NOT NULL,
            `name_en` VARCHAR(100) NOT NULL,
            `duration_days` INT NOT NULL,
            `price` DECIMAL(10,2) NOT NULL,
            `features` JSON NULL,
            `is_active` BOOLEAN DEFAULT TRUE,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX `idx_active` (`is_active`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        
        // User subscriptions table
        $this->db->query("CREATE TABLE IF NOT EXISTS `user_subscriptions` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `plan_id` INT NOT NULL,
            `start_date` TIMESTAMP NULL,
            `end_date` TIMESTAMP NULL,
            `status` ENUM('pending', 'active', 'expired', 'cancelled') DEFAULT 'pending',
            `notified_expiry` TIMESTAMP NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX `idx_user` (`user_id`),
            INDEX `idx_status` (`status`),
            INDEX `idx_end_date` (`end_date`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        
        // Payment receipts table
        $this->db->query("CREATE TABLE IF NOT EXISTS `payment_receipts` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `plan_id` INT NOT NULL,
            `amount` DECIMAL(10,2) NOT NULL,
            `receipt_image` VARCHAR(255) NULL,
            `transaction_id` VARCHAR(100) NULL,
            `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
            `admin_note` TEXT NULL,
            `submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `reviewed_at` TIMESTAMP NULL,
            `reviewed_by` BIGINT NULL,
            INDEX `idx_user` (`user_id`),
            INDEX `idx_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        
        // Payment settings table
        $this->db->query("CREATE TABLE IF NOT EXISTS `payment_settings` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `card_number` VARCHAR(20) NOT NULL,
            `card_holder` VARCHAR(100) NOT NULL,
            `bank_name` VARCHAR(100) NULL,
            `is_active` BOOLEAN DEFAULT TRUE,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }
    
    /**
     * Check if user has active subscription
     */
    public function hasActiveSubscription(int $userId): bool {
        $sub = $this->db->fetchOne(
            "SELECT * FROM `user_subscriptions` 
             WHERE user_id = ? AND status = 'active' AND end_date > NOW()
             ORDER BY end_date DESC LIMIT 1",
            [$userId]
        );
        
        return $sub !== null;
    }
    
    /**
     * Get user subscription
     */
    public function getUserSubscription(int $userId): ?array {
        return $this->db->fetchOne(
            "SELECT us.*, sp.name, sp.name_en, sp.features 
             FROM `user_subscriptions` us
             JOIN `subscription_plans` sp ON us.plan_id = sp.id
             WHERE us.user_id = ? AND us.status = 'active' AND us.end_date > NOW()
             ORDER BY us.end_date DESC LIMIT 1",
            [$userId]
        );
    }
    
    /**
     * Get all plans
     */
    public function getAllPlans(): array {
        return $this->db->fetchAll(
            "SELECT * FROM `subscription_plans` WHERE is_active = 1 ORDER BY price ASC"
        );
    }
    
    /**
     * Get plan by ID
     */
    public function getPlan(int $planId): ?array {
        return $this->db->fetchOne(
            "SELECT * FROM `subscription_plans` WHERE id = ?",
            [$planId]
        );
    }
    
    /**
     * Create new plan
     */
    public function createPlan(array $data): bool {
        return $this->db->insert('subscription_plans', [
            'name' => $data['name'],
            'name_en' => $data['name_en'],
            'duration_days' => $data['duration_days'],
            'price' => $data['price'],
            'features' => json_encode($data['features'] ?? [])
        ]);
    }
    
    /**
     * Update plan
     */
    public function updatePlan(int $planId, array $data): bool {
        $updateData = [];
        
        if (isset($data['name'])) $updateData['name'] = $data['name'];
        if (isset($data['name_en'])) $updateData['name_en'] = $data['name_en'];
        if (isset($data['duration_days'])) $updateData['duration_days'] = $data['duration_days'];
        if (isset($data['price'])) $updateData['price'] = $data['price'];
        if (isset($data['features'])) $updateData['features'] = json_encode($data['features']);
        if (isset($data['is_active'])) $updateData['is_active'] = $data['is_active'];
        
        return $this->db->update('subscription_plans', $updateData, 'id = ?', [$planId]);
    }
    
    /**
     * Delete plan
     */
    public function deletePlan(int $planId): bool {
        return $this->db->update('subscription_plans', ['is_active' => 0], 'id = ?', [$planId]);
    }
    
    /**
     * Submit payment receipt
     */
    public function submitReceipt(int $userId, int $planId, string $receiptImage, ?string $transactionId = null): int {
        $plan = $this->getPlan($planId);
        
        if (!$plan) {
            return 0;
        }
        
        $this->db->insert('payment_receipts', [
            'user_id' => $userId,
            'plan_id' => $planId,
            'amount' => $plan['price'],
            'receipt_image' => $receiptImage,
            'transaction_id' => $transactionId,
            'status' => 'pending'
        ]);
        
        return (int)$this->db->insert_id;
    }
    
    /**
     * Get pending receipts
     */
    public function getPendingReceipts(int $limit = 10): array {
        return $this->db->fetchAll(
            "SELECT pr.*, sp.name as plan_name, sp.price, m.first_name, m.username
             FROM `payment_receipts` pr
             JOIN `subscription_plans` sp ON pr.plan_id = sp.id
             LEFT JOIN `members` m ON pr.user_id = m.id
             WHERE pr.status = 'pending'
             ORDER BY pr.submitted_at DESC
             LIMIT ?",
            [$limit]
        );
    }
    
    /**
     * Get receipt by ID
     */
    public function getReceipt(int $receiptId): ?array {
        return $this->db->fetchOne(
            "SELECT pr.*, sp.name as plan_name, sp.duration_days, sp.price
             FROM `payment_receipts` pr
             JOIN `subscription_plans` sp ON pr.plan_id = sp.id
             WHERE pr.id = ?",
            [$receiptId]
        );
    }
    
    /**
     * Approve receipt and activate subscription
     */
    public function approveReceipt(int $receiptId, int $adminId, ?string $note = null): bool {
        $receipt = $this->getReceipt($receiptId);
        
        if (!$receipt || $receipt['status'] !== 'pending') {
            return false;
        }
        
        // Update receipt status
        $this->db->update('payment_receipts', [
            'status' => 'approved',
            'reviewed_at' => date('Y-m-d H:i:s'),
            'reviewed_by' => $adminId,
            'admin_note' => $note
        ], 'id = ?', [$receiptId]);
        
        // Activate subscription
        $startDate = date('Y-m-d H:i:s');
        $endDate = date('Y-m-d H:i:s', strtotime("+{$receipt['duration_days']} days"));
        
        $this->db->insert('user_subscriptions', [
            'user_id' => $receipt['user_id'],
            'plan_id' => $receipt['plan_id'],
            'start_date' => $startDate,
            'end_date' => $endDate,
            'status' => 'active'
        ]);
        
        return true;
    }
    
    /**
     * Reject receipt
     */
    public function rejectReceipt(int $receiptId, int $adminId, string $reason): bool {
        $receipt = $this->getReceipt($receiptId);
        
        if (!$receipt || $receipt['status'] !== 'pending') {
            return false;
        }
        
        return $this->db->update('payment_receipts', [
            'status' => 'rejected',
            'reviewed_at' => date('Y-m-d H:i:s'),
            'reviewed_by' => $adminId,
            'admin_note' => $reason
        ], 'id = ?', [$receiptId]);
    }
    
    /**
     * Get payment settings
     */
    public function getPaymentSettings(): ?array {
        return $this->db->fetchOne(
            "SELECT * FROM `payment_settings` WHERE is_active = 1 ORDER BY id DESC LIMIT 1"
        );
    }
    
    /**
     * Update payment settings
     */
    public function updatePaymentSettings(array $data): bool {
        // Deactivate old settings
        $this->db->query("UPDATE `payment_settings` SET is_active = 0");
        
        // Insert new settings
        return $this->db->insert('payment_settings', [
            'card_number' => $data['card_number'],
            'card_holder' => $data['card_holder'],
            'bank_name' => $data['bank_name'] ?? null,
            'is_active' => 1
        ]);
    }
    
    /**
     * Check and expire subscriptions
     */
    public function checkExpiredSubscriptions(): int {
        $result = $this->db->query(
            "UPDATE `user_subscriptions` 
             SET status = 'expired' 
             WHERE status = 'active' AND end_date < NOW()"
        );
        
        return $this->db->affected_rows;
    }
    
    /**
     * Get subscription stats
     */
    public function getStats(): array {
        $stats = [];
        
        // Total active subscriptions
        $stats['active'] = $this->db->query(
            "SELECT COUNT(*) as count FROM `user_subscriptions` 
             WHERE status = 'active' AND end_date > NOW()"
        )->fetch_assoc()['count'];
        
        // Pending receipts
        $stats['pending'] = $this->db->query(
            "SELECT COUNT(*) as count FROM `payment_receipts` WHERE status = 'pending'"
        )->fetch_assoc()['count'];
        
        // Total revenue
        $stats['revenue'] = $this->db->query(
            "SELECT SUM(amount) as total FROM `payment_receipts` WHERE status = 'approved'"
        )->fetch_assoc()['total'] ?? 0;
        
        // Expiring soon (7 days)
        $stats['expiring_soon'] = $this->db->query(
            "SELECT COUNT(*) as count FROM `user_subscriptions` 
             WHERE status = 'active' AND end_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)"
        )->fetch_assoc()['count'];
        
        return $stats;
    }
    
    /**
     * Get user's payment history
     */
    public function getUserPaymentHistory(int $userId): array {
        return $this->db->fetchAll(
            "SELECT pr.*, sp.name as plan_name
             FROM `payment_receipts` pr
             JOIN `subscription_plans` sp ON pr.plan_id = sp.id
             WHERE pr.user_id = ?
             ORDER BY pr.submitted_at DESC",
            [$userId]
        );
    }
}
