<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);


$action = $_POST['action'];
$table = isset($_POST['table']) ? $_POST['table'] : '';
$xid   = isset($_POST['id']) ? $_POST['id'] : '';
$ff    = isset($_POST['ff']) ? $_POST['ff'] : '';

// $xid = isset($_POST['xid']) ? $_POST['xid'] : '';

// Buat koneksi ke database
include_once '../data.php';  // Ganti path jika perlu (misalnya 'data.php' kalau sama folder)
$conn = new mysqli($servername, $username, $password, $dbname);

// Periksa koneksi
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$conn->set_charset("utf8mb4");
// Fungsi CRUD
if ($action === 'get_kds_orders') {
    get_kds_orders($conn);
} elseif ($action === 'tambah_kds') {
    tambah_kds($conn);
} elseif ($action === 'hapus_kds_items') {
    hapus_kds_items($conn);
} elseif ($action === 'update_kds_status') {
    update_kds_status($conn);    
} elseif ($action === 'tambahjson') {
    tambahjson($conn, $table);
} elseif ($action === 'rubahjson') {
    rubahjson($conn, $table,$xid,$ff);
} elseif ($action === 'tambah') {
    tambah($conn, $table);
} elseif ($action === 'ganti') {
    ganti($conn, $table);
} elseif ($action === 'hapus') {
    hapus($conn, $table);
} elseif ($action === 'ambil') {
    ambil($conn, $table);
} elseif ($action === 'xambil') {
    xambil($conn, $table);
} elseif ($action === 'gantisemua') {
    gantisemua($conn, $table);
} elseif ($action === 'hapussemua') {
    hapussemua($conn, $table);
} elseif ($action === 'ambilawal') {
    ambilawal($conn, $table);
} elseif ($action === 'ambilakhir') {
    ambilakhir($conn, $table);
} elseif ($action === 'berikut') {
    berikut($conn, $table);
} elseif ($action === 'searchNames') {
    searchNames($conn, $table);
} elseif ($action === 'searchcakep') {
    searchcakep($conn, $table);
} elseif ($action === 'sebelum') {
    sebelum($conn, $table);
} elseif ($action === 'ambil_select') {
    ambil_select($conn);
}
else {
    echo "Invalid action";
}

// Tutup koneksi
$conn->close();



function hapus_kds_items($conn) {
    $kdsId = isset($_POST['kdsId']) ? intval($_POST['kdsId']) : 0;
    
    if ($kdsId === 0) {
        echo "0";
        return;
    }
    
    // Delete all orders for the specified KDS ID
    $sql = "DELETE FROM kds_orders WHERE kds_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $kdsId);
    
    if ($stmt->execute()) {
        echo "1"; // Success
    } else {
        echo "Error: " . $stmt->error;
    }
    $stmt->close();
}
function get_kds_orders($conn) {
    $kdsId = isset($_POST['kds_id']) ? intval($_POST['kds_id']) : 0;

    if ($kdsId === 0) {
        echo "[]";
        return;
    }

    // Ambil semua order yang statusnya BUKAN 'cleared'
    $sql = "SELECT * FROM kds_orders WHERE kds_id = ? AND status != 'cleared'";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $kdsId);
    $stmt->execute();
    $result = $stmt->get_result();

    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    echo json_encode($rows);
    $stmt->close();
}


