-- Fast SQL backfill script for analytics performance fields
-- This script calculates and updates all ShortfallAnalyticsDaily records in bulk
-- Much faster than calling recomputeDaily() for each record individually
-- 
-- Usage: Run this SQL directly in your PostgreSQL database
-- psql -d your_database -f backfill-analytics-performance-fields.sql

BEGIN;

-- Step 1: Update existing ShortfallAnalyticsDaily records with new fields
-- Calculate unlinked invoices and ProductShortfall data in bulk

WITH daily_unlinked AS (
  -- Unlinked invoice aggregations per day/branch
  SELECT 
    DATE(i."docDt") as date,
    COALESCE(i."branchId", 'GLOBAL') as branch_key,
    COALESCE(SUM(li.qty), 0)::DECIMAL as unlinked_qty,
    COALESCE(SUM(
      COALESCE(li."taxableAmt", li.amount, li.qty * li.rate, 0)
    ), 0)::DECIMAL as unlinked_amount
  FROM invoices i
  INNER JOIN invoice_line_items li ON li."invoiceId" = i.id
  WHERE NOT EXISTS (
    SELECT 1 FROM order_invoices oi WHERE oi."invoiceId" = i.id
  )
  AND NOT EXISTS (
    SELECT 1 FROM orders o WHERE o."linkedInvoiceId" = i.id
  )
  GROUP BY DATE(i."docDt"), COALESCE(i."branchId", 'GLOBAL')
),
daily_shortfall AS (
  -- ProductShortfall aggregations per day/branch
  SELECT 
    DATE(o."docDt") as date,
    COALESCE(o."branchId", 'GLOBAL') as branch_key,
    COALESCE(SUM(ps."orderedQty"), 0)::DECIMAL as planned_qty,
    COALESCE(SUM(ps."invoicedQty"), 0)::DECIMAL as actual_qty_linked,
    COALESCE(SUM(
      CASE 
        WHEN p.rate IS NOT NULL AND p.rate > 0 
        THEN ps."orderedQty" * p.rate
        ELSE 0
      END
    ), 0)::DECIMAL as planned_amount,
    COALESCE(SUM(
      CASE 
        WHEN p.rate IS NOT NULL AND p.rate > 0 
        THEN ps."invoicedQty" * p.rate
        ELSE 0
      END
    ), 0)::DECIMAL as actual_amount_linked
  FROM orders o
  INNER JOIN product_shortfalls ps ON ps."orderId" = o.id
  LEFT JOIN products p ON p.id = ps."productId"
  WHERE o."isDeleted" = false
  GROUP BY DATE(o."docDt"), COALESCE(o."branchId", 'GLOBAL')
),
combined_data AS (
  -- Combine both data sources
  SELECT 
    COALESCE(ds.date, du.date) as date,
    COALESCE(ds.branch_key, du.branch_key) as branch_key,
    COALESCE(ds.planned_qty, 0) as planned_qty,
    COALESCE(ds.actual_qty_linked, 0) + COALESCE(du.unlinked_qty, 0) as actual_qty,
    COALESCE(ds.planned_amount, 0) as planned_amount,
    COALESCE(ds.actual_amount_linked, 0) + COALESCE(du.unlinked_amount, 0) as actual_amount,
    COALESCE(du.unlinked_qty, 0) as unlinked_qty,
    COALESCE(du.unlinked_amount, 0) as unlinked_amount
  FROM daily_shortfall ds
  FULL OUTER JOIN daily_unlinked du 
    ON ds.date = du.date AND ds.branch_key = du.branch_key
)
UPDATE shortfall_analytics_daily sad
SET 
  "unlinkedActualQty" = COALESCE(cd.unlinked_qty, 0),
  "unlinkedActualAmount" = COALESCE(cd.unlinked_amount, 0),
  "totalActualQty" = COALESCE(cd.actual_qty, 0),
  "totalActualAmount" = COALESCE(cd.actual_amount, 0),
  "totalPlannedQty" = COALESCE(cd.planned_qty, 0),
  "totalPlannedAmount" = COALESCE(cd.planned_amount, 0),
  "updatedAt" = NOW()
FROM combined_data cd
WHERE DATE(sad.date) = cd.date
  AND sad."branchKey" = cd.branch_key;

-- Step 2: Create new records for dates with data but no ShortfallAnalyticsDaily record
-- This ensures all dates with orders/invoices have analytics records

