<?php
/**
 * Export Reports
 * Export all report data to CSV format
 */

require_once '../../config/config.php';
require_once '../../includes/functions.php';

requireAdmin();

$pdo = getDB();
$type = $_GET['type'] ?? 'doctors';
$format = $_GET['format'] ?? 'csv';

// Set headers for download
if ($format == 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $type . '_report_' . date('Y-m-d') . '.csv"');
    $output = fopen('php://output', 'w');
}

switch($type) {
    case 'doctors':
        $stmt = $pdo->query("SELECT u.id, u.full_name, u.email, u.username, u.is_active, u.created_at as joined_date,
                            COUNT(DISTINCT c.id) as total_cases,
                            COUNT(DISTINCT CASE WHEN c.status = 'active' THEN c.id END) as active_cases,
                            COUNT(DISTINCT CASE WHEN c.status = 'closed' THEN c.id END) as closed_cases,
                            (SELECT COUNT(*) FROM subscriptions WHERE user_id = u.id AND is_active = 1) as has_subscription
                            FROM users u
                            LEFT JOIN cases c ON u.id = c.user_id
                            WHERE u.role = 'doctor'
                            GROUP BY u.id
                            ORDER BY total_cases DESC");
        $data = $stmt->fetchAll();
        
        fputcsv($output, ['ID', 'Doctor Name', 'Email', 'Username', 'Status', 'Joined Date', 'Total Cases', 'Active Cases', 'Closed Cases', 'Has Subscription']);
        foreach($data as $row) {
            fputcsv($output, [
                $row['id'], $row['full_name'], $row['email'], $row['username'],
                $row['is_active'] ? 'Active' : 'Inactive', $row['joined_date'],
                $row['total_cases'], $row['active_cases'], $row['closed_cases'],
                $row['has_subscription'] ? 'Yes' : 'No'
            ]);
        }
        break;
        
    case 'subscriptions':
        $stmt = $pdo->query("SELECT s.*, u.full_name, u.email, 
                            DATEDIFF(s.end_date, CURDATE()) as days_remaining
                            FROM subscriptions s
                            JOIN users u ON s.user_id = u.id
                            ORDER BY s.created_at DESC");
        $data = $stmt->fetchAll();
        
        fputcsv($output, ['ID', 'Doctor', 'Email', 'Plan', 'Amount', 'Start Date', 'End Date', 'Status', 'Created']);
        foreach($data as $row) {
            $status = $row['is_active'] ? ($row['plan_type'] == 'unlimited' ? 'Lifetime' : 'Active') : 'Cancelled';
            if ($row['plan_type'] != 'unlimited' && $row['end_date'] && strtotime($row['end_date']) < time()) $status = 'Expired';
            fputcsv($output, [
                $row['id'], $row['full_name'], $row['email'], $row['plan_type'], $row['amount'],
                $row['start_date'], $row['end_date'] ?? 'Lifetime', $status, $row['created_at']
            ]);
        }
        break;
        
    case 'revenue':
        $stmt = $pdo->query("SELECT 
                            DATE_FORMAT(created_at, '%Y-%m') as month,
                            COUNT(*) as subscription_count,
                            SUM(amount) as total_amount
                            FROM subscriptions
                            GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                            ORDER BY month DESC");
        $data = $stmt->fetchAll();
        
        fputcsv($output, ['Month', 'Subscription Count', 'Total Revenue']);
        foreach($data as $row) {
            fputcsv($output, [$row['month'], $row['subscription_count'], $row['total_amount']]);
        }
        break;
        
    case 'remedies':
        $stmt = $pdo->query("SELECT r.name, r.abbreviation,
                            (SELECT COUNT(*) FROM rubric_remedy WHERE remedy_id = r.id) as rubric_count,
                            (SELECT COUNT(*) FROM cases WHERE prescribed_remedy_id = r.id) as prescription_count
                            FROM remedies r
                            ORDER BY prescription_count DESC");
        $data = $stmt->fetchAll();
        
        fputcsv($output, ['Remedy', 'Abbreviation', 'Rubric Count', 'Prescription Count']);
        foreach($data as $row) {
            fputcsv($output, [$row['name'], $row['abbreviation'], $row['rubric_count'], $row['prescription_count']]);
        }
        break;
        
    case 'cases':
    default:
        $stmt = $pdo->query("SELECT c.id, c.patient_name, c.patient_mobile, c.created_at, c.status,
                            u.full_name as doctor_name, r.name as remedy_name
                            FROM cases c
                            LEFT JOIN users u ON c.user_id = u.id
                            LEFT JOIN remedies r ON c.prescribed_remedy_id = r.id
                            ORDER BY c.created_at DESC
                            LIMIT 1000");
        $data = $stmt->fetchAll();
        
        fputcsv($output, ['Case ID', 'Patient Name', 'Mobile', 'Date', 'Status', 'Doctor', 'Prescribed Remedy']);
        foreach($data as $row) {
            fputcsv($output, [
                $row['id'], $row['patient_name'], $row['patient_mobile'], $row['created_at'],
                $row['status'], $row['doctor_name'], $row['remedy_name']
            ]);
        }
        break;
}

if ($format == 'csv') {
    fclose($output);
}
exit;
?>