<?php
class PlanModel {
    public static function all(): array {
        return db()->query("SELECT * FROM plans WHERE is_active=1 ORDER BY sort_order")->fetchAll();
    }

    public static function find(int $id): ?array {
        $stmt = db()->prepare("SELECT * FROM plans WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public static function findBySlug(string $slug): ?array {
        $stmt = db()->prepare("SELECT * FROM plans WHERE slug = ? LIMIT 1");
        $stmt->execute([$slug]);
        return $stmt->fetch() ?: null;
    }

    public static function subscribe(int $tenantId, int $planId, string $cycle = 'monthly'): int {
        // Cancel existing subscriptions
        db()->prepare("UPDATE subscriptions SET status='cancelled',cancelled_at=NOW() WHERE tenant_id=? AND status='active'")
             ->execute([$tenantId]);
        $plan   = self::find($planId);
        $price  = self::calculatePrice($plan, $cycle);
        $start  = date('Y-m-d H:i:s');
        $end    = self::calculateEndDate($cycle);
        $stmt = db()->prepare(
            "INSERT INTO subscriptions (tenant_id,plan_id,status,billing_cycle,amount,started_at,current_period_start,current_period_end)
             VALUES (?,?,'active',?,?,?,?,?)"
        );
        $stmt->execute([$tenantId,$planId,$cycle,$price,$start,$start,$end]);
        // Update tenant status to active
        db()->prepare("UPDATE tenants SET status='active' WHERE id=?")->execute([$tenantId]);
        return (int)db()->lastInsertId();
    }

    public static function calculatePrice(array $plan, string $cycle): float {
        $basePrice = $plan['price_monthly'];
        switch($cycle) {
            case '3months':
                return $basePrice * 3;
            case '6months':
                return $basePrice * 6;
            case 'yearly':
                return $plan['price_yearly'] ?? ($basePrice * 12);
            default:
                return $basePrice;
        }
    }

    private static function calculateEndDate(string $cycle): string {
        $start = new DateTime();
        switch($cycle) {
            case '3months':
                $start->modify('+3 months');
                break;
            case '6months':
                $start->modify('+6 months');
                break;
            case 'yearly':
                $start->modify('+1 year');
                break;
            default:
                $start->modify('+1 month');
                break;
        }
        return $start->format('Y-m-d H:i:s');
    }

    public static function getActiveSubscription(int $tenantId): ?array {
        $stmt = db()->prepare(
            "SELECT s.*,p.name as plan_name,p.slug as plan_slug,p.slug as slug,p.max_products,p.max_users,
                    p.max_locations,p.storage_mb,p.features,p.price_monthly,p.price_yearly
             FROM subscriptions s JOIN plans p ON p.id=s.plan_id
             WHERE s.tenant_id=? AND s.status='active' ORDER BY s.id DESC LIMIT 1"
        );
        $stmt->execute([$tenantId]);
        return $stmt->fetch() ?: null;
    }

    public static function getFreePlan(): ?array {
        return self::findBySlug('free');
    }

    public static function createPaystackSubscription(int $tenantId, int $planId, string $cycle, array $paymentData): int {
        // Cancel existing subscriptions
        db()->prepare("UPDATE subscriptions SET status='cancelled',cancelled_at=NOW() WHERE tenant_id=? AND status='active'")
             ->execute([$tenantId]);

        $plan = self::find($planId);
        $price = self::calculatePrice($plan, $cycle);
        $start = date('Y-m-d H:i:s');
        $end = self::calculateEndDate($cycle);

        $stmt = db()->prepare(
            "INSERT INTO subscriptions (tenant_id,plan_id,status,billing_cycle,amount,started_at,current_period_start,current_period_end,
             cancelled_at,payment_method,paystack_reference,paystack_subscription_code,paystack_customer_code)
             VALUES (?,?,'active',?,?,?,?,?,NULL,?,?,?,?)"
        );
        $stmt->execute([
            $tenantId,
            $planId,
            $cycle,
            $price,
            $start,
            $start,
            $end,
            'paystack',
            $paymentData['reference'] ?? null,
            $paymentData['subscription_code'] ?? null,
            $paymentData['customer_code'] ?? null
        ]);

        // Update tenant status to active
        db()->prepare("UPDATE tenants SET status='active' WHERE id=?")->execute([$tenantId]);

        return (int)db()->lastInsertId();
    }

    public static function updateSubscriptionPayment(int $subscriptionId, array $paymentData): bool {
        $stmt = db()->prepare(
            "UPDATE subscriptions SET 
             paystack_reference=?, paystack_subscription_code=?, paystack_customer_code=?, 
             payment_method='paystack', status='active'
             WHERE id=?"
        );
        return $stmt->execute([
            $paymentData['reference'] ?? null,
            $paymentData['subscription_code'] ?? null,
            $paymentData['customer_code'] ?? null,
            $subscriptionId
        ]);
    }

    public static function cancelSubscription(int $tenantId): bool {
        return db()->prepare("UPDATE subscriptions SET status='cancelled',cancelled_at=NOW() WHERE tenant_id=? AND status='active'")
                    ->execute([$tenantId]);
    }

    public static function createManualSubscription(int $tenantId, int $planId, string $cycle, float $amount): int {
        // Cancel existing subscriptions
        db()->prepare("UPDATE subscriptions SET status='cancelled',cancelled_at=NOW() WHERE tenant_id=? AND status='active'")
             ->execute([$tenantId]);

        $plan = self::find($planId);
        $start = date('Y-m-d H:i:s');
        $end = self::calculateEndDate($cycle);

        $stmt = db()->prepare(
            "INSERT INTO subscriptions (tenant_id,plan_id,status,billing_cycle,amount,started_at,current_period_start,current_period_end,payment_method)
             VALUES (?,?,'pending',?,?,?,?,?,?)"
        );
        $stmt->execute([
            $tenantId, $planId, $cycle, $amount, $start, $start, $end, 'manual'
        ]);

        return (int)db()->lastInsertId();
    }

    public static function updatePlanPaystackCodes(int $planId, string $planCode): bool {
        return db()->prepare("UPDATE plans SET paystack_plan_code=? WHERE id=?")
                    ->execute([$planCode, $planId]);
    }

    public static function getPlansWithPaystackCodes(): array {
        return db()->query("SELECT * FROM plans WHERE is_active=1 ORDER BY sort_order")->fetchAll();
    }
}
