<?php
session_start();
include 'layouts/dbconn.php';

// Check authentication and permissions
if (!isset($_SESSION['user_id']) || $_SESSION['role_id'] !== 1) {
    http_response_code(403);
    echo json_encode(['success' => false, 'error' => 'Insufficient permissions. Not Authorized.']);
    exit;
}

header('Content-Type: application/json');

try {
    // Get parameters
    $corp_id = $_GET['corp_id'] ?? '';
    $provider_id = $_GET['provider_id'] ?? '';
    $service_id = $_GET['service_id'] ?? '';
    $status = $_GET['status'] ?? '';
    $aging_bracket = $_GET['aging_bracket'] ?? '';
    $date_from = $_GET['date_from'] ?? '';
    $date_to = $_GET['date_to'] ?? '';
    $report_type = $_GET['report_type'] ?? 'summary';
    $member_no = $_GET['member_no'] ?? '';

    // Build WHERE conditions with BINARY for string comparisons to avoid collation issues
    $whereConditions = ["b.invoice_date >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR)"];
    $params = [];
    $types = '';

    if (!empty($corp_id)) {
        $whereConditions[] = "BINARY b.corp_id = BINARY ?";
        $params[] = $corp_id;
        $types .= 's';
    }

    if (!empty($provider_id)) {
        $whereConditions[] = "b.provider = ?";
        $params[] = $provider_id;
        $types .= 'i';
    }

    if (!empty($service_id)) {
        $whereConditions[] = "b.service = ?";
        $params[] = $service_id;
        $types .= 'i';
    }

    if (!empty($member_no)) {
        $whereConditions[] = "BINARY b.member_no = BINARY ?";
        $params[] = $member_no;
        $types .= 's';
    }

    if (!empty($date_from)) {
        $whereConditions[] = "b.invoice_date >= ?";
        $params[] = $date_from;
        $types .= 's';
    }

    if (!empty($date_to)) {
        $whereConditions[] = "b.invoice_date <= ?";
        $params[] = $date_to;
        $types .= 's';
    }

    $whereClause = "WHERE " . implode(" AND ", $whereConditions);

    // Different query types
    if ($report_type === 'summary') {
        // Direct summary query using BINARY joins to avoid collation issues
        $summary_sql = "
            SELECT 
                COUNT(*) as total_claims,
                SUM(COALESCE(b.invoiced_amount, 0)) as total_invoiced,
                SUM(COALESCE(b.amount_payable, 0)) as total_payable,
                SUM(CASE WHEN b.paid = 1 THEN 1 ELSE 0 END) as paid_claims,
                SUM(CASE WHEN b.paid = 1 THEN COALESCE(b.amount_payable, 0) ELSE 0 END) as paid_amount,
                
                -- Settlement days calculation
                AVG(CASE 
                    WHEN b.paid = 1 AND b.voucher_date IS NOT NULL THEN 
                        DATEDIFF(b.voucher_date, b.invoice_date)
                    WHEN b.paid = 1 AND b.voucher_date IS NULL THEN 
                        DATEDIFF(CURDATE(), b.invoice_date)
                    ELSE NULL
                END) as avg_settlement_days,
                
                -- Aging brackets based on current status
                SUM(CASE 
                    WHEN b.paid = 1 THEN 0
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 30 THEN 1 
                    ELSE 0 
                END) as bracket_0_30,
                
                SUM(CASE 
                    WHEN b.paid = 1 THEN 0
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 31 AND 60 THEN 1 
                    ELSE 0 
                END) as bracket_31_60,
                
                SUM(CASE 
                    WHEN b.paid = 1 THEN 0
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 61 AND 90 THEN 1 
                    ELSE 0 
                END) as bracket_61_90,
                
                SUM(CASE 
                    WHEN b.paid = 1 THEN 0
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 91 AND 120 THEN 1 
                    ELSE 0 
                END) as bracket_91_120,
                
                SUM(CASE 
                    WHEN b.paid = 1 THEN 0
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) > 120 THEN 1 
                    ELSE 0 
                END) as bracket_over_120,
                
                -- Status breakdown
                SUM(CASE WHEN b.paid = 1 THEN 1 ELSE 0 END) as status_paid,
                SUM(CASE WHEN COALESCE(b.vet_status, -1) = 0 THEN 1 ELSE 0 END) as status_rejected,
                SUM(CASE WHEN b.date_received IS NULL THEN 1 ELSE 0 END) as status_not_received,
                SUM(CASE WHEN b.vet_status IS NULL AND b.date_received IS NOT NULL THEN 1 ELSE 0 END) as status_pending_vetting,
                SUM(CASE WHEN COALESCE(b.vet_status, -1) = 1 AND COALESCE(b.paid, 0) = 0 THEN 1 ELSE 0 END) as status_approved_unpaid
                
            FROM bills b
            $whereClause";

        // Apply status filter if specified
        if (!empty($status)) {
            switch ($status) {
                case 'PAID':
                    $summary_sql .= " AND b.paid = 1";
                    break;
                case 'REJECTED':
                    $summary_sql .= " AND COALESCE(b.vet_status, -1) = 0";
                    break;
                case 'NOT_RECEIVED':
                    $summary_sql .= " AND b.date_received IS NULL";
                    break;
                case 'PENDING_VETTING':
                    $summary_sql .= " AND b.vet_status IS NULL AND b.date_received IS NOT NULL";
                    break;
                case 'APPROVED_UNPAID':
                    $summary_sql .= " AND COALESCE(b.vet_status, -1) = 1 AND COALESCE(b.paid, 0) = 0";
                    break;
            }
        }

        // Apply aging bracket filter if specified
        if (!empty($aging_bracket)) {
            switch ($aging_bracket) {
                case 'SETTLED':
                    $summary_sql .= " AND b.paid = 1";
                    break;
                case '0-30 DAYS':
                    $summary_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 30";
                    break;
                case '31-60 DAYS':
                    $summary_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 31 AND 60";
                    break;
                case '61-90 DAYS':
                    $summary_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 61 AND 90";
                    break;
                case '91-120 DAYS':
                    $summary_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 91 AND 120";
                    break;
                case 'OVER 120 DAYS':
                    $summary_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) > 120";
                    break;
            }
        }

        if (!empty($params)) {
            $stmt = $conn->prepare($summary_sql);
            if (!$stmt) {
                throw new Exception("Failed to prepare summary statement: " . $conn->error);
            }
            $stmt->bind_param($types, ...$params);
            if (!$stmt->execute()) {
                throw new Exception("Failed to execute summary query: " . $stmt->error);
            }
            $result = $stmt->get_result();
        } else {
            $result = $conn->query($summary_sql);
            if (!$result) {
                throw new Exception("Failed to execute summary query: " . $conn->error);
            }
        }

        $summary = $result->fetch_assoc();
        if (!$summary) {
            $summary = [
                'total_claims' => 0,
                'total_invoiced' => 0,
                'total_payable' => 0,
                'paid_claims' => 0,
                'paid_amount' => 0,
                'avg_settlement_days' => 0,
                'bracket_0_30' => 0,
                'bracket_31_60' => 0,
                'bracket_61_90' => 0,
                'bracket_91_120' => 0,
                'bracket_over_120' => 0,
                'status_paid' => 0,
                'status_rejected' => 0,
                'status_not_received' => 0,
                'status_pending_vetting' => 0,
                'status_approved_unpaid' => 0
            ];
        }

        // Get corporate breakdown if no specific corporate is selected
        $corporate_breakdown = [];
        if (empty($corp_id)) {
            $corp_sql = "
                SELECT 
                    b.corp_id,
                    COALESCE(c.corporate, 'Unknown Corporate') as corporate,
                    COUNT(*) as claim_count,
                    SUM(COALESCE(b.invoiced_amount, 0)) as total_invoiced,
                    SUM(COALESCE(b.amount_payable, 0)) as total_payable,
                    AVG(CASE 
                        WHEN b.paid = 1 AND b.voucher_date IS NOT NULL THEN 
                            DATEDIFF(b.voucher_date, b.invoice_date)
                        WHEN b.paid = 1 AND b.voucher_date IS NULL THEN 
                            DATEDIFF(CURDATE(), b.invoice_date)
                        ELSE NULL
                    END) as avg_settlement_days,
                    SUM(CASE WHEN b.paid = 1 THEN 1 ELSE 0 END) as paid_claims,
                    AVG(CASE 
                        WHEN COALESCE(b.paid, 0) = 0 THEN
                            DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date))
                        ELSE 0
                    END) as avg_outstanding_days
                FROM bills b
                LEFT JOIN corporate c ON BINARY b.corp_id = BINARY c.corp_id
                $whereClause
                GROUP BY b.corp_id, c.corporate 
                ORDER BY claim_count DESC 
                LIMIT 20";

            try {
                if (!empty($params)) {
                    $stmt = $conn->prepare($corp_sql);
                    if ($stmt) {
                        $stmt->bind_param($types, ...$params);
                        if ($stmt->execute()) {
                            $result = $stmt->get_result();
                            while ($row = $result->fetch_assoc()) {
                                $corporate_breakdown[] = $row;
                            }
                        }
                    }
                } else {
                    $result = $conn->query($corp_sql);
                    if ($result) {
                        while ($row = $result->fetch_assoc()) {
                            $corporate_breakdown[] = $row;
                        }
                    }
                }
            } catch (Exception $e) {
                error_log("Corporate breakdown query failed: " . $e->getMessage());
            }
        }

        echo json_encode([
            'success' => true,
            'summary' => $summary,
            'corporate_breakdown' => $corporate_breakdown
        ]);

    } elseif ($report_type === 'detailed') {
        // Detailed list with pagination
        $page = intval($_GET['page'] ?? 1);
        $limit = intval($_GET['limit'] ?? 50);
        $offset = ($page - 1) * $limit;

        // Get total count first
        $count_sql = "SELECT COUNT(*) as total FROM bills b $whereClause";
        
        // Apply status filter for count
        if (!empty($status)) {
            switch ($status) {
                case 'PAID':
                    $count_sql .= " AND b.paid = 1";
                    break;
                case 'REJECTED':
                    $count_sql .= " AND COALESCE(b.vet_status, -1) = 0";
                    break;
                case 'NOT_RECEIVED':
                    $count_sql .= " AND b.date_received IS NULL";
                    break;
                case 'PENDING_VETTING':
                    $count_sql .= " AND b.vet_status IS NULL AND b.date_received IS NOT NULL";
                    break;
                case 'APPROVED_UNPAID':
                    $count_sql .= " AND COALESCE(b.vet_status, -1) = 1 AND COALESCE(b.paid, 0) = 0";
                    break;
            }
        }

        // Apply aging bracket filter for count
        if (!empty($aging_bracket)) {
            switch ($aging_bracket) {
                case 'SETTLED':
                    $count_sql .= " AND b.paid = 1";
                    break;
                case '0-30 DAYS':
                    $count_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 30";
                    break;
                case '31-60 DAYS':
                    $count_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 31 AND 60";
                    break;
                case '61-90 DAYS':
                    $count_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 61 AND 90";
                    break;
                case '91-120 DAYS':
                    $count_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 91 AND 120";
                    break;
                case 'OVER 120 DAYS':
                    $count_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) > 120";
                    break;
            }
        }
        
        if (!empty($params)) {
            $stmt = $conn->prepare($count_sql);
            if (!$stmt) {
                throw new Exception("Failed to prepare count statement: " . $conn->error);
            }
            $stmt->bind_param($types, ...$params);
            if (!$stmt->execute()) {
                throw new Exception("Failed to execute count query: " . $stmt->error);
            }
            $total = $stmt->get_result()->fetch_assoc()['total'];
        } else {
            $result = $conn->query($count_sql);
            if (!$result) {
                throw new Exception("Failed to execute count query: " . $conn->error);
            }
            $total = $result->fetch_assoc()['total'];
        }

        // Get detailed data using BINARY joins to avoid collation issues
        $detailed_sql = "
            SELECT 
                b.claim_no,
                b.invoice_no,
                b.member_no,
                b.corp_id,
                COALESCE(c.corporate, 'Unknown Corporate') as corporate,
                b.provider,
                COALESCE(p.provider_name, 'Unknown Provider') as provider_name,
                b.service,
                COALESCE(s.service, 'Unknown Service') as service_name,
                b.invoice_date,
                b.invoiced_amount,
                b.amount_payable,
                b.date_received,
                b.vet_status,
                b.paid,
                b.voucher_date,
                b.payment_no,
                b.family_no,
                
                -- Settlement date
                CASE 
                    WHEN b.paid = 1 AND b.voucher_date IS NOT NULL THEN b.voucher_date
                    WHEN b.paid = 1 AND b.voucher_date IS NULL THEN CURDATE()
                    ELSE NULL
                END AS settlement_date,
                
                -- Total processing days
                CASE 
                    WHEN b.paid = 1 THEN
                        CASE 
                            WHEN b.voucher_date IS NOT NULL THEN 
                                DATEDIFF(b.voucher_date, b.invoice_date)
                            ELSE 
                                DATEDIFF(CURDATE(), b.invoice_date)
                        END
                    ELSE 
                        DATEDIFF(CURDATE(), b.invoice_date)
                END AS total_processing_days,
                
                -- Outstanding days
                CASE 
                    WHEN COALESCE(b.paid, 0) = 0 THEN
                        DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date))
                    ELSE 0
                END AS outstanding_days,
                
                -- Status
                CASE 
                    WHEN b.paid = 1 THEN 'PAID'
                    WHEN COALESCE(b.vet_status, -1) = 0 THEN 'REJECTED'
                    WHEN b.date_received IS NULL THEN 'NOT_RECEIVED'
                    WHEN b.vet_status IS NULL THEN 'PENDING_VETTING'
                    WHEN COALESCE(b.vet_status, -1) = 1 AND COALESCE(b.paid, 0) = 0 THEN 'APPROVED_UNPAID'
                    ELSE 'UNKNOWN'
                END AS claim_status,
                
                -- Aging bracket
                CASE 
                    WHEN b.paid = 1 THEN 'SETTLED'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 30 THEN '0-30 DAYS'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 60 THEN '31-60 DAYS'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 90 THEN '61-90 DAYS'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 120 THEN '91-120 DAYS'
                    ELSE 'OVER 120 DAYS'
                END AS aging_bracket,
                
                b.anniv,
                b.user_id,
                b.date_entered
                
            FROM bills b
            LEFT JOIN corporate c ON BINARY b.corp_id = BINARY c.corp_id
            LEFT JOIN providers p ON b.provider = p.provider_id
            LEFT JOIN services s ON b.service = s.code
            $whereClause";

        // Apply additional filters for detailed view
        if (!empty($status)) {
            switch ($status) {
                case 'PAID':
                    $detailed_sql .= " AND b.paid = 1";
                    break;
                case 'REJECTED':
                    $detailed_sql .= " AND COALESCE(b.vet_status, -1) = 0";
                    break;
                case 'NOT_RECEIVED':
                    $detailed_sql .= " AND b.date_received IS NULL";
                    break;
                case 'PENDING_VETTING':
                    $detailed_sql .= " AND b.vet_status IS NULL AND b.date_received IS NOT NULL";
                    break;
                case 'APPROVED_UNPAID':
                    $detailed_sql .= " AND COALESCE(b.vet_status, -1) = 1 AND COALESCE(b.paid, 0) = 0";
                    break;
            }
        }

        if (!empty($aging_bracket)) {
            switch ($aging_bracket) {
                case 'SETTLED':
                    $detailed_sql .= " AND b.paid = 1";
                    break;
                case '0-30 DAYS':
                    $detailed_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 30";
                    break;
                case '31-60 DAYS':
                    $detailed_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 31 AND 60";
                    break;
                case '61-90 DAYS':
                    $detailed_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 61 AND 90";
                    break;
                case '91-120 DAYS':
                    $detailed_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) BETWEEN 91 AND 120";
                    break;
                case 'OVER 120 DAYS':
                    $detailed_sql .= " AND COALESCE(b.paid, 0) != 1 AND DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) > 120";
                    break;
            }
        }

        $detailed_sql .= " ORDER BY b.invoice_date DESC, b.claim_no DESC LIMIT $limit OFFSET $offset";
        
        if (!empty($params)) {
            $stmt = $conn->prepare($detailed_sql);
            if (!$stmt) {
                throw new Exception("Failed to prepare detailed statement: " . $conn->error);
            }
            $stmt->bind_param($types, ...$params);
            if (!$stmt->execute()) {
                throw new Exception("Failed to execute detailed query: " . $stmt->error);
            }
            $result = $stmt->get_result();
        } else {
            $result = $conn->query($detailed_sql);
            if (!$result) {
                throw new Exception("Failed to execute detailed query: " . $conn->error);
            }
        }

        $claims = [];
        while ($row = $result->fetch_assoc()) {
            $claims[] = $row;
        }

        echo json_encode([
            'success' => true,
            'claims' => $claims,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'limit' => $limit,
                'pages' => ceil($total / $limit)
            ]
        ]);

    } elseif ($report_type === 'export') {
        // Export data (limit to 5000 for performance)
        $export_sql = "
            SELECT 
                b.claim_no,
                b.invoice_no,
                b.member_no,
                b.corp_id,
                COALESCE(c.corporate, 'Unknown Corporate') as corporate,
                b.provider,
                COALESCE(p.provider_name, 'Unknown Provider') as provider_name,
                b.service,
                COALESCE(s.service, 'Unknown Service') as service_name,
                b.invoice_date,
                b.invoiced_amount,
                b.amount_payable,
                b.date_received,
                b.vet_status,
                b.paid,
                b.voucher_date,
                b.payment_no,
                b.family_no,
                
                CASE 
                    WHEN b.paid = 1 AND b.voucher_date IS NOT NULL THEN b.voucher_date
                    WHEN b.paid = 1 AND b.voucher_date IS NULL THEN CURDATE()
                    ELSE NULL
                END AS settlement_date,
                
                CASE 
                    WHEN b.paid = 1 THEN
                        CASE 
                            WHEN b.voucher_date IS NOT NULL THEN 
                                DATEDIFF(b.voucher_date, b.invoice_date)
                            ELSE 
                                DATEDIFF(CURDATE(), b.invoice_date)
                        END
                    ELSE 
                        DATEDIFF(CURDATE(), b.invoice_date)
                END AS total_processing_days,
                
                CASE 
                    WHEN COALESCE(b.paid, 0) = 0 THEN
                        DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date))
                    ELSE 0
                END AS outstanding_days,
                
                CASE 
                    WHEN b.paid = 1 THEN 'PAID'
                    WHEN COALESCE(b.vet_status, -1) = 0 THEN 'REJECTED'
                    WHEN b.date_received IS NULL THEN 'NOT_RECEIVED'
                    WHEN b.vet_status IS NULL THEN 'PENDING_VETTING'
                    WHEN COALESCE(b.vet_status, -1) = 1 AND COALESCE(b.paid, 0) = 0 THEN 'APPROVED_UNPAID'
                    ELSE 'UNKNOWN'
                END AS claim_status,
                
                CASE 
                    WHEN b.paid = 1 THEN 'SETTLED'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 30 THEN '0-30 DAYS'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 60 THEN '31-60 DAYS'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 90 THEN '61-90 DAYS'
                    WHEN DATEDIFF(CURDATE(), COALESCE(b.date_received, b.invoice_date)) <= 120 THEN '91-120 DAYS'
                    ELSE 'OVER 120 DAYS'
                END AS aging_bracket,
                
                b.anniv,
                b.user_id,
                b.date_entered
                
            FROM bills b
            LEFT JOIN corporate c ON BINARY b.corp_id = BINARY c.corp_id
            LEFT JOIN providers p ON b.provider = p.provider_id
            LEFT JOIN services s ON b.service = s.code
            $whereClause
            ORDER BY b.invoice_date DESC, b.claim_no DESC 
            LIMIT 5000";
        
        if (!empty($params)) {
            $stmt = $conn->prepare($export_sql);
            if (!$stmt) {
                throw new Exception("Failed to prepare export statement: " . $conn->error);
            }
            $stmt->bind_param($types, ...$params);
            if (!$stmt->execute()) {
                throw new Exception("Failed to execute export query: " . $stmt->error);
            }
            $result = $stmt->get_result();
        } else {
            $result = $conn->query($export_sql);
            if (!$result) {
                throw new Exception("Failed to execute export query: " . $conn->error);
            }
        }

        $claims = [];
        while ($row = $result->fetch_assoc()) {
            $claims[] = $row;
        }

        echo json_encode([
            'success' => true,
            'claims' => $claims
        ]);
    }

} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage(),
        'debug' => [
            'line' => $e->getLine(),
            'file' => basename($e->getFile()),
            'mysql_error' => $conn->error ?? 'No MySQL error'
        ]
    ]);
}

if (isset($conn)) {
    $conn->close();
}
?>