<?php
// Set headers to allow cross-origin requests and handle JSON data
header('Content-Type: application/json');
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
// Handle pre-flight OPTIONS request
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}
// IMPORTANT: Replace with your actual database credentials
$servername = "localhost"; // Usually 'localhost' on cPanel
$username = "ontarioedu_Gap"; // e.g., 'cpaneluser_my_db'
$password = "?wGzoR!77fmuzs%5";
$dbname = "ontarioedu_API";
// Create database connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die(json_encode(["success" => false, "message" => "Connection failed: " . $conn->connect_error]));
}

// Handle HTTP requests
$method = $_SERVER['REQUEST_METHOD'];;

$action = $_GET['action'] ?? '';
$response = ['status' => 'error', 'message' => 'Invalid action'];

switch ($action) {
    case 'create':
        $title_EN = $_POST['title_EN'] ?? '';
        $title_KH = $_POST['title_KH'] ?? '';
        $description_EN = $_POST['description_EN'] ?? '';
        $description_KH = $_POST['description_KH'] ?? '';
        $status = $_POST['status'] ?? 'Active';
        $create_by = $_POST['create_by'] ?? '';
        $create_date = $_POST['create_date'] ?? date('Y-m-d H:i:s');
        $update_date = $_POST['update_date'] ?? date('Y-m-d H:i:s');
        
        $image_file = '';
        if (isset($_FILES['image_file']) && $_FILES['image_file']['error'] == 0) {
            $target_dir = "../../Images/curriculum/";
            if (!is_dir($target_dir)) {
                mkdir($target_dir, 0777, true);
            }
            $target_file = $target_dir . basename($_FILES["image_file"]["name"]);
            $imageFileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));

            // Generate a unique filename to prevent conflicts
            $unique_filename = uniqid() . '.' . $imageFileType;
            $upload_path = $target_dir . $unique_filename;

            if (move_uploaded_file($_FILES["image_file"]["tmp_name"], $upload_path)) {
                $image_file = '' . $upload_path;
            } else {
                $response = ['status' => 'error', 'message' => 'Failed to upload image.'];
                echo json_encode($response);
                exit;
            }
        }
        
        $sql = "INSERT INTO tbl_curriculum (title_EN, title_KH, description_EN, description_KH, image_file, status, create_by, create_date, update_date) VALUES (?, ?, ?, ?, ?, ?, ?)";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sssssss", $title_EN, $title_KH, $description_EN, $description_KH, $image_file, $status, $create_by, $create_date, $update_date);

        if ($stmt->execute()) {
            $response = ['status' => 'success', 'message' => 'Record created successfully.'];
        } else {
            $response = ['status' => 'error', 'message' => 'Execute failed: ' . $stmt->error];
        }
        $stmt->close();
        break;

    case 'read':
        $id = $_GET['id'] ?? null;
        if ($id) {
            $sql = "SELECT * FROM tbl_curriculum WHERE id = ?";
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("i", $id);
            $stmt->execute();
            $result = $stmt->get_result();
            $data = $result->fetch_all(MYSQLI_ASSOC);
            $response = ['status' => 'success', 'data' => $data];
        } else {
            // Server-side processing for DataTables
            $draw = $_GET['draw'] ?? 1;
            $start = $_GET['start'] ?? 0;
            $length = $_GET['length'] ?? 10;
            $search_value = $_GET['search']['value'] ?? '';
            $order_column = $_GET['order'][0]['column'] ?? 0;
            $order_dir = $_GET['order'][0]['dir'] ?? 'asc';
            $columns = ['id', 'title_EN','title_KH', 'description_EN','description_KH', 'image_file', 'status', 'create_by', 'create_date', 'update_date'];

            // Total records count
            $sql_total = "SELECT COUNT(*) FROM tbl_curriculum";
            $result_total = $conn->query($sql_total);
            $total_records = $result_total->fetch_row()[0];

            // Filtered records count and data
            $sql_filter = "SELECT * FROM tbl_curriculum";
            $params = [];
            $types = '';
            
            if (!empty($search_value)) {
                $sql_filter .= " WHERE title_EN LIKE ?";
                $params[] = '%' . $search_value . '%';
                $types .= 's';
            }

            $sql_filter_count = "SELECT COUNT(*) FROM ($sql_filter) AS temp";
            $stmt_count = $conn->prepare($sql_filter_count);
            if ($params) {
                $stmt_count->bind_param($types, ...$params);
            }
            $stmt_count->execute();
            $filtered_records = $stmt_count->get_result()->fetch_row()[0];

            // Order and limit
            $order_by = $columns[$order_column] ?? 'id';
            $sql_filter .= " ORDER BY $order_by $order_dir LIMIT ?, ?";
            
            $stmt_data = $conn->prepare($sql_filter);
            $params[] = $start;
            $params[] = $length;
            $types .= 'ii';
            $stmt_data->bind_param($types, ...$params);
            $stmt_data->execute();
            $result_data = $stmt_data->get_result();
            $data = $result_data->fetch_all(MYSQLI_ASSOC);

            $response = [
                "draw" => intval($draw),
                "recordsTotal" => intval($total_records),
                "recordsFiltered" => intval($filtered_records),
                "data" => $data
            ];
        }
        break;

    case 'update':
        $id = $_POST['id'] ?? '';
        $title_EN = $_POST['title_EN'] ?? '';
        $title_KH = $_POST['title_KH'] ?? '';
        $description_EN = $_POST['description'] ?? '';
        $description_KH = $_POST['description_KH'] ?? '';
        $status = $_POST['status'] ?? 'Active';
        $create_by = $_POST['create_by'] ?? '';
        $create_date = $_POST['create_date'] ?? '';
        $update_date = date('Y-m-d H:i:s');
        
        $image_file = null;
        $sql = "SELECT image_file FROM tbl_curriculum WHERE id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $stmt->bind_result($current_image);
        $stmt->fetch();
        $stmt->close();
        
        if (isset($_FILES['image_file']) && $_FILES['image_file']['error'] == 0) {
            $target_dir = "../../Images/curriculum/";
            if (!is_dir($target_dir)) {
                mkdir($target_dir, 0777, true);
            }
            $target_file = $target_dir . basename($_FILES["image_file"]["name"]);
            $imageFileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));

            $unique_filename = uniqid() . '.' . $imageFileType;
            $upload_path = $target_dir . $unique_filename;

            if (move_uploaded_file($_FILES["image_file"]["tmp_name"], $upload_path)) {
                $image_file = '' . $upload_path;
                // Delete old image if exists
                if ($current_image && file_exists($current_image)) {
                    unlink($current_image);
                }
            } else {
                $response = ['status' => 'error', 'message' => 'Failed to upload new image.'];
                echo json_encode($response);
                exit;
            }
        } else {
            $image_file = $current_image; // Keep the existing image
        }

        $sql = "UPDATE tbl_curriculum SET title_EN=?,title_KH=?, description_EN=?,description_KH=?, image_file=?, status=?, create_by=?, create_date=?, update_date=? WHERE id=?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sssssssi", $title_EN, $title_KH, $description_EN,$description_KH, $image_file, $status, $create_by, $create_date, $update_date, $id);

        if ($stmt->execute()) {
            $response = ['status' => 'success', 'message' => 'Record updated successfully.'];
        } else {
            $response = ['status' => 'error', 'message' => 'Execute failed: ' . $stmt->error];
        }
        $stmt->close();
        break;

    case 'delete':
        $id = $_GET['id'] ?? '';

        // First, get the image path to delete the file
        $sql_select = "SELECT image_file FROM tbl_curriculum WHERE id = ?";
        $stmt_select = $conn->prepare($sql_select);
        $stmt_select->bind_param("i", $id);
        $stmt_select->execute();
        $stmt_select->bind_result($image_file);
        $stmt_select->fetch();
        $stmt_select->close();

        // Then, delete the record from the database
        $sql_delete = "DELETE FROM tbl_curriculum WHERE id = ?";
        $stmt_delete = $conn->prepare($sql_delete);
        $stmt_delete->bind_param("i", $id);
        
        if ($stmt_delete->execute()) {
            // Delete the file from the server
            if ($image_file && file_exists($image_file)) {
                unlink($image_file);
            }
            $response = ['status' => 'success', 'message' => 'Record and image deleted successfully.'];
        } else {
            $response = ['status' => 'error', 'message' => 'Execute failed: ' . $stmt_delete->error];
        }
        $stmt_delete->close();
        break;
}

echo json_encode($response);

$conn->close();
?>