// <<< TAMBAHKAN FUNGSI BARU DI BAWAH INI >>>
function update_kds_status($conn) {
    $ids = json_decode($_POST['ids'], true);
    $status = $_POST['status'];
    
    // Ambil parameter tambahan untuk timestamp
    $order_proses = isset($_POST['order_proses']) ? $_POST['order_proses'] : null;
    $order_finish = isset($_POST['order_finish']) ? $_POST['order_finish'] : null;
    
    if (empty($ids) || !is_array($ids) || empty($status)) {
        echo "0"; // Gagal karena data tidak lengkap
        return;
    }
    
    // Ubah array ID menjadi string yang aman untuk klausa IN
    $idList = implode(',', array_map('intval', $ids));
    
    // Siapkan query update dasar
    $sql = "UPDATE kds_orders SET status = ?";
    
    // Tambahkan field order_proses jika ada
    if ($order_proses) {
        $sql .= ", order_proses = ?";
    }
    
    // Tambahkan field order_finish jika ada
    if ($order_finish) {
        $sql .= ", order_finish = ?";
    }
    
    // Tambahkan WHERE clause
    $sql .= " WHERE id IN ($idList)";
    
    // Gunakan prepared statement untuk keamanan
    $stmt = $conn->prepare($sql);
    if ($stmt === false) {
        echo "Prepare failed: " . $conn->error;
        return;
    }
    
    // Siapkan parameter untuk bind_param
    $params = [$status];
    $types = "s"; // Status adalah string
    
    // Tambahkan order_proses jika ada
    if ($order_proses) {
        $params[] = $order_proses;
        $types .= "s"; // order_proses adalah string (datetime)
    }
    
    // Tambahkan order_finish jika ada
    if ($order_finish) {
        $params[] = $order_finish;
        $types .= "s"; // order_finish adalah string (datetime)
    }
    
    // Bind parameter
    $stmt->bind_param($types, ...$params);
    
    if ($stmt->execute()) {
        echo "1"; // Sukses
    } else {
        echo "Error: " . $stmt->error;
    }
    $stmt->close();
}


// function hapus_kds_items($conn) {
//     $ids = json_decode($_POST['ids'], true);

//     if (empty($ids) || !is_array($ids)) {
//         echo "0";
//         return;
//     }

//     // Ubah array ID menjadi string yang aman untuk klausa IN
//     $idList = implode(',', array_map('intval', $ids));

//     $sql = "DELETE FROM kds_orders WHERE id IN ($idList)";

//     if ($conn->query($sql) === TRUE) {
//         echo "1"; // Sukses
//     } else {
//         echo "Error: " . $conn->error;
//     }
// }

// Implementasi Fungsi CRUD
function tambah_kds($conn) {
    $data = json_decode($_POST['data'], true);

    if (empty($data)) {
        echo "0";
        return;
    }

    // Siapkan statement untuk efisiensi
    $stmt = $conn->prepare("INSERT INTO kds_orders (kds_id, order_id, table_id, waiter, order_time, item_id, item_name, quantity, note, toppings, kgroup, brand, cab) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    if ($stmt === false) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
        return;
    }

    $conn->begin_transaction();
    try {
        foreach ($data as $item) {
            $stmt->bind_param("isssssissssss", 
                $item['kds_id'], 
                $item['order_id'], 
                $item['table_id'], 
                $item['waiter'], 
                $item['order_time'], 
                $item['item_id'], 
                $item['item_name'], 
                $item['quantity'], 
                $item['note'], 
                $item['toppings'], 
                $item['kgroup'], 
                $item['brand'], 
                $item['cab']
            );
            $stmt->execute();
        }
        $conn->commit();
        echo "1"; // Sukses
    } catch (mysqli_sql_exception $exception) {
        $conn->rollback();
        echo "Transaction failed: " . $exception->getMessage();
    }
    $stmt->close();
}

function searchNames($conn) {
    $keyword = $_POST['keyword'];

    // Buat dan jalankan query SELECT untuk mencari nama berdasarkan keyword
    // $sql = "SELECT CONCAT('TAMBAH DATA') AS nama UNION SELECT nama AS nama FROM penduduk WHERE  nama LIKE '%$keyword%' OR nama='TAMBAH DATA' LIMIT 10";
    //  $sql = "SELECT CONCAT( nama+' | '+alamat) AS  nama  FROM penduduk WHERE nama LIKE '%$keyword%' ";
    //  $sql = "SELECT  nama FROM warga WHERE nama LIKE '%$keyword%' ";
     $sql = "SELECT CONCAT(nama, ' | ', alamat) AS nama FROM relasi WHERE nama LIKE '%$keyword%'";
    $result = $conn->query($sql);

    if ($result) {
        if ($result->num_rows > 0) {
            $names = array();
            while ($row = $result->fetch_assoc()) {
                $names[] = $row['nama'];
            }
            echo json_encode($names);
        } else {
            echo json_encode([]); // Return an empty array if no matches
        }
    } else {
        // Handle the query execution error
        // echo json_encode(['error' => 'Error executing the query: ' . $conn->error]);
        echo json_encode(['error' => $conn->error]);
    }
}

