<?php

declare(strict_types=1);

require_once __DIR__ . '/PlatformSettings.php';

/**
 * WorkspaceManager
 * 
 * Manages workspaces from super admin perspective
 */
class WorkspaceManager
{
    /**
     * Get all workspaces with pagination
     */
    public static function getWorkspaces(int $page = 1, int $perPage = 20, array $filters = []): array
    {
        $pdo = getPDO();
        $offset = ($page - 1) * $perPage;
        
        $where = ['1=1'];
        $params = [];

        if (!empty($filters['search'])) {
            $where[] = '(c.name LIKE ? OR c.username LIKE ? OR c.email LIKE ?)';
            $params[] = '%' . $filters['search'] . '%';
            $params[] = '%' . $filters['search'] . '%';
            $params[] = '%' . $filters['search'] . '%';
        }

        if (isset($filters['is_banned'])) {
            $where[] = 'c.is_banned = ?';
            $params[] = $filters['is_banned'];
        }

        if (!empty($filters['subscription_status'])) {
            $where[] = 'c.subscription_status = ?';
            $params[] = $filters['subscription_status'];
        }

        if (!empty($filters['plan_id'])) {
            $where[] = 'c.plan_id = ?';
            $params[] = $filters['plan_id'];
        }

        $whereClause = implode(' AND ', $where);

        // Get total count
        $countStmt = $pdo->prepare("SELECT COUNT(*) FROM companies c WHERE $whereClause");
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        // Get workspaces
        $sql = "
            SELECT c.*, 
                   sp.name as plan_name,
                   u.name as owner_name,
                   u.email as owner_email,
                   (SELECT COUNT(*) FROM workspace_members WHERE company_id = c.id) as member_count,
                   (SELECT COUNT(*) FROM clients WHERE company_id = c.id) as client_count
            FROM companies c
            LEFT JOIN subscription_plans sp ON c.plan_id = sp.id
            LEFT JOIN users u ON c.owner_id = u.id
            WHERE $whereClause
            ORDER BY c.created_at DESC
            LIMIT $perPage OFFSET $offset
        ";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $workspaces = $stmt->fetchAll();

        return [
            'workspaces' => $workspaces,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'total_pages' => ceil($total / $perPage),
        ];
    }

    /**
     * Get a single workspace with details
     */
    public static function getWorkspace(int $workspaceId): ?array
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            SELECT c.*, sp.name as plan_name, sp.limits as plan_limits,
                   u.name as owner_name, u.email as owner_email
            FROM companies c
            LEFT JOIN subscription_plans sp ON c.plan_id = sp.id
            LEFT JOIN users u ON c.owner_id = u.id
            WHERE c.id = ?
        ');
        $stmt->execute([$workspaceId]);
        $workspace = $stmt->fetch();

        if (!$workspace) {
            return null;
        }

        // Get members
        $stmt = $pdo->prepare('
            SELECT wm.*, u.name, u.email, wr.name as role_name
            FROM workspace_members wm
            JOIN users u ON wm.user_id = u.id
            LEFT JOIN workspace_roles wr ON wm.role_id = wr.id
            WHERE wm.company_id = ?
        ');
        $stmt->execute([$workspaceId]);
        $workspace['members'] = $stmt->fetchAll();

        // Get usage stats
        $workspace['usage'] = self::getWorkspaceUsage($workspaceId);

        return $workspace;
    }

    /**
     * Get workspace usage statistics
     */
    public static function getWorkspaceUsage(int $workspaceId): array
    {
        $pdo = getPDO();
        
        $usage = [];

        // Members
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM workspace_members WHERE company_id = ?');
        $stmt->execute([$workspaceId]);
        $usage['members'] = (int)$stmt->fetchColumn();

        // Clients
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM clients WHERE company_id = ?');
        $stmt->execute([$workspaceId]);
        $usage['clients'] = (int)$stmt->fetchColumn();

        // Templates
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM templates WHERE company_id = ?');
        $stmt->execute([$workspaceId]);
        $usage['templates'] = (int)$stmt->fetchColumn();

        // Proposals this month
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM proposals WHERE company_id = ? AND created_at >= DATE_FORMAT(NOW(), "%Y-%m-01")');
        $stmt->execute([$workspaceId]);
        $usage['proposals_this_month'] = (int)$stmt->fetchColumn();

        // Contracts this month
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM contracts WHERE company_id = ? AND created_at >= DATE_FORMAT(NOW(), "%Y-%m-01")');
        $stmt->execute([$workspaceId]);
        $usage['contracts_this_month'] = (int)$stmt->fetchColumn();

        // Invoices this month
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM invoices WHERE company_id = ? AND created_at >= DATE_FORMAT(NOW(), "%Y-%m-01")');
        $stmt->execute([$workspaceId]);
        $usage['invoices_this_month'] = (int)$stmt->fetchColumn();

        return $usage;
    }

    /**
     * Ban a workspace
     */
    public static function banWorkspace(int $workspaceId, string $reason = ''): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('
            UPDATE companies SET is_banned = 1, banned_at = NOW(), banned_reason = ?
            WHERE id = ?
        ');
        return $stmt->execute([$reason, $workspaceId]);
    }

