-- SQL script to delete all orders and related records
-- This will NOT delete invoices or invoice data
-- 
-- WARNING: This will permanently delete all orders (dispatch plans) and their related data
-- Make sure you have a backup before running this!
--
-- Usage: Run this SQL directly in your PostgreSQL database
-- psql -U your_username -d your_database -f delete-all-orders.sql

BEGIN;

-- Step 1: Delete ProductShortfall records (these reference orders)
-- These will be automatically deleted by cascade, but we'll delete explicitly for clarity
DELETE FROM product_shortfalls;

-- Step 2: Delete OrderProduct records (these reference orders)
-- These will be automatically deleted by cascade, but we'll delete explicitly for clarity
DELETE FROM order_products;

-- Step 3: Delete OrderInvoice junction records (these reference orders)
-- These will be automatically deleted by cascade, but we'll delete explicitly for clarity
DELETE FROM order_invoices;

-- Step 4: Clear the linkedInvoiceId reference in orders (optional, but clean)
-- This removes the link from orders to invoices, but doesn't delete invoices
UPDATE orders SET "linkedInvoiceId" = NULL WHERE "linkedInvoiceId" IS NOT NULL;

-- Step 5: Delete all orders
DELETE FROM orders;

-- Step 6: Update ShortfallAnalyticsDaily to remove order-related data
-- This keeps the invoice data but clears order-related fields
-- This ensures analytics still show invoice data but no order data
UPDATE shortfall_analytics_daily SET
  "totalShortfallItems" = 0,
  "totalShortfallQty" = 0,
  "totalOrderedQty" = 0,
  "totalOrdersWithShortfall" = 0,
  "totalOrders" = 0,
  "totalShortfallAmount" = 0,
  "totalPlannedQty" = 0,
  "totalPlannedAmount" = 0,
  -- Keep actual quantities/amounts (from invoices) and unlinked invoice data
  -- totalActualQty and totalActualAmount already include unlinked invoices
  -- unlinkedActualQty and unlinkedActualAmount remain unchanged
  "updatedAt" = NOW();

COMMIT;

-- Verification queries (run these after to confirm deletion):
-- SELECT COUNT(*) as total_orders FROM orders;
-- SELECT COUNT(*) as total_product_shortfalls FROM product_shortfalls;
-- SELECT COUNT(*) as total_order_products FROM order_products;
-- SELECT COUNT(*) as total_order_invoices FROM order_invoices;
-- SELECT COUNT(*) as total_invoices FROM invoices; -- Should remain unchanged
-- SELECT COUNT(*) as total_invoice_line_items FROM invoice_line_items; -- Should remain unchanged