function searchcakep($conn) {
    $keyword = $_POST['keyword'];
    $tabel   = $_POST['tabel'];

    // Buat dan jalankan query SELECT untuk mencari nama berdasarkan keyword
    $sql = "SELECT CONCAT(kode,' _ ',nama) as nama FROM $tabel WHERE nama LIKE '%$keyword%' limit 20 ";

    $result = $conn->query($sql);

    if ($result) {
        if ($result->num_rows > 0) {
            $names = array();
            while ($row = $result->fetch_assoc()) {
                $names[] = $row['nama'];
            }
            echo json_encode($names);
        } else {
            echo json_encode([]); // Return an empty array if no matches
        }
    } else {
        // Handle the query execution error
        // echo json_encode(['error' => 'Error executing the query: ' . $conn->error]);
        echo json_encode(['error' => $conn->error]);
    }
}
// ...




function tambahjson($conn, $table)
{
    $jsonData = json_decode($_POST['jsonData'], true);
    
    // Hapus 'id' jika ada, karena akan di-generate otomatis
    unset($jsonData['id']);
    
    $columns = array_keys($jsonData);
    $values = array_values($jsonData);
    
    // Bungkus nama kolom dengan backticks
    $quotedColumns = array_map(function($col) {
        return "`" . str_replace("`", "``", $col) . "`";
    }, $columns);

    // Buat placeholder untuk prepared statement
    $placeholders = implode(', ', array_fill(0, count($columns), '?'));

    // Buat query SQL
    $sql = "INSERT INTO `{$table}` (" . implode(', ', $quotedColumns) . ") VALUES ({$placeholders})";

    try {
        // Gunakan prepared statement untuk keamanan
        $stmt = $conn->prepare($sql);
        
        // =================================================================
        // <<< PERBAIKAN DIMULAI DI SINI >>>
        // =================================================================
        
        // 1. Buat string tipe data untuk bind_param. 
        //    Kita anggap semua string ('s') agar aman, MySQL akan mengkonversi tipe datanya.
        $types = str_repeat('s', count($values));
        
        // 2. Ikat (bind) parameter ke statement
        $stmt->bind_param($types, ...$values);
        
        // 3. Eksekusi statement tanpa argumen
        $stmt->execute();
        
        // =================================================================
        // <<< AKHIR DARI PERBAIKAN >>>
        // =================================================================

        // Ambil ID dari baris yang baru saja dimasukkan
        $newId = $conn->insert_id;

        // Kembalikan ID baru sebagai respons
        echo $newId;

    } catch (mysqli_sql_exception $e) {
        // Jika terjadi error, kirim pesan error
        http_response_code(500); // Set status error
        echo "Error: " . $e->getMessage() . " | Query: " . $sql;
    }
}

function rubahJson($conn, $table,$xid,$ff)

{   $jsonData = json_decode($_POST['jsonData'], true);
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];
    // $xid = $jsonData['id'];
    // Buat array yang berisi string 'kolom = nilai' untuk query UPDATE
    $updateData = [];
    foreach ($jsonData as $column => $value) {
        if (gettype($value) == 'string') {
            $updateData[] = "$column = '$value'"; // tambahkan tanda kutip pada nilai string
        } else {
           $updateData[] = "$column = $value";
        }
    };

    $setClause = implode(', ', $updateData);
    // Buat dan jalankan query UPDATE
    echo $xid . '     '.$ff.'        ';
    if ($ff==='') {
        $sql = "UPDATE $table SET $setClause WHERE id = $xid";
    } else {
        $sql = "UPDATE $table SET $setClause WHERE brand='$xbrand' AND cab='$xcab' AND  $ff = '$xid'";
    };
    echo 'sql:' . $sql;
    $sql = str_replace(" left ", " `left` ", $sql);
    $sql = str_replace(" top ", " `top` ", $sql);
    // echo $sql;
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo 1; // Mengembalikan 1 jika sukses
    } else {
        echo  ' gagal '.$xid; // Mengembalikan 0 jika gagal
    }
}

