<?php
require_once __DIR__ . '/config/db.php';
$database = new Database();
$db = $database->getConnection();

try {
    $db->beginTransaction();

    // Find the product ID by SKU or Name
    $stmt_find = $db->prepare("SELECT id FROM products WHERE sku = 'เสื้อ' OR name = 'เสื้อ้'");
    $stmt_find->execute();
    $product_ids = $stmt_find->fetchAll(PDO::FETCH_COLUMN);

    if (empty($product_ids)) {
        echo "<h3>Product 'เสื้อ้' (SKU: เสื้อ) not found in the database.</h3>";
        $db->rollBack();
        exit();
    }

    foreach ($product_ids as $del_id) {
        // Delete referencing order items
        $stmt_orders = $db->prepare("SELECT DISTINCT order_id FROM order_items WHERE product_id = ?");
        $stmt_orders->execute([$del_id]);
        $order_ids = $stmt_orders->fetchAll(PDO::FETCH_COLUMN);

        $stmt_del_items = $db->prepare("DELETE FROM order_items WHERE product_id = ?");
        $stmt_del_items->execute([$del_id]);

        if (!empty($order_ids)) {
            foreach ($order_ids as $o_id) {
                $stmt_chk = $db->prepare("SELECT COUNT(*) FROM order_items WHERE order_id = ?");
                $stmt_chk->execute([$o_id]);
                if ($stmt_chk->fetchColumn() == 0) {
                    $stmt_del_pay = $db->prepare("DELETE FROM payments WHERE order_id = ?");
                    $stmt_del_pay->execute([$o_id]);
                    $stmt_del_ord = $db->prepare("DELETE FROM orders WHERE id = ?");
                    $stmt_del_ord->execute([$o_id]);
                }
            }
        }

        // Delete the product itself
        $stmt = $db->prepare("DELETE FROM products WHERE id = ?");
        $stmt->execute([$del_id]);
        echo "Deleted product ID: $del_id successfully.<br>";
    }

    $db->commit();
    echo "<h3>Product deleted successfully! Please delete this file (delete_product_temp.php).</h3>";
} catch (Exception $e) {
    $db->rollBack();
    echo "Error: " . $e->getMessage();
}
?>