-- SQL script to delete all dispatch plan related data
-- Execute this script directly in the database
-- WARNING: This will permanently delete all orders/dispatch plans and related data

-- Delete in correct order to respect foreign key constraints

-- 1. Delete product shortfalls (references orders)
DELETE FROM product_shortfalls;

-- 2. Delete order-invoice links (junction table - links between orders and invoices)
-- This removes all linking data between orders and invoices
DELETE FROM order_invoices;

-- 3. Delete order products (references orders)
DELETE FROM order_products;

-- 4. Delete orders/dispatch plans
DELETE FROM orders;

-- 5. Delete shortfall analytics daily records
DELETE FROM shortfall_analytics_daily;

-- Note: Invoices and invoice_line_items are preserved as they contain actual data
-- After deletion, all invoices will be "unlinked" (not connected to any orders)

-- ============================================================================
-- STEP 6: Recompute Analytics for All Invoice Dates (Direct SQL)
-- ============================================================================
-- This step creates ShortfallAnalyticsDaily records for all dates/branches that have invoices
-- Since all orders are deleted, all invoices are unlinked, so we only compute actual qty/amount
--
-- NOTE: If gen_random_uuid() is not available (PostgreSQL < 13), you may need to:
-- 1. Enable the uuid-ossp extension: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 2. Replace gen_random_uuid() with uuid_generate_v4()

-- Create analytics records for each date/branch combination with invoices
-- This uses INSERT ... ON CONFLICT (UPSERT) to handle existing records
-- NOTE: Only includes invoices with non-NULL branchId (invoices with NULL branchId go to GLOBAL only)
INSERT INTO shortfall_analytics_daily (
    id,
    date,
    "branchId",
    "unitId",
    "branchKey",
    "totalShortfallItems",
    "totalShortfallQty",
    "totalOrderedQty",
    "totalOrdersWithShortfall",
    "totalOrders",
    "totalShortfallAmount",
    "unlinkedActualQty",
    "unlinkedActualAmount",
    "totalActualQty",
    "totalActualAmount",
    "totalPlannedQty",
    "totalPlannedAmount",
    "createdAt",
    "updatedAt"
)
SELECT 
    gen_random_uuid()::text as id,
    invoice_date::timestamp at time zone 'UTC' as date,
    branch_id as "branchId",
    branch_unit_id as "unitId",
    branch_id as "branchKey",
    0 as "totalShortfallItems",
    0::decimal as "totalShortfallQty",
    0::decimal as "totalOrderedQty",
    0 as "totalOrdersWithShortfall",
    0 as "totalOrders",
    0::decimal as "totalShortfallAmount",
    COALESCE(actual_qty, 0)::decimal as "unlinkedActualQty",
    COALESCE(actual_amount, 0)::decimal as "unlinkedActualAmount",
    COALESCE(actual_qty, 0)::decimal as "totalActualQty",
    COALESCE(actual_amount, 0)::decimal as "totalActualAmount",
    0::decimal as "totalPlannedQty",
    0::decimal as "totalPlannedAmount",
    NOW() as "createdAt",
    NOW() as "updatedAt"
FROM (
    SELECT 
        DATE_TRUNC('day', i."docDt")::date as invoice_date,
        i."branchId" as branch_id,
        b."unitId" as branch_unit_id,
        SUM(COALESCE(li.qty, 0)) as actual_qty,
        SUM(
            COALESCE(
                li."taxableAmt",
                li.amount,
                li.qty * COALESCE(li.rate, 0),
                0
            )
        ) as actual_amount
    FROM invoices i
    INNER JOIN invoice_line_items li ON li."invoiceId" = i.id
    LEFT JOIN branches b ON b.id = i."branchId"
    WHERE i."docDt" IS NOT NULL
        AND i."branchId" IS NOT NULL  -- Only include invoices with branchId
    GROUP BY DATE_TRUNC('day', i."docDt")::date, i."branchId", b."unitId"
) invoice_aggregates
ON CONFLICT (date, "branchKey") 
DO UPDATE SET
    "unlinkedActualQty" = EXCLUDED."unlinkedActualQty",
    "unlinkedActualAmount" = EXCLUDED."unlinkedActualAmount",
    "totalActualQty" = EXCLUDED."totalActualQty",
    "totalActualAmount" = EXCLUDED."totalActualAmount",
    "unitId" = EXCLUDED."unitId",
    "updatedAt" = NOW();