function tambah($conn, $table)
{
    $data = $_POST['data'];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat daftar kolom dan nilai untuk query INSERT
    $columns = implode(', ', array_keys($data));
    $values = "'" . implode("', '", array_values($data)) . "'";

    // Buat dan jalankan query INSERT
    $sql = "INSERT INTO $table ($columns) VALUES ($values)";
    $result = $conn->query($sql);

    
    if ($result === TRUE) {
        echo 1; // Mengembalikan 1 jika sukses
    } else {
        echo 0 ; // Mengembalikan 0 jika gagal
    }
}

function ganti($conn, $table)
{
    $idField = $_POST['idField'];
    $idValue = $_POST['idValue'];
    $data = $_POST['data'];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat daftar kolom dan nilai untuk query UPDATE
    $setValues = "";
    foreach ($data as $field => $value) {
        $setValues .= "$field = '$value', ";
    }
    $setValues = rtrim($setValues, ', ');

    // Buat dan jalankan query UPDATE
    $sql = "UPDATE $table SET $setValues WHERE $idField = $idValue";
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo 1; // Mengembalikan 1 jika sukses
    } else {
        echo 0; // Mengembalikan 0 jika gagal
    }
}

function hapus($conn, $table)
{
    $idField = $_POST['idField'];
    $idValue = $_POST['idValue'];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat dan jalankan query DELETE
    $sql = "DELETE FROM $table WHERE brand='$xbrand' AND cab='$xcab' AND  $idField = $idValue";
    
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo 1; // Mengembalikan 1 jika sukses
    } else {
        echo 0; // Mengembalikan 0 jika gagal
    }
}

function xambil($conn, $table)
{
    $field = $_POST['field'];
    $value1 = $_POST['value'][0];
    $value2 = $_POST['value'][1];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];
   
    //  echo $xcab.$xbrand . '       xxxxxxxxxxxxxx     ';
    // Buat dan jalankan query SELECT dengan pencarian range
    $sql = "SELECT * FROM $table WHERE $field >= '$value1' AND $field <= '$value2'";
    if ($xcab=='yyy') {
        $xcab='';
        $xbrand='';
        $sql = "SELECT * FROM $table WHERE  $field >= '$value1' AND $field <= '$value2'";
    };    
    $result = $conn->query($sql);

    if ($result === false) {
        // Query tidak berhasil dieksekusi
        echo "Error: " . $conn->error;
        return;
    };


    if ($result->num_rows > 0) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode($rows);
    } else {
        echo "[]";
    }
}


function ambil($conn, $table)
{
    $field = $_POST['field'];
    $value1 = $_POST['value'][0];
    $value2 = $_POST['value'][1];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];
   
    //  echo $xcab.$xbrand . '       xxxxxxxxxxxxxx     ';
    // Buat dan jalankan query SELECT dengan pencarian range
    $sql = "SELECT * FROM $table WHERE brand=$xbrand AND cab=$xcab AND $field >= '$value1' AND $field <= '$value2'";
    if ($xcab=='yyy') {
        $xcab='';
        $xbrand='';
        $sql = "SELECT * FROM $table WHERE  $field >= '$value1' AND $field <= '$value2'";
    };    
    $result = $conn->query($sql);

    if ($result === false) {
        // Query tidak berhasil dieksekusi
        echo "Error: " . $conn->error;
        return;
    };


    if ($result->num_rows > 0) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode($rows);
    } else {
        echo "[]";
    }
}

function ambil_select($conn) {
    $sql = $_POST['sql'];

    // Tambahkan filter brand dan cab jika belum ada
    if (strpos($sql, 'brand') === false && strpos($sql, 'WHERE') !== false) {
        $sql = str_replace('WHERE', "WHERE brand='{$_POST['xbrand']}' AND cab='{$_POST['xcab']}' AND ", $sql);
    } elseif (strpos($sql, 'WHERE') !== false) {
        $sql = str_replace('WHERE', "WHERE brand='{$_POST['xbrand']}' AND cab='{$_POST['xcab']}' AND ", $sql);
    } else {
        $sql .= " WHERE brand='{$_POST['xbrand']}' AND cab='{$_POST['xcab']}'";
    }

    $result = $conn->query($sql);

    if ($result) {
        if ($result->num_rows > 0) {
            $rows = array();
            while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
            echo json_encode($rows);
        } else {
            echo "[]";
        }
    } else {
        error_log("SQL Error: " . $conn->error . " | SQL: " . $sql);
        echo json_encode(["error" => "Query failed"]);
    }
}