    /**
     * Unban a workspace
     */
    public static function unbanWorkspace(int $workspaceId): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('
            UPDATE companies SET is_banned = 0, banned_at = NULL, banned_reason = NULL
            WHERE id = ?
        ');
        return $stmt->execute([$workspaceId]);
    }

    /**
     * Delete a workspace
     */
    public static function deleteWorkspace(int $workspaceId): bool
    {
        $pdo = getPDO();
        
        try {
            $pdo->beginTransaction();

            // Delete related data
            $tables = [
                'workspace_members', 'workspace_roles', 'workspace_invitations',
                'clients', 'proposals', 'contracts', 'invoices', 'templates',
                'modules', 'labels', 'user_company_access', 'workspace_activity_log'
            ];

            foreach ($tables as $table) {
                $stmt = $pdo->prepare("DELETE FROM $table WHERE company_id = ?");
                $stmt->execute([$workspaceId]);
            }

            // Delete workspace
            $stmt = $pdo->prepare('DELETE FROM companies WHERE id = ?');
            $stmt->execute([$workspaceId]);

            $pdo->commit();
            return true;
        } catch (Exception $e) {
            $pdo->rollBack();
            return false;
        }
    }

    /**
     * Update workspace subscription
     */
    public static function updateSubscription(int $workspaceId, int $planId, string $status, ?string $expiresAt = null): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('
            UPDATE companies 
            SET plan_id = ?, subscription_status = ?, subscription_expires_at = ?
            WHERE id = ?
        ');
        return $stmt->execute([$planId, $status, $expiresAt, $workspaceId]);
    }

