-- ============================================
-- SQL Queries to Clear All Orders and Analytics Data
-- ============================================
-- WARNING: These queries will DELETE ALL data from the specified tables
-- Use with caution! Make sure you have a backup before running these.
-- ============================================

-- Step 1: Delete Shortfall Analytics Daily records
-- (This is independent and doesn't have foreign key constraints with orders)
DELETE FROM "shortfall_analytics_daily";

-- Step 2: Delete Product Shortfalls
-- (These reference orders, but we'll delete them explicitly before orders)
DELETE FROM "product_shortfalls";

-- Step 3: Delete Order-Invoice links
-- (These reference both orders and invoices)
DELETE FROM "order_invoices";

-- Step 4: Delete Order Products
-- (These reference orders)
DELETE FROM "order_products";

-- Step 5: Clear the linkedInvoiceId from Orders (to remove FK constraint)
-- (This allows us to delete orders even if invoices still exist)
UPDATE "orders" SET "linkedInvoiceId" = NULL;

-- Step 6: Delete All Orders
DELETE FROM "orders";

-- ============================================
-- Verification Queries (Run after deletion)
-- ============================================

-- Check remaining orders
SELECT COUNT(*) as remaining_orders FROM "orders";

-- Check remaining product shortfalls
SELECT COUNT(*) as remaining_shortfalls FROM "product_shortfalls";

-- Check remaining order products
SELECT COUNT(*) as remaining_order_products FROM "order_products";

-- Check remaining order invoices
SELECT COUNT(*) as remaining_order_invoices FROM "order_invoices";

-- Check remaining analytics records
SELECT COUNT(*) as remaining_analytics FROM "shortfall_analytics_daily";

-- ============================================
-- Alternative: Single Transaction (Safer)
-- ============================================
-- If you want to run everything in a single transaction:

BEGIN;

DELETE FROM "shortfall_analytics_daily";
DELETE FROM "product_shortfalls";
DELETE FROM "order_invoices";
DELETE FROM "order_products";
UPDATE "orders" SET "linkedInvoiceId" = NULL;
DELETE FROM "orders";

-- Review the counts before committing
SELECT 
    (SELECT COUNT(*) FROM "orders") as orders_count,
    (SELECT COUNT(*) FROM "product_shortfalls") as shortfalls_count,
    (SELECT COUNT(*) FROM "order_products") as order_products_count,
    (SELECT COUNT(*) FROM "order_invoices") as order_invoices_count,
    (SELECT COUNT(*) FROM "shortfall_analytics_daily") as analytics_count;

-- If everything looks good, commit:
-- COMMIT;

-- If something went wrong, rollback:
-- ROLLBACK;

