<?php
// Database configuration
$host = "localhost";
$username = "saifvjof_doctor_info"; 
$password = "doctor_info123@";
$database = "saifvjof_doctor_info_db";

$conn = new mysqli($host, $username, $password, $database);

if ($conn->connect_error) {
    error_log("JSON Update Failed: Database connection error");
    return false;
}

function generateDoctorJSON($conn) {
    // Updated query without visiting_hours and mobile_number
    $sql = "SELECT 
                c.id as category_id,
                c.category_name, 
                c.category_image,
                d.id as doctor_id,
                d.doctor_name,
                d.doctor_image,
                d.doctor_degree,
                d.doctor_title,
                d.doctor_district,
                d.doctor_chamber,
                d.chamber_address,
                d.visit_time,
                d.apperment,
                d.doctor_about
            FROM categories c
            LEFT JOIN doctors d ON c.id = d.category_id
            ORDER BY c.category_name, d.doctor_name";
    
    $result = $conn->query($sql);
    
    if (!$result) {
        error_log("JSON Update Failed: Query error - " . $conn->error);
        return false;
    }
    
    $categories = [];
    $total_doctors = 0;
    
    while($row = $result->fetch_assoc()) {
        $category_name = $row['category_name'];
        
        $category_exists = false;
        foreach($categories as &$category) {
            if($category['category_name'] === $category_name) {
                $category_exists = true;
                
                if($row['doctor_name'] !== null) {
                    $category['doctor_list'][] = [
                        'doctor_id' => (int)$row['doctor_id'],
                        'doctor_name' => $row['doctor_name'],
                        'doctor_image' => !empty($row['doctor_image']) ? 'https://dic.saiful.app/comilla_doctor_info/' . $row['doctor_image'] : '',
                        'doctor_degree' => $row['doctor_degree'],
                        'doctor_title' => $row['doctor_title'],
                        'doctor_district' => $row['doctor_district'],
                        'doctor_chamber' => $row['doctor_chamber'],
                        'chamber_address' => $row['chamber_address'],
                        'visit_time' => $row['visit_time'],
                        'apperment' => $row['apperment'],
                        'doctor_about' => $row['doctor_about']
                    ];
                    $total_doctors++;
                }
                break;
            }
        }
        
        if(!$category_exists) {
            $new_category = [
                'category_id' => (int)$row['category_id'],
                'category_name' => $category_name,
                'category_image' => 'https://dic.saiful.app/comilla_doctor_info/' . $row['category_image'],
                'doctor_list' => []
            ];
            
            if($row['doctor_name'] !== null) {
                $new_category['doctor_list'][] = [
                    'doctor_id' => (int)$row['doctor_id'],
                    'doctor_name' => $row['doctor_name'],
                    'doctor_image' => !empty($row['doctor_image']) ? 'https://dic.saiful.app/comilla_doctor_info/' . $row['doctor_image'] : '',
                    'doctor_degree' => $row['doctor_degree'],
                    'doctor_title' => $row['doctor_title'],
                    'doctor_district' => $row['doctor_district'],
                    'doctor_chamber' => $row['doctor_chamber'],
                    'chamber_address' => $row['chamber_address'],
                    'visit_time' => $row['visit_time'],
                    'apperment' => $row['apperment'],
                    'doctor_about' => $row['doctor_about']
                ];
                $total_doctors++;
            }
            
            $categories[] = $new_category;
        }
    }
    
    $response = [
        "status" => "success",
        "total_categories" => count($categories),
        "total_doctors" => $total_doctors,
        "data" => $categories,
        "last_updated" => date("Y-m-d H:i:s")
    ];
    
    $json_data = json_encode($response, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    
    if ($json_data === false) {
        error_log("JSON Update Failed: JSON encoding error");
        return false;
    }
    
    $save_result = file_put_contents('../doctor_list.json', $json_data);
    
    if ($save_result === false) {
        error_log("JSON Update Failed: Could not write to doctor_list.json");
        return false;
    }
    
    return true;
}

function generateHospitalJSON($conn) {
    // Updated query without visiting_hours and mobile_number
    $sql = "SELECT 
                h.id as hospital_id,
                h.hospital_name, 
                h.hospital_address,
                h.hospital_image,
                d.id as doctor_id,
                d.doctor_name,
                d.doctor_image,
                d.doctor_degree,
                d.doctor_title,
                d.doctor_district,
                d.doctor_chamber,
                d.chamber_address,
                d.visit_time,
                d.apperment,
                d.doctor_about,
                c.category_name
            FROM hospitals h
            LEFT JOIN doctors d ON h.id = d.hospital_id
            LEFT JOIN categories c ON d.category_id = c.id
            ORDER BY h.hospital_name, d.doctor_name";
    
    $result = $conn->query($sql);
    
    if (!$result) {
        error_log("Hospital JSON Update Failed: Query error - " . $conn->error);
        return false;
    }
    
    $hospitals = [];
    $total_hospital_doctors = 0;
    
    while($row = $result->fetch_assoc()) {
        $hospital_name = $row['hospital_name'];
        
        $hospital_exists = false;
        foreach($hospitals as &$hospital) {
            if($hospital['hospital_name'] === $hospital_name) {
                $hospital_exists = true;
                
                if($row['doctor_name'] !== null) {
                    $hospital['doctor_list'][] = [
                        'doctor_id' => (int)$row['doctor_id'],
                        'doctor_name' => $row['doctor_name'],
                        'doctor_image' => !empty($row['doctor_image']) ? 'https://dic.saiful.app/comilla_doctor_info/' . $row['doctor_image'] : '',
                        'doctor_degree' => $row['doctor_degree'],
                        'doctor_title' => $row['doctor_title'],
                        'doctor_district' => $row['doctor_district'],
                        'doctor_chamber' => $row['doctor_chamber'],
                        'chamber_address' => $row['chamber_address'],
                        'visit_time' => $row['visit_time'],
                        'apperment' => $row['apperment'],
                        'doctor_about' => $row['doctor_about'],
                        'category_name' => $row['category_name']
                    ];
                    $total_hospital_doctors++;
                }
                break;
            }
        }
        
        if(!$hospital_exists) {
            $new_hospital = [
                'hospital_id' => (int)$row['hospital_id'],
                'hospital_name' => $hospital_name,
                'hospital_address' => $row['hospital_address'],
                'hospital_image' => !empty($row['hospital_image']) ? 'https://dic.saiful.app/comilla_doctor_info/' . $row['hospital_image'] : '',
                'doctor_list' => []
            ];
            
            if($row['doctor_name'] !== null) {
                $new_hospital['doctor_list'][] = [
                    'doctor_id' => (int)$row['doctor_id'],
                    'doctor_name' => $row['doctor_name'],
                    'doctor_image' => !empty($row['doctor_image']) ? 'https://dic.saiful.app/comilla_doctor_info/' . $row['doctor_image'] : '',
                    'doctor_degree' => $row['doctor_degree'],
                    'doctor_title' => $row['doctor_title'],
                    'doctor_district' => $row['doctor_district'],
                    'doctor_chamber' => $row['doctor_chamber'],
                    'chamber_address' => $row['chamber_address'],
                    'visit_time' => $row['visit_time'],
                    'apperment' => $row['apperment'],
                    'doctor_about' => $row['doctor_about'],
                    'category_name' => $row['category_name']
                ];
                $total_hospital_doctors++;
            }
            
            $hospitals[] = $new_hospital;
        }
    }
    
    $response = [
        "status" => "success",
        "total_hospitals" => count($hospitals),
        "total_doctors" => $total_hospital_doctors,
        "data" => $hospitals,
        "last_updated" => date("Y-m-d H:i:s")
    ];
    
    $json_data = json_encode($response, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    
    if ($json_data === false) {
        error_log("Hospital JSON Update Failed: JSON encoding error");
        return false;
    }
    
    $save_result = file_put_contents('../hospital_list.json', $json_data);
    
    if ($save_result === false) {
        error_log("Hospital JSON Update Failed: Could not write to hospital_list.json");
        return false;
    }
    
    return true;
}

// Generate both JSON files
$result1 = generateDoctorJSON($conn);
$result2 = generateHospitalJSON($conn);

$conn->close();

// If accessed directly
if (basename(__FILE__) == basename($_SERVER['SCRIPT_FILENAME'])) {
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Origin: *");
    
    if ($result1 && $result2) {
        echo json_encode([
            "status" => "success", 
            "message" => "Both JSON files updated successfully",
            "doctor_json" => "https://dic.saiful.app/comilla_doctor_info/doctor_list.json",
            "hospital_json" => "https://dic.saiful.app/comilla_doctor_info/hospital_list.json",
            "timestamp" => date("Y-m-d H:i:s")
        ]);
    } else {
        echo json_encode([
            "status" => "error", 
            "message" => "Failed to update JSON files"
        ]);
    }
}

return $result1 && $result2;
?>