    /**
     * Get workspace statistics
     */
    public static function getStats(): array
    {
        $pdo = getPDO();

        $stats = [];

        // Total workspaces
        $stmt = $pdo->query('SELECT COUNT(*) FROM companies');
        $stats['total_workspaces'] = (int)$stmt->fetchColumn();

        // Active workspaces
        $stmt = $pdo->query('SELECT COUNT(*) FROM companies WHERE is_banned = 0');
        $stats['active_workspaces'] = (int)$stmt->fetchColumn();

        // Banned workspaces
        $stmt = $pdo->query('SELECT COUNT(*) FROM companies WHERE is_banned = 1');
        $stats['banned_workspaces'] = (int)$stmt->fetchColumn();

        // By subscription status
        $stmt = $pdo->query('
            SELECT subscription_status, COUNT(*) as count 
            FROM companies 
            GROUP BY subscription_status
        ');
        $stats['by_status'] = [];
        foreach ($stmt->fetchAll() as $row) {
            $stats['by_status'][$row['subscription_status'] ?? 'none'] = (int)$row['count'];
        }

        // By plan
        $stmt = $pdo->query('
            SELECT sp.name, COUNT(c.id) as count 
            FROM companies c
            LEFT JOIN subscription_plans sp ON c.plan_id = sp.id
            GROUP BY c.plan_id, sp.name
        ');
        $stats['by_plan'] = [];
        foreach ($stmt->fetchAll() as $row) {
            $stats['by_plan'][$row['name'] ?? 'No Plan'] = (int)$row['count'];
        }

        // Created this month
        $stmt = $pdo->query('SELECT COUNT(*) FROM companies WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)');
        $stats['created_this_month'] = (int)$stmt->fetchColumn();

        // Expiring soon (next 7 days)
        $stmt = $pdo->query('
            SELECT COUNT(*) FROM companies 
            WHERE subscription_expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)
        ');
        $stats['expiring_soon'] = (int)$stmt->fetchColumn();

        return $stats;
    }

    /**
     * Get billing summary
     */
    public static function getBillingSummary(): array
    {
        $pdo = getPDO();

        $summary = [];

        // Paid workspaces
        $stmt = $pdo->query("SELECT COUNT(*) FROM companies WHERE subscription_status = 'active' AND plan_id IS NOT NULL");
        $summary['paid_workspaces'] = (int)$stmt->fetchColumn();

        // Trial workspaces
        $stmt = $pdo->query("SELECT COUNT(*) FROM companies WHERE subscription_status = 'trial'");
        $summary['trial_workspaces'] = (int)$stmt->fetchColumn();

        // Monthly revenue estimate (multi-currency, split by billing cycle)
        $usdMonthlyNative = 0.0;
        $usdYearlyNative = 0.0;
        $inrMonthlyNative = 0.0;
        $inrYearlyNative = 0.0;

        $usdMonthlyWorkspaces = 0;
        $usdYearlyWorkspaces = 0;
        $inrMonthlyWorkspaces = 0;
        $inrYearlyWorkspaces = 0;

        $stmt = $pdo->query(
            "SELECT c.preferred_currency, c.billing_cycle,
                    sp.price_monthly, sp.price_yearly,
                    sp.price_monthly_inr, sp.price_yearly_inr
             FROM companies c
             JOIN subscription_plans sp ON c.plan_id = sp.id
             WHERE c.subscription_status = 'active'"
        );

        $usdToInrRate = (float)PlatformSettings::get('usd_to_inr_rate', 83.0);
        if ($usdToInrRate <= 0) {
            $usdToInrRate = 83.0;
        }

        foreach ($stmt->fetchAll() as $row) {
            $currency = strtoupper((string)($row['preferred_currency'] ?? 'USD'));
            $cycle = strtolower((string)($row['billing_cycle'] ?? 'monthly')) === 'yearly' ? 'yearly' : 'monthly';

            $priceMonthlyUsd = (float)($row['price_monthly'] ?? 0);
            $priceYearlyUsd = (float)($row['price_yearly'] ?? 0);

            $priceMonthlyInr = $row['price_monthly_inr'] !== null
                ? (float)$row['price_monthly_inr']
                : $priceMonthlyUsd * $usdToInrRate;

            $priceYearlyInr = $row['price_yearly_inr'] !== null
                ? (float)$row['price_yearly_inr']
                : $priceYearlyUsd * $usdToInrRate;

            if ($currency === 'INR') {
                if ($cycle === 'yearly') {
                    $inrYearlyNative += $priceYearlyInr;
                    $inrYearlyWorkspaces++;
                } else {
                    $inrMonthlyNative += $priceMonthlyInr;
                    $inrMonthlyWorkspaces++;
                }
            } else {
                if ($cycle === 'yearly') {
                    $usdYearlyNative += $priceYearlyUsd;
                    $usdYearlyWorkspaces++;
                } else {
                    $usdMonthlyNative += $priceMonthlyUsd;
                    $usdMonthlyWorkspaces++;
                }
            }
        }

        $rate = $usdToInrRate;

        // Monthlyized bases (for total monthly revenue estimates)
        $usdBaseMonthlyized = $usdMonthlyNative + ($usdYearlyNative / 12.0);
        $inrBaseMonthlyized = $inrMonthlyNative + ($inrYearlyNative / 12.0);

        $monthlyRevenueUsd = $usdBaseMonthlyized + ($inrBaseMonthlyized / $rate);
        $monthlyRevenueInr = $inrBaseMonthlyized + ($usdBaseMonthlyized * $rate);

        // Backwards-compatible field (used by existing templates)
        $summary['monthly_revenue'] = $monthlyRevenueUsd;
        $summary['monthly_revenue_usd'] = $monthlyRevenueUsd;
        $summary['monthly_revenue_inr'] = $monthlyRevenueInr;

        // Component breakdowns for each view
        // USD view: how much comes from USD-native vs INR-native workspaces
        $summary['monthly_usd_base_native'] = $usdBaseMonthlyized;                // native USD
        $summary['monthly_usd_from_inr_converted'] = $inrBaseMonthlyized / $rate; // INR -> USD

        // INR view: how much comes from INR-native vs USD-native workspaces
        $summary['monthly_inr_base_native'] = $inrBaseMonthlyized;                // native INR
        $summary['monthly_inr_from_usd_converted'] = $usdBaseMonthlyized * $rate; // USD -> INR

        // Native currency breakdown (raw plan prices, not monthlyized)
        $summary['usd_monthly_workspaces'] = $usdMonthlyWorkspaces;
        $summary['usd_yearly_workspaces'] = $usdYearlyWorkspaces;
        $summary['inr_monthly_workspaces'] = $inrMonthlyWorkspaces;
        $summary['inr_yearly_workspaces'] = $inrYearlyWorkspaces;

        $summary['usd_monthly_revenue_native'] = $usdMonthlyNative;
        $summary['usd_yearly_revenue_native'] = $usdYearlyNative;
        $summary['inr_monthly_revenue_native'] = $inrMonthlyNative;
        $summary['inr_yearly_revenue_native'] = $inrYearlyNative;

        $summary['usd_workspaces'] = $usdMonthlyWorkspaces + $usdYearlyWorkspaces;
        $summary['inr_workspaces'] = $inrMonthlyWorkspaces + $inrYearlyWorkspaces;
        $summary['usd_revenue_native'] = $usdMonthlyNative + $usdYearlyNative;
        $summary['inr_revenue_native'] = $inrMonthlyNative + $inrYearlyNative;

        $summary['monthly_workspaces'] = $usdMonthlyWorkspaces + $inrMonthlyWorkspaces;
        $summary['yearly_workspaces'] = $usdYearlyWorkspaces + $inrYearlyWorkspaces;

        // Renewals this week
        $stmt = $pdo->query(
            "SELECT COUNT(*) FROM companies
             WHERE subscription_expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)
             AND subscription_status = 'active'"
        );
        $summary['renewals_this_week'] = (int)$stmt->fetchColumn();

        // Renewals this month
        $stmt = $pdo->query(
            "SELECT COUNT(*) FROM companies
             WHERE subscription_expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)
             AND subscription_status = 'active'"
        );
        $summary['renewals_this_month'] = (int)$stmt->fetchColumn();

        return $summary;
    }
}
