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

require_once __DIR__ . '/../../includes/db.php';

try {

    $response = [];

    /*
    |------------------------------------------------------------
    | SUB DEPARTMENTS
    |------------------------------------------------------------
    */

    $dept_sql = "
        SELECT 
            id,
            sub_dept_name,
            is_active
        FROM lab_sub_departments
        WHERE is_active = 1
        ORDER BY sub_dept_name ASC
    ";

    $departments = $conn->query($dept_sql)->fetch_all(MYSQLI_ASSOC);

    /*
    |------------------------------------------------------------
    | INVESTIGATIONS (CRITICAL FIX HERE)
    |------------------------------------------------------------
    */

    $inv_sql = "
        SELECT 
            sc.id AS service_id,
            sc.service_name,
            sc.lab_sub_dept_id,
            d.sub_dept_name
        FROM service_catalog sc
        LEFT JOIN lab_sub_departments d 
            ON sc.lab_sub_dept_id = d.id
        WHERE 
            sc.service_type IN ('Investigation','Procedure')
        ORDER BY sc.service_name ASC
    ";

    $investigations = $conn->query($inv_sql)->fetch_all(MYSQLI_ASSOC);

    /*
    |------------------------------------------------------------
    | STATISTICS
    |------------------------------------------------------------
    */

    $stats_sql = "
        SELECT
            COUNT(*) AS total_investigations,
            SUM(CASE WHEN lab_sub_dept_id IS NOT NULL THEN 1 ELSE 0 END) AS mapped_investigations,
            SUM(CASE WHEN lab_sub_dept_id IS NULL THEN 1 ELSE 0 END) AS unmapped_investigations
        FROM service_catalog
        WHERE service_type IN ('Investigation','Procedure')
    ";

    $stats = $conn->query($stats_sql)->fetch_assoc();

    $response = [
        "success" => true,
        "data" => [
            "departments" => $departments,
            "investigations" => $investigations,
            "statistics" => [
                "total_investigations" => (int)$stats['total_investigations'],
                "mapped_investigations" => (int)$stats['mapped_investigations'],
                "unmapped_investigations" => (int)$stats['unmapped_investigations'],
                "total_departments" => count($departments)
            ]
        ]
    ];

    echo json_encode($response);
    exit;

} catch (Exception $e) {

    echo json_encode([
        "success" => false,
        "message" => $e->getMessage()
    ]);
}