function ambilakhir($conn, $table)
{   
    $field = $_POST['field'];
    $value1 = $_POST['value'][0];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Siapkan query dengan prepared statement untuk keamanan
    if (!empty($value1)) {
        $sql = "SELECT * FROM `$table` WHERE brand=? AND cab=? AND `$field` = ? ORDER BY id DESC LIMIT 1";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sss", $xbrand, $xcab, $value1);
    } else {
        $sql = "SELECT * FROM `$table` WHERE brand=? AND cab=? ORDER BY id DESC LIMIT 1";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ss", $xbrand, $xcab);
    }

    $stmt->execute();
    $result = $stmt->get_result();

    if ($result && $result->num_rows > 0) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode($rows);
    } else {
        // PENTING: Selalu kembalikan array JSON kosong jika tidak ada data
        echo "[]"; 
    }
    $stmt->close();
}

function gantisemua($conn, $table)
{
    $fieldToChange = $_POST['fieldToChange'];
    $valueToChange = $_POST['valueToChange'];
    $changes = $_POST['changes'];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat daftar perubahan untuk query UPDATE
    $setValues = "";
    foreach ($changes as $field => $change) {
        $setValues .= "$field = ";
        if (is_numeric($change)) {  
            $setValues .= "$field + $change, ";
        } else {
            $setValues .= "'$change', ";
        }
    }
    $setValues = rtrim($setValues, ', ');

    // Buat dan jalankan query UPDATE
    $sql = "UPDATE $table SET $setValues WHERE $fieldToChange = '$valueToChange'";
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo 1; // Mengembalikan 1 jika sukses
    } else {
        echo 0; // Mengembalikan 0 jika gagal
    }
}

function hapussemua($conn, $table)
{
    $fieldToDelete = $_POST['fieldToDelete'];
    $valueToDelete = $_POST['valueToDelete'];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat dan jalankan query DELETE
    $sql = "DELETE FROM $table WHERE $fieldToDelete = '$valueToDelete'";
    $result = $conn->query($sql);

    if ($result === TRUE) {
        echo 1; // Mengembalikan 1 jika sukses
    } else {
        echo 0; // Mengembalikan 0 jika gagal
    }
}

function ambilawal($conn, $table)
{
    $field = $_POST['field'];
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat dan jalankan query SELECT untuk mengambil record pertama
    $sql = "SELECT * FROM $table ORDER BY $field ASC LIMIT 1";
    $result = $conn->query($sql);

    // Pastikan tidak ada kesalahan saat menjalankan query
    if (!$result) {
        echo "[]";
        return;
    }

    if ($result->num_rows > 0) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode($rows);
    } else {
        echo "[]";
    }

    // Jangan lupa untuk melepas result set
    $result->free_result();
}

function ambilakhirsss($conn, $table)
{
    $field = $_POST['field'];

    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat dan jalankan query SELECT untuk mengambil record terakhir
    $sql = "SELECT * FROM $table ORDER BY $field DESC LIMIT 1";
    echo $sql;
    $result = $conn->query($sql);

    // Pastikan tidak ada kesalahan saat menjalankan query
    if (!$result) {
        echo "[]";
        return;
    }

    if ($result->num_rows > 0) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode($rows);
    } else {
        echo "[]";
    }

    // Jangan lupa untuk melepas result set
    $result->free_result();
}

function berikut($conn, $table)
{
    $field = $_POST['field'];
    $cari = isset($_POST['cari']) ? $_POST['cari'] : '';
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];

    // Buat dan jalankan query SELECT untuk mengambil data berikutnya
    $sql = "SELECT * FROM $table WHERE $field > '$cari' ORDER BY $field ASC LIMIT 1";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode($rows);
    } else {
        echo "[]";
    }
}




function sebelum($conn, $table)
{
    $field = $_POST['field'];
    $cari = isset($_POST['cari']) ? $_POST['cari'] : '';
    $xbrand = $_POST['xbrand'];
    $xcab = $_POST['xcab'];    

    // Buat dan jalankan query SELECT untuk mengambil data sebelumnya
    $sql = "SELECT * FROM $table WHERE $field < '$cari' ORDER BY $field DESC LIMIT 1";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode($rows);
    } else {
        echo "[]";
    }
}
?>