-- Also create GLOBAL records (aggregated across ALL invoices including NULL branchId) for each date
-- This includes ALL invoices regardless of branchId, so it's a true system-wide aggregate
INSERT INTO shortfall_analytics_daily (
    id,
    date,
    "branchId",
    "unitId",
    "branchKey",
    "totalShortfallItems",
    "totalShortfallQty",
    "totalOrderedQty",
    "totalOrdersWithShortfall",
    "totalOrders",
    "totalShortfallAmount",
    "unlinkedActualQty",
    "unlinkedActualAmount",
    "totalActualQty",
    "totalActualAmount",
    "totalPlannedQty",
    "totalPlannedAmount",
    "createdAt",
    "updatedAt"
)
SELECT 
    gen_random_uuid()::text as id,
    invoice_date::timestamp at time zone 'UTC' as date,
    NULL as "branchId",
    NULL as "unitId",
    'GLOBAL' as "branchKey",
    0 as "totalShortfallItems",
    0::decimal as "totalShortfallQty",
    0::decimal as "totalOrderedQty",
    0 as "totalOrdersWithShortfall",
    0 as "totalOrders",
    0::decimal as "totalShortfallAmount",
    COALESCE(actual_qty, 0)::decimal as "unlinkedActualQty",
    COALESCE(actual_amount, 0)::decimal as "unlinkedActualAmount",
    COALESCE(actual_qty, 0)::decimal as "totalActualQty",
    COALESCE(actual_amount, 0)::decimal as "totalActualAmount",
    0::decimal as "totalPlannedQty",
    0::decimal as "totalPlannedAmount",
    NOW() as "createdAt",
    NOW() as "updatedAt"
FROM (
    SELECT 
        DATE_TRUNC('day', i."docDt")::date as invoice_date,
        SUM(COALESCE(li.qty, 0)) as actual_qty,
        SUM(
            COALESCE(
                li."taxableAmt",
                li.amount,
                li.qty * COALESCE(li.rate, 0),
                0
            )
        ) as actual_amount
    FROM invoices i
    INNER JOIN invoice_line_items li ON li."invoiceId" = i.id
    WHERE i."docDt" IS NOT NULL
        -- Include ALL invoices (both with and without branchId) for GLOBAL aggregation
    GROUP BY DATE_TRUNC('day', i."docDt")::date
) global_aggregates
ON CONFLICT (date, "branchKey") 
DO UPDATE SET
    "unlinkedActualQty" = EXCLUDED."unlinkedActualQty",
    "unlinkedActualAmount" = EXCLUDED."unlinkedActualAmount",
    "totalActualQty" = EXCLUDED."totalActualQty",
    "totalActualAmount" = EXCLUDED."totalActualAmount",
    "updatedAt" = NOW();

-- ============================================================================
-- Verification Queries (Run these to verify the recomputation)
-- ============================================================================

-- 1. Summary: Total invoices and line items processed
SELECT 
    COUNT(DISTINCT i.id) as total_invoices,
    COUNT(li.id) as total_line_items,
    SUM(COALESCE(li.qty, 0)) as total_actual_qty,
    SUM(
        COALESCE(
            li."taxableAmt",
            li.amount,
            li.qty * COALESCE(li.rate, 0),
            0
        )
    ) as total_actual_amount,
    COUNT(DISTINCT DATE_TRUNC('day', i."docDt")::date) as total_dates,
    COUNT(DISTINCT i."branchId") as total_branches
FROM invoices i
INNER JOIN invoice_line_items li ON li."invoiceId" = i.id
WHERE i."docDt" IS NOT NULL;

-- 2. Check the created analytics records
SELECT 
    date,
    "branchKey",
    "branchId",
    "unitId",
    "unlinkedActualQty",
    "unlinkedActualAmount",
    "totalActualQty",
    "totalActualAmount",
    "totalPlannedQty",
    "totalPlannedAmount"
FROM shortfall_analytics_daily
ORDER BY date DESC, "branchKey"
LIMIT 100;

-- 3. Verify totals match: Compare analytics records with source invoices
SELECT 
    'Analytics Total' as source,
    SUM("unlinkedActualQty") as total_qty,
    SUM("unlinkedActualAmount") as total_amount
FROM shortfall_analytics_daily
WHERE "branchKey" = 'GLOBAL'
UNION ALL
SELECT 
    'Invoice Source' as source,
    SUM(COALESCE(li.qty, 0)) as total_qty,
    SUM(
        COALESCE(
            li."taxableAmt",
            li.amount,
            li.qty * COALESCE(li.rate, 0),
            0
        )
    ) as total_amount
FROM invoices i
INNER JOIN invoice_line_items li ON li."invoiceId" = i.id
WHERE i."docDt" IS NOT NULL;

