<?php
session_start();
include '../inc/connect.php';
header('Content-Type: application/json');

$action = isset($_GET['action']) ? $_GET['action'] : (isset($_POST['action']) ? $_POST['action'] : '');
$res = array('status'=>'error','message'=>'ไม่สามารถดำเนินการได้');

switch($action){
    case 'get_group_guidance_report':
        $start_date = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
        $end_date = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
        
        try {
            $sql = "SELECT g.*, 
                           pt.title as participant_type_name,
                           gt.title as guidance_type_name
                    FROM group_guidance g 
                    LEFT JOIN typeActivity pt ON g.participant_type = pt.id
                    LEFT JOIN typeGuidance gt ON g.guidance_type = gt.id
                    WHERE 1=1";
            
            $params = array();
            
            if($start_date !== '') {
                $sql .= " AND g.service_date >= ?";
                $params[] = $start_date;
            }
            
            if($end_date !== '') {
                $sql .= " AND g.service_date <= ?";
                $params[] = $end_date;
            }
            
            
            $sql .= " ORDER BY g.service_date DESC, g.created_at DESC";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // สถิติข้อมูล
            $total_records = count($data);
            // PHP 5.3 compatible alternative to array_column
            $participant_counts = array();
            foreach($data as $row) {
                $participant_counts[] = isset($row['participant_count']) ? $row['participant_count'] : 0;
            }
            $total_participants = array_sum($participant_counts);
            // $total_hours = array_sum(array_column($data, 'duration_hours')); // ลบฟิลด์ duration แล้ว
            
            echo json_encode(array(
                "status" => "success", 
                "data" => $data,
                "statistics" => array(
                    "total_records" => $total_records,
                    "total_participants" => $total_participants,
                    // "total_hours" => $total_hours // ลบฟิลด์ duration แล้ว
                )
            ));
        } catch (PDOException $e) {
            echo json_encode(array("status"=>"error", "message"=>"เกิดข้อผิดพลาด: " . $e->getMessage()));
        }
        exit;

    case 'get_individual_counseling_report':
        $start_date = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
        $end_date = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
        $service_success = isset($_GET['service_success']) ? trim($_GET['service_success']) : '';
        
        try {
            $sql = "SELECT i.*, u.name as user_name, 
                           o.title as occupation_title, 
                           m.title as marital_status_title,
                           pt.title as participant_type_name
                    FROM individual_counseling i 
                    LEFT JOIN users u ON i.user_id = u.id 
                    LEFT JOIN typeOccupation o ON i.occupation_id = o.id
                    LEFT JOIN typeStatus m ON i.marital_status_id = m.id
                    LEFT JOIN typeActivity pt ON i.participant_type = pt.id
                    WHERE 1=1";
            
            $params = array();
            
            if($start_date !== '') {
                $sql .= " AND i.service_request_date >= ?";
                $params[] = $start_date;
            }
            
            if($end_date !== '') {
                $sql .= " AND i.service_request_date <= ?";
                $params[] = $end_date;
            }
            
            
            if($service_success !== '') {
                $sql .= " AND i.service_success = ?";
                $params[] = $service_success;
            }
            
            $sql .= " ORDER BY i.service_request_date DESC, i.created_at DESC";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // สถิติข้อมูล
            $total_records = count($data);
            $success_count = count(array_filter($data, function($item) { return $item['service_success'] === 'success'; }));
            $failure_count = $total_records - $success_count;
            
            // สถิติตามเพศ
            $male_count = count(array_filter($data, function($item) { return $item['gender'] === 'ชาย'; }));
            $female_count = count(array_filter($data, function($item) { return $item['gender'] === 'หญิง'; }));
            
            // สถิติตามช่วงอายุ
            $age_groups = array();
            foreach($data as $item) {
                if($item['birth_date']) {
                    $birth_year = date('Y', strtotime($item['birth_date']));
                    $current_year = date('Y');
                    $age = $current_year - $birth_year;
                    
                    if($age <= 18) $age_group = '0-18';
                    elseif($age <= 30) $age_group = '19-30';
                    elseif($age <= 50) $age_group = '31-50';
                    else $age_group = '51+';
                    
                    if(!isset($age_groups[$age_group])) $age_groups[$age_group] = 0;
                    $age_groups[$age_group]++;
                }
            }
            
            echo json_encode(array(
                "status" => "success", 
                "data" => $data,
                "statistics" => array(
                    "total_records" => $total_records,
                    "success_count" => $success_count,
                    "failure_count" => $failure_count,
                    "male_count" => $male_count,
                    "female_count" => $female_count,
                    "age_groups" => $age_groups
                )
            ));
        } catch (PDOException $e) {
            echo json_encode(array("status"=>"error", "message"=>"เกิดข้อผิดพลาด: " . $e->getMessage()));
        }
        exit;

    case 'get_service_records_report':
        $individual_counseling_id = isset($_GET['individual_counseling_id']) ? intval($_GET['individual_counseling_id']) : 0;
        $start_date = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
        $end_date = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
        
        try {
            $sql = "SELECT sr.*, i.first_name, i.last_name, u.name as user_name
                    FROM service_records sr 
                    LEFT JOIN individual_counseling i ON sr.individual_counseling_id = i.id
                    LEFT JOIN users u ON i.user_id = u.id
                    WHERE 1=1";
            
            $params = array();
            
            if($individual_counseling_id > 0) {
                $sql .= " AND sr.individual_counseling_id = ?";
                $params[] = $individual_counseling_id;
            }
            
            if($start_date !== '') {
                $sql .= " AND sr.service_date >= ?";
                $params[] = $start_date;
            }
            
            if($end_date !== '') {
                $sql .= " AND sr.service_date <= ?";
                $params[] = $end_date;
            }
            
            $sql .= " ORDER BY sr.service_date DESC, sr.created_at DESC";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            echo json_encode(array("status" => "success", "data" => $data));
        } catch (PDOException $e) {
            echo json_encode(array("status"=>"error", "message"=>"เกิดข้อผิดพลาด: " . $e->getMessage()));
        }
        exit;

    case 'get_summary_report':
        $start_date = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
        $end_date = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
        
        try {
            // สถิติรวม Group Guidance
            $sql_group = "SELECT COUNT(*) as total_group_sessions,
                                SUM(participant_count) as total_group_participants,
                                0 as total_group_hours /* ลบฟิลด์ duration แล้ว */
                         FROM group_guidance WHERE 1=1";
            $params_group = array();
            
            if($start_date !== '') {
                $sql_group .= " AND service_date >= ?";
                $params_group[] = $start_date;
            }
            if($end_date !== '') {
                $sql_group .= " AND service_date <= ?";
                $params_group[] = $end_date;
            }
            
            $stmt = $pdo->prepare($sql_group);
            $stmt->execute($params_group);
            $group_stats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // สถิติรวม Individual Counseling
            $sql_individual = "SELECT COUNT(*) as total_individual_cases,
                                     SUM(CASE WHEN service_success = 'success' THEN 1 ELSE 0 END) as success_cases,
                                     SUM(CASE WHEN service_success = 'failure' THEN 1 ELSE 0 END) as failure_cases
                              FROM individual_counseling WHERE 1=1";
            $params_individual = array();
            
            if($start_date !== '') {
                $sql_individual .= " AND service_request_date >= ?";
                $params_individual[] = $start_date;
            }
            if($end_date !== '') {
                $sql_individual .= " AND service_request_date <= ?";
                $params_individual[] = $end_date;
            }
            
            $stmt = $pdo->prepare($sql_individual);
            $stmt->execute($params_individual);
            $individual_stats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // สถิติรวม Service Records
            $sql_service = "SELECT COUNT(*) as total_service_records
                           FROM service_records sr
                           LEFT JOIN individual_counseling i ON sr.individual_counseling_id = i.id
                           WHERE 1=1";
            $params_service = array();
            
            if($start_date !== '') {
                $sql_service .= " AND sr.service_date >= ?";
                $params_service[] = $start_date;
            }
            if($end_date !== '') {
                $sql_service .= " AND sr.service_date <= ?";
                $params_service[] = $end_date;
            }
            
            $stmt = $pdo->prepare($sql_service);
            $stmt->execute($params_service);
            $service_stats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // รายงานตาม User (เฉพาะ Individual Counseling เนื่องจาก Group Guidance ไม่มี user_id แล้ว)
            $sql_user = "SELECT u.name, 
                               COUNT(DISTINCT i.id) as individual_cases
                        FROM users u
                        LEFT JOIN individual_counseling i ON u.id = i.user_id";
            if($start_date !== '' || $end_date !== '') {
                $sql_user .= " AND (1=1";
                if($start_date !== '') $sql_user .= " AND i.service_request_date >= '$start_date'";
                if($end_date !== '') $sql_user .= " AND i.service_request_date <= '$end_date'";
                $sql_user .= ")";
            }
            
            $sql_user .= " GROUP BY u.id, u.name
                          HAVING individual_cases > 0
                          ORDER BY u.name";
            
            $stmt = $pdo->prepare($sql_user);
            $stmt->execute();
            $user_stats = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            echo json_encode(array(
                "status" => "success", 
                "data" => array(
                    "group_guidance" => $group_stats,
                    "individual_counseling" => $individual_stats,
                    "service_records" => $service_stats,
                    "user_statistics" => $user_stats
                )
            ));
        } catch (PDOException $e) {
            echo json_encode(array("status"=>"error", "message"=>"เกิดข้อผิดพลาด: " . $e->getMessage()));
        }
        exit;

    case 'export_data':
        $type = isset($_GET['type']) ? trim($_GET['type']) : '';
        $format = isset($_GET['format']) ? trim($_GET['format']) : 'csv';
        $start_date = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
        $end_date = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
        
        if($type === '') {
            echo json_encode(array("status"=>"error", "message"=>"กรุณาระบุประเภทข้อมูล"));
            exit;
        }
        
        try {
            $data = array();
            $filename = '';
            
            switch($type) {
                case 'group_guidance':
                    $sql = "SELECT * FROM group_guidance WHERE 1=1";
                    $params = array();
                    
                    if($start_date !== '') {
                        $sql .= " AND service_date >= ?";
                        $params[] = $start_date;
                    }
                    if($end_date !== '') {
                        $sql .= " AND service_date <= ?";
                        $params[] = $end_date;
                    }
                    
                    $sql .= " ORDER BY service_date DESC";
                    
                    $stmt = $pdo->prepare($sql);
                    $stmt->execute($params);
                    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    $filename = 'group_guidance_export_' . date('Y-m-d');
                    break;
                    
                case 'individual_counseling':
                    $sql = "SELECT * FROM individual_counseling WHERE 1=1";
                    $params = array();
                    
                    if($start_date !== '') {
                        $sql .= " AND service_request_date >= ?";
                        $params[] = $start_date;
                    }
                    if($end_date !== '') {
                        $sql .= " AND service_request_date <= ?";
                        $params[] = $end_date;
                    }
                    
                    $sql .= " ORDER BY service_request_date DESC";
                    
                    $stmt = $pdo->prepare($sql);
                    $stmt->execute($params);
                    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    $filename = 'individual_counseling_export_' . date('Y-m-d');
                    break;
            }
            
            if($format === 'csv') {
                // ส่งออกเป็น CSV
                header('Content-Type: text/csv; charset=UTF-8');
                header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
                header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                
                echo "\xEF\xBB\xBF"; // UTF-8 BOM
                
                if(!empty($data)) {
                    $output = fopen('php://output', 'w');
                    
                    // Header row
                    fputcsv($output, array_keys($data[0]));
                    
                    // Data rows
                    foreach($data as $row) {
                        fputcsv($output, $row);
                    }
                    
                    fclose($output);
                }
            } else {
                // ส่งออกเป็น JSON
                header('Content-Type: application/json; charset=UTF-8');
                header('Content-Disposition: attachment; filename="' . $filename . '.json"');
                
                echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
            }
            
        } catch (PDOException $e) {
            echo json_encode(array("status"=>"error", "message"=>"เกิดข้อผิดพลาด: " . $e->getMessage()));
        }
        exit;

    default:
        echo json_encode($res);
        exit;
}
?>