WITH dates_needing_records AS (
  -- Find all date/branch combinations that have data
  SELECT DISTINCT
    DATE(o."docDt") as date,
    COALESCE(o."branchId", 'GLOBAL') as branch_key,
    o."branchId" as branch_id
  FROM orders o
  WHERE o."isDeleted" = false
  
  UNION
  
  SELECT DISTINCT
    DATE(i."docDt") as date,
    COALESCE(i."branchId", 'GLOBAL') as branch_key,
    i."branchId" as branch_id
  FROM invoices i
),
missing_dates AS (
  -- Find dates that don't have ShortfallAnalyticsDaily records yet
  SELECT 
    dnr.date,
    dnr.branch_key,
    dnr.branch_id
  FROM dates_needing_records dnr
  WHERE NOT EXISTS (
    SELECT 1 
    FROM shortfall_analytics_daily sad
    WHERE DATE(sad.date) = dnr.date
      AND sad."branchKey" = dnr.branch_key
  )
),
calculated_data AS (
  -- Calculate all fields for missing dates
  SELECT 
    md.date,
    md.branch_key,
    md.branch_id,
    -- ProductShortfall data
    COALESCE((
      SELECT SUM(ps."orderedQty")
      FROM orders o
      INNER JOIN product_shortfalls ps ON ps."orderId" = o.id
      WHERE DATE(o."docDt") = md.date
        AND COALESCE(o."branchId", 'GLOBAL') = md.branch_key
        AND o."isDeleted" = false
    ), 0)::DECIMAL as planned_qty,
    COALESCE((
      SELECT SUM(ps."invoicedQty")
      FROM orders o
      INNER JOIN product_shortfalls ps ON ps."orderId" = o.id
      WHERE DATE(o."docDt") = md.date
        AND COALESCE(o."branchId", 'GLOBAL') = md.branch_key
        AND o."isDeleted" = false
    ), 0)::DECIMAL as actual_qty_linked,
    COALESCE((
      SELECT SUM(
        CASE 
          WHEN p.rate IS NOT NULL AND p.rate > 0 
          THEN ps."orderedQty" * p.rate
          ELSE 0
        END
      )
      FROM orders o
      INNER JOIN product_shortfalls ps ON ps."orderId" = o.id
      LEFT JOIN products p ON p.id = ps."productId"
      WHERE DATE(o."docDt") = md.date
        AND COALESCE(o."branchId", 'GLOBAL') = md.branch_key
        AND o."isDeleted" = false
    ), 0)::DECIMAL as planned_amount,
    COALESCE((
      SELECT SUM(
        CASE 
          WHEN p.rate IS NOT NULL AND p.rate > 0 
          THEN ps."invoicedQty" * p.rate
          ELSE 0
        END
      )
      FROM orders o
      INNER JOIN product_shortfalls ps ON ps."orderId" = o.id
      LEFT JOIN products p ON p.id = ps."productId"
      WHERE DATE(o."docDt") = md.date
        AND COALESCE(o."branchId", 'GLOBAL') = md.branch_key
        AND o."isDeleted" = false
    ), 0)::DECIMAL as actual_amount_linked,
    -- Unlinked invoice data
    COALESCE((
      SELECT SUM(li.qty)
      FROM invoices i
      INNER JOIN invoice_line_items li ON li."invoiceId" = i.id
      WHERE DATE(i."docDt") = md.date
        AND COALESCE(i."branchId", 'GLOBAL') = md.branch_key
        AND NOT EXISTS (SELECT 1 FROM order_invoices oi WHERE oi."invoiceId" = i.id)
        AND NOT EXISTS (SELECT 1 FROM orders o WHERE o."linkedInvoiceId" = i.id)
    ), 0)::DECIMAL as unlinked_qty,
    COALESCE((
      SELECT SUM(
        COALESCE(li."taxableAmt", li.amount, li.qty * li.rate, 0)
      )
      FROM invoices i
      INNER JOIN invoice_line_items li ON li."invoiceId" = i.id
      WHERE DATE(i."docDt") = md.date
        AND COALESCE(i."branchId", 'GLOBAL') = md.branch_key
        AND NOT EXISTS (SELECT 1 FROM order_invoices oi WHERE oi."invoiceId" = i.id)
        AND NOT EXISTS (SELECT 1 FROM orders o WHERE o."linkedInvoiceId" = i.id)
    ), 0)::DECIMAL as unlinked_amount
  FROM missing_dates md
)
INSERT INTO shortfall_analytics_daily (
  id,
  date,
  "branchId",
  "branchKey",
  "totalShortfallItems",
  "totalShortfallQty",
  "totalOrderedQty",
  "totalOrdersWithShortfall",
  "totalOrders",
  "totalShortfallAmount",
  "unlinkedActualQty",
  "unlinkedActualAmount",
  "totalActualQty",
  "totalActualAmount",
  "totalPlannedQty",
  "totalPlannedAmount",
  "createdAt",
  "updatedAt"
)
SELECT 
  gen_random_uuid()::text as id,
  cd.date,
  NULLIF(cd.branch_id, 'GLOBAL') as "branchId",
  cd.branch_key as "branchKey",
  0 as "totalShortfallItems",
  0 as "totalShortfallQty",
  0 as "totalOrderedQty",
  0 as "totalOrdersWithShortfall",
  0 as "totalOrders",
  0 as "totalShortfallAmount",
  cd.unlinked_qty as "unlinkedActualQty",
  cd.unlinked_amount as "unlinkedActualAmount",
  (cd.actual_qty_linked + cd.unlinked_qty) as "totalActualQty",
  (cd.actual_amount_linked + cd.unlinked_amount) as "totalActualAmount",
  cd.planned_qty as "totalPlannedQty",
  cd.planned_amount as "totalPlannedAmount",
  NOW() as "createdAt",
  NOW() as "updatedAt"
FROM calculated_data cd
WHERE cd.planned_qty > 0 
   OR cd.actual_qty_linked > 0 
   OR cd.unlinked_qty > 0
ON CONFLICT (date, "branchKey") DO UPDATE SET
  "unlinkedActualQty" = EXCLUDED."unlinkedActualQty",
  "unlinkedActualAmount" = EXCLUDED."unlinkedActualAmount",
  "totalActualQty" = EXCLUDED."totalActualQty",
  "totalActualAmount" = EXCLUDED."totalActualAmount",
  "totalPlannedQty" = EXCLUDED."totalPlannedQty",
  "totalPlannedAmount" = EXCLUDED."totalPlannedAmount",
  "updatedAt" = NOW();

COMMIT;

-- Note: This script will:
-- 1. Update all existing ShortfallAnalyticsDaily records with new fields
-- 2. Create new records for dates that have orders/invoices but no analytics record
-- 3. Use bulk SQL operations for maximum speed
-- 
-- Expected execution time: Much faster than the TypeScript script (seconds vs minutes/hours)
