<?php
/**
 * Clean Demo Stores, Products, Variants, SKUs, and Seed Data
 * Preserves:
 * - Real User & Admin Accounts: superadmin (admin@cargoo.com - ID: 1), ctn_ng (ctnng.29@gmail.com - ID: 2), test (test@gmail.com - ID: 28)
 * - Categories (All 15 system categories)
 * - Referential Integrity for historical transaction relations
 */

declare(strict_types=1);

require_once __DIR__ . '/Database.php';

$db = Database::getInstance();

echo "====================================================\n";
echo "CARGOO DEMO STORES & PRODUCTS CLEANUP\n";
echo "====================================================\n";

$db->beginTransaction();

try {
    // 1. Clean cart_items and saved_items
    $db->exec("DELETE FROM `cart_items`");
    echo "[1/8] Cleaned cart_items\n";

    $db->exec("DELETE FROM `saved_items`");
    echo "[2/8] Cleaned saved_items\n";

    // 2. Clean reviews and review_images
    $db->exec("DELETE FROM `review_images`");
    $db->exec("DELETE FROM `reviews`");
    echo "[3/8] Cleaned reviews & review_images\n";

    // 3. Clean product images, SKUs, and variations
    $db->exec("DELETE FROM `product_images`");
    $db->exec("DELETE FROM `product_skus`");
    $db->exec("DELETE FROM `product_variation_options`");
    $db->exec("DELETE FROM `product_variations`");
    echo "[4/8] Cleaned product images, SKUs, and variations\n";

    // 4. Find products with/without order_items
    $productsWithOrders = $db->query("SELECT DISTINCT product_id FROM `order_items`")->fetchAll(PDO::FETCH_COLUMN);
    $productsWithoutOrders = $db->query("SELECT id FROM `products` WHERE id NOT IN (SELECT DISTINCT product_id FROM `order_items`)")->fetchAll(PDO::FETCH_COLUMN);

    // Hard-delete products without order history
    if (!empty($productsWithoutOrders)) {
        $idsStr = implode(',', array_map('intval', $productsWithoutOrders));
        $deletedCount = $db->exec("DELETE FROM `products` WHERE `id` IN ({$idsStr})");
        echo "[5/8] Hard-deleted {$deletedCount} demo products without order history (IDs: {$idsStr})\n";
    }

    // Soft-delete products with order history (maintain FK integrity, but hide from customer catalog)
    if (!empty($productsWithOrders)) {
        $idsStr = implode(',', array_map('intval', $productsWithOrders));
        $softCount = $db->exec("UPDATE `products` SET `status` = 'deleted', `deleted_at` = NOW(), `stock` = 0 WHERE `id` IN ({$idsStr})");
        echo "[6/8] Soft-deleted {$softCount} demo products with historic order references (IDs: {$idsStr})\n";
    }

    // 5. Clean stores
    // Check stores with orders or settlements
    $storesWithOrdersOrSettlements = $db->query("
        SELECT DISTINCT store_id FROM (
            SELECT store_id FROM `orders`
            UNION
            SELECT store_id FROM `settlements`
        ) as combined_stores WHERE store_id IS NOT NULL
    ")->fetchAll(PDO::FETCH_COLUMN);

    $storesWithoutDependencies = $db->query("
        SELECT id FROM `stores` 
        WHERE id NOT IN (
            SELECT DISTINCT store_id FROM `orders` WHERE store_id IS NOT NULL
            UNION
            SELECT DISTINCT store_id FROM `settlements` WHERE store_id IS NOT NULL
        )
    ")->fetchAll(PDO::FETCH_COLUMN);

    // Hard-delete stores without dependencies
    if (!empty($storesWithoutDependencies)) {
        $idsStr = implode(',', array_map('intval', $storesWithoutDependencies));
        $deletedStores = $db->exec("DELETE FROM `stores` WHERE `id` IN ({$idsStr})");
        echo "[7/8] Hard-deleted {$deletedStores} demo stores without order/settlement history (IDs: {$idsStr})\n";
    }

    // Soft-delete stores with historic order/settlement references
    if (!empty($storesWithOrdersOrSettlements)) {
        $idsStr = implode(',', array_map('intval', $storesWithOrdersOrSettlements));
        $softStores = $db->exec("UPDATE `stores` SET `status` = 'suspended', `deleted_at` = NOW() WHERE `id` IN ({$idsStr})");
        echo "[8/8] Soft-deleted {$softStores} demo stores with historic transactions (IDs: {$idsStr})\n";
    }

    // Reset rating aggregations
    $db->exec("UPDATE `products` SET `rating_avg` = 0.00, `rating_count` = 0");
    $db->exec("UPDATE `stores` SET `rating_avg` = 0.00, `rating_count` = 0");

    $db->commit();
    echo "\n=== CLEANUP COMPLETED SUCCESSFULLY ===\n";

} catch (Throwable $e) {
    $db->rollBack();
    echo "ERROR during cleanup: " . $e->getMessage() . "\n";
    exit(1);
}