<?php
class FinancialAnalytics {
    private $conn;
    
    public function __construct($conn) {
        $this->conn = $conn;
    }

    // Premium Optimization
    public function analyzePremiumPerformance($corporate_id, $period = 12) {
        $sql = "SELECT 
                    MONTH(debit_date) as month,
                    YEAR(debit_date) as year,
                    SUM(amount) as premium_amount,
                    COUNT(*) as policy_count,
                    SUM(allocated_amount)/SUM(amount) as collection_ratio
                FROM premium_debit_notes 
                WHERE corp_id = ? 
                AND debit_date >= DATE_SUB(NOW(), INTERVAL ? MONTH)
                GROUP BY YEAR(debit_date), MONTH(debit_date)
                ORDER BY year, month";

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('si', $corporate_id, $period);
        $stmt->execute();
        $result = $stmt->get_result();
        
        return $this->calculatePremiumTrends($result->fetch_all(MYSQLI_ASSOC));
    }

    // Loss Ratio Analysis 
    public function calculateLossRatio($corporate_id, $period = 12) {
        $sql = "SELECT 
                    c.corp_id,
                    SUM(b.invoiced_amount) as claims_amount,
                    (SELECT SUM(amount) 
                     FROM premium_debit_notes 
                     WHERE corp_id = c.corp_id 
                     AND debit_date >= DATE_SUB(NOW(), INTERVAL ? MONTH)) as premium_amount
                FROM bills b
                JOIN corporate c ON b.corp_id = c.corp_id
                WHERE c.corp_id = ?
                AND b.invoice_date >= DATE_SUB(NOW(), INTERVAL ? MONTH)
                GROUP BY c.corp_id";

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('isi', $period, $corporate_id, $period);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return [
            'loss_ratio' => ($result['claims_amount'] / $result['premium_amount']) * 100,
            'claims_amount' => $result['claims_amount'],
            'premium_amount' => $result['premium_amount']
        ];
    }

    // Revenue Forecasting
    public function forecastRevenue($corporate_id, $months_ahead = 3) {
        // Get historical data
        $sql = "SELECT 
                    MONTH(debit_date) as month,
                    YEAR(debit_date) as year,
                    SUM(amount) as revenue
                FROM premium_debit_notes
                WHERE corp_id = ?
                AND debit_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
                GROUP BY YEAR(debit_date), MONTH(debit_date)
                ORDER BY year, month";

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $corporate_id);
        $stmt->execute();
        $historical_data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

        return $this->calculateRevenueForecast($historical_data, $months_ahead);
    }

    // Underwriting Risk Assessment
    public function assessUnderwritingRisk($corporate_id) {
        $metrics = [
            'loss_ratio' => $this->calculateLossRatio($corporate_id),
            'claim_frequency' => $this->analyzeClaimFrequency($corporate_id),
            'payment_history' => $this->analyzePaymentHistory($corporate_id),
            'member_demographics' => $this->analyzeMemberDemographics($corporate_id)
        ];

        return $this->calculateRiskScore($metrics);
    }

    private function analyzeClaimFrequency($corporate_id) {
        $sql = "SELECT 
                    COUNT(*) as claim_count,
                    COUNT(DISTINCT member_no) as member_count,
                    AVG(invoiced_amount) as avg_claim_amount
                FROM bills
                WHERE corp_id = ?
                AND invoice_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)";

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $corporate_id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    private function analyzePaymentHistory($corporate_id) {
        $sql = "SELECT 
                    COUNT(*) as total_debits,
                    SUM(CASE WHEN status = 'ALLOCATED' THEN 1 ELSE 0 END) as paid_debits,
                    AVG(DATEDIFF(
                        (SELECT MIN(created_at) FROM receipt_allocations ra WHERE ra.debit_id = pdn.debit_id),
                        pdn.debit_date
                    )) as avg_payment_days
                FROM premium_debit_notes pdn
                WHERE corp_id = ?
                AND debit_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)";

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $corporate_id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    private function calculateRiskScore($metrics) {
        $risk_score = 0;
        $weights = [
            'loss_ratio' => 0.4,
            'claim_frequency' => 0.3,
            'payment_history' => 0.2,
            'member_demographics' => 0.1
        ];

        // Calculate weighted risk score
        foreach ($metrics as $metric => $value) {
            $risk_score += $this->normalizeMetric($value) * $weights[$metric];
        }

        return [
            'risk_score' => $risk_score,
            'risk_level' => $this->getRiskLevel($risk_score),
            'metrics' => $metrics
        ];
    }

    private function getRiskLevel($score) {
        if ($score < 0.3) return 'LOW';
        if ($score < 0.7) return 'MEDIUM';
        return 'HIGH';
    }
}
?>