-- ============================================================================
-- Phase 8: Inventory Analytics Enhancement
-- Purpose: Create normalized sales and payment line items tables for analytics
-- Date: October 25, 2025
-- ============================================================================

-- Add cashier_id to existing lineitems_payments table
ALTER TABLE lineitems_payments 
  ADD COLUMN IF NOT EXISTS cashier_id uuid REFERENCES users(user_id),
  ADD COLUMN IF NOT EXISTS branch_id uuid REFERENCES branches(branch_id),
  ADD COLUMN IF NOT EXISTS transaction_date timestamptz DEFAULT now();

CREATE INDEX IF NOT EXISTS idx_lineitems_payments_cashier ON lineitems_payments(cashier_id) 
  WHERE isDeleted = false;
CREATE INDEX IF NOT EXISTS idx_lineitems_payments_branch ON lineitems_payments(branch_id) 
  WHERE isDeleted = false;
CREATE INDEX IF NOT EXISTS idx_lineitems_payments_transaction_date ON lineitems_payments(transaction_date) 
  WHERE isDeleted = false;

COMMENT ON COLUMN lineitems_payments.cashier_id IS 'User ID of cashier who processed the transaction';
COMMENT ON COLUMN lineitems_payments.branch_id IS 'Branch where transaction occurred';
COMMENT ON COLUMN lineitems_payments.transaction_date IS 'Timestamp when transaction was processed';

-- ============================================================================
-- Sales Line Items Table (Normalized from JSONB)
-- ============================================================================

CREATE TABLE sales_line_items (
  sales_line_item_id    UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                UUID NOT NULL REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id             UUID REFERENCES branches(branch_id),
  
  -- Transaction Reference
  reference_table       VARCHAR(50) NOT NULL,  -- 'invoices', 'lineitems_payments', 'sales_receipts'
  reference_id          UUID NOT NULL,
  transaction_date      TIMESTAMPTZ NOT NULL,
  transaction_number    TEXT,
  
  -- Product Details
  product_id            UUID NOT NULL REFERENCES products(product_id),
  product_name          TEXT NOT NULL,
  product_barcode       TEXT,
  category_id           UUID REFERENCES product_categories(category_id),
  
  -- Quantity and Units
  quantity              NUMERIC(12,3) NOT NULL,
  unit_type             VARCHAR(50) NOT NULL DEFAULT 'PIECE',
  quantity_in_base_units INTEGER NOT NULL,
  
  -- Pricing (Before Discount)
  unit_price            NUMERIC(12,2) NOT NULL,
  subtotal              NUMERIC(12,2) NOT NULL,  -- quantity * unit_price (excl. VAT)
  
  -- Discount Information
  discount_amount       NUMERIC(12,2) DEFAULT 0,
  discount_percentage   NUMERIC(5,2) DEFAULT 0,
  discount_type         VARCHAR(50),  -- 'percentage', 'amount', 'bogo', etc.
  subtotal_after_discount NUMERIC(12,2) NOT NULL,
  
  -- VAT Information
  vat_code              VARCHAR(10) NOT NULL,     -- 'G', 'A', etc.
  vat_rate              NUMERIC(5,4) NOT NULL,    -- 0.125, 0.0000
  vat_amount            NUMERIC(12,2) NOT NULL,   -- Calculated VAT
  
  -- Total
  line_total            NUMERIC(12,2) NOT NULL,   -- subtotal_after_discount + vat_amount
  
  -- Customer Information
  customer_id           UUID REFERENCES customers(customer_id),
  customer_name         TEXT,
  customer_tin          TEXT,
  
  -- Staff Information
  cashier_id            UUID REFERENCES users(user_id),
  cashier_name          TEXT,
  
  -- Additional Metadata
  notes                 TEXT,
  cost_price            NUMERIC(12,2),  -- For margin analysis
  profit_margin         NUMERIC(12,2),  -- line_total - (cost_price * quantity)
  
  -- Audit Fields
  isDeleted             BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt             TIMESTAMPTZ NOT NULL DEFAULT now(),
  createdAt             TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  -- Constraints
  CONSTRAINT chk_sales_line_items_quantity CHECK (quantity > 0),
  CONSTRAINT chk_sales_line_items_prices CHECK (
    unit_price >= 0 AND 
    subtotal >= 0 AND 
    subtotal_after_discount >= 0 AND
    vat_amount >= 0 AND
    line_total >= 0
  )
);

-- Indexes for performance
CREATE INDEX idx_sales_line_items_org_date ON sales_line_items(org_id, transaction_date) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_product ON sales_line_items(product_id, transaction_date) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_vat_code ON sales_line_items(vat_code, transaction_date) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_reference ON sales_line_items(reference_table, reference_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_customer ON sales_line_items(customer_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_cashier ON sales_line_items(cashier_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_branch ON sales_line_items(branch_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_category ON sales_line_items(category_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_sales_line_items_date ON sales_line_items(transaction_date DESC) 
  WHERE isDeleted = false;

-- Composite index for VAT report
CREATE INDEX idx_sales_line_items_vat_report 
  ON sales_line_items(org_id, vat_code, transaction_date)
  WHERE isDeleted = false;

-- Composite index for product movement report
CREATE INDEX idx_sales_line_items_movement 
  ON sales_line_items(product_id, org_id, transaction_date)
  WHERE isDeleted = false;

-- Unique constraint to prevent duplicate extractions
CREATE UNIQUE INDEX idx_sales_line_items_unique 
  ON sales_line_items(reference_table, reference_id, product_id, transaction_date)
  WHERE isDeleted = false;

COMMENT ON TABLE sales_line_items IS 'Normalized sales line items extracted from JSONB for reporting and analytics';
COMMENT ON COLUMN sales_line_items.vat_code IS 'VAT code from products.tax_rates: G=12.5%, A=0%';
COMMENT ON COLUMN sales_line_items.subtotal IS 'Amount before discount and VAT (taxable amount base)';
COMMENT ON COLUMN sales_line_items.subtotal_after_discount IS 'Taxable amount after discounts';
COMMENT ON COLUMN sales_line_items.vat_amount IS 'Calculated VAT amount';
COMMENT ON COLUMN sales_line_items.line_total IS 'Total including VAT';
COMMENT ON COLUMN sales_line_items.quantity_in_base_units IS 'Normalized quantity in base units for aggregation';
COMMENT ON COLUMN sales_line_items.cost_price IS 'Product cost for profit margin calculation';
COMMENT ON COLUMN sales_line_items.profit_margin IS 'Gross profit: revenue - cost';

-- ============================================================================
-- Payment Line Items Table (Normalized from JSONB)
-- ============================================================================

CREATE TABLE payment_line_items (
  payment_line_item_id  UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                UUID NOT NULL REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id             UUID REFERENCES branches(branch_id),
  
  -- Transaction Reference
  reference_table       VARCHAR(50) NOT NULL,  -- 'lineitems_payments', 'payments', 'sales_receipts'
  reference_id          UUID NOT NULL,
  transaction_date      TIMESTAMPTZ NOT NULL,
  transaction_number    TEXT,
  
  -- Payment Method Details
  payment_method        VARCHAR(50) NOT NULL,  -- 'Cash', 'Credit', 'Debit', 'MPaisa', 'Cheque', etc.
  payment_amount        NUMERIC(12,2) NOT NULL,
  
  -- Payment Method Specific Data
  card_type             VARCHAR(50),           -- 'Visa', 'MasterCard', 'Amex', etc.
  card_last_four        VARCHAR(4),
  transaction_id        TEXT,                  -- External transaction ID (MPaisa, bank, etc.)
  reference_number      TEXT,                  -- Cheque number, approval code, etc.
  
  -- Credit Payment Details (if applicable)
  credit_customer_id    UUID REFERENCES customers(customer_id),
  credit_customer_name  TEXT,
  credit_terms_days     INTEGER,               -- Payment terms (30, 60, 90 days)
  credit_due_date       DATE,
  
  -- Customer Information
  customer_id           UUID REFERENCES customers(customer_id),
  customer_name         TEXT,
  customer_tin          TEXT,
  
  -- Staff Information
  cashier_id            UUID REFERENCES users(user_id),
  cashier_name          TEXT,
  
  -- Additional Metadata
  notes                 TEXT,
  exchange_rate         NUMERIC(10,4) DEFAULT 1.0,  -- For foreign currency
  currency_code         VARCHAR(3) DEFAULT 'FJD',
  
  -- Status
  payment_status        VARCHAR(50) DEFAULT 'completed',  -- 'completed', 'pending', 'failed', 'refunded'
  cleared_date          DATE,                   -- When cheque/credit cleared
  
  -- Audit Fields
  isDeleted             BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt             TIMESTAMPTZ NOT NULL DEFAULT now(),
  createdAt             TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  -- Constraints
  CONSTRAINT chk_payment_line_items_amount CHECK (payment_amount >= 0)
);

-- Indexes for performance
CREATE INDEX idx_payment_line_items_org_date ON payment_line_items(org_id, transaction_date) 
  WHERE isDeleted = false;
CREATE INDEX idx_payment_line_items_method ON payment_line_items(payment_method, transaction_date) 
  WHERE isDeleted = false;
CREATE INDEX idx_payment_line_items_reference ON payment_line_items(reference_table, reference_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_payment_line_items_customer ON payment_line_items(customer_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_payment_line_items_cashier ON payment_line_items(cashier_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_payment_line_items_branch ON payment_line_items(branch_id) 
  WHERE isDeleted = false;
CREATE INDEX idx_payment_line_items_credit_customer ON payment_line_items(credit_customer_id) 
  WHERE credit_customer_id IS NOT NULL AND isDeleted = false;
CREATE INDEX idx_payment_line_items_status ON payment_line_items(payment_status) 
  WHERE isDeleted = false;
CREATE INDEX idx_payment_line_items_date ON payment_line_items(transaction_date DESC) 
  WHERE isDeleted = false;

-- Composite index for payment method report
CREATE INDEX idx_payment_line_items_method_report 
  ON payment_line_items(org_id, payment_method, transaction_date)
  WHERE isDeleted = false;

COMMENT ON TABLE payment_line_items IS 'Normalized payment method details extracted from JSONB for reporting and analytics';
COMMENT ON COLUMN payment_line_items.payment_method IS 'Payment method: Cash, Credit, Debit, MPaisa, Cheque, Bank Transfer, etc.';
COMMENT ON COLUMN payment_line_items.credit_customer_id IS 'Customer receiving credit (if payment method is Credit)';
COMMENT ON COLUMN payment_line_items.credit_terms_days IS 'Credit payment terms in days';
COMMENT ON COLUMN payment_line_items.transaction_id IS 'External payment processor transaction ID';
COMMENT ON COLUMN payment_line_items.payment_status IS 'Payment status: completed, pending, failed, refunded';

-- ============================================================================
-- Helper Function: Get VAT Rate from Code
-- ============================================================================

CREATE OR REPLACE FUNCTION get_vat_rate_from_code(vat_code TEXT) 
RETURNS NUMERIC AS $$
BEGIN
  RETURN CASE 
    WHEN UPPER(vat_code) = 'G' THEN 0.125   -- General rate 12.5%
    WHEN UPPER(vat_code) = 'A' THEN 0.0000  -- Zero-rated
    WHEN UPPER(vat_code) = 'E' THEN 0.0000  -- Exempt
    WHEN UPPER(vat_code) = 'Z' THEN 0.0000  -- Zero-rated (alternate)
    ELSE 0.125  -- Default to general rate
  END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION get_vat_rate_from_code IS 'Convert VAT code (G, A, E, Z) to numeric rate';

-- ============================================================================
-- Trigger Function: Extract Sales Line Items from JSONB
-- ============================================================================

CREATE OR REPLACE FUNCTION extract_sales_line_items() RETURNS TRIGGER AS $$
DECLARE
  line_item JSONB;
  v_vat_code TEXT;
  v_vat_rate NUMERIC;
  v_category_id UUID;
  v_product_name TEXT;
  v_product_barcode TEXT;
  v_cashier_name TEXT;
  v_customer_name TEXT;
  v_customer_tin TEXT;
  v_quantity NUMERIC;
  v_unit_price NUMERIC;
  v_subtotal NUMERIC;
  v_discount_amount NUMERIC;
  v_vat_amount NUMERIC;
  v_line_total NUMERIC;
BEGIN
  -- Only process if line_items is not null and has elements
  IF NEW.line_items IS NOT NULL AND jsonb_array_length(NEW.line_items) > 0 THEN
    
    -- Get customer details if available
    IF NEW.customer_id IS NOT NULL THEN
      SELECT name, tin INTO v_customer_name, v_customer_tin
      FROM customers WHERE customer_id = NEW.customer_id;
    END IF;
    
    -- Get cashier name if available
    IF NEW.cashier_id IS NOT NULL THEN
      SELECT full_name INTO v_cashier_name
      FROM users WHERE user_id = NEW.cashier_id;
    END IF;
    
    -- Loop through each line item
    FOR line_item IN SELECT * FROM jsonb_array_elements(NEW.line_items)
    LOOP
      -- Skip if product_id is missing
      CONTINUE WHEN line_item->>'product_id' IS NULL;
      
      -- Get product details including VAT code
      SELECT 
        COALESCE(tax_rates->>'vat', 'G'),
        category_id,
        name,
        barcode
      INTO v_vat_code, v_category_id, v_product_name, v_product_barcode
      FROM products
      WHERE product_id = (line_item->>'product_id')::uuid;
      
      -- Calculate VAT rate
      v_vat_rate := get_vat_rate_from_code(v_vat_code);
      
      -- Extract quantities and prices
      v_quantity := COALESCE((line_item->>'quantity')::numeric, 0);
      v_unit_price := COALESCE((line_item->>'unit_price')::numeric, (line_item->>'price')::numeric, 0);
      v_subtotal := COALESCE((line_item->>'subtotal')::numeric, v_quantity * v_unit_price);
      v_discount_amount := COALESCE((line_item->>'discount_amount')::numeric, 0);
      v_vat_amount := COALESCE(
        (line_item->>'vat_amount')::numeric, 
        (v_subtotal - v_discount_amount) * v_vat_rate
      );
      v_line_total := COALESCE(
        (line_item->>'total')::numeric, 
        (v_subtotal - v_discount_amount) + v_vat_amount
      );
      
      -- Insert or update normalized line item
      INSERT INTO sales_line_items (
        org_id,
        branch_id,
        reference_table,
        reference_id,
        transaction_date,
        product_id,
        product_name,
        product_barcode,
        category_id,
        quantity,
        unit_type,
        quantity_in_base_units,
        unit_price,
        subtotal,
        discount_amount,
        discount_percentage,
        subtotal_after_discount,
        vat_code,
        vat_rate,
        vat_amount,
        line_total,
        customer_id,
        customer_name,
        customer_tin,
        cashier_id,
        cashier_name,
        notes
      ) VALUES (
        NEW.org_id,
        NEW.branch_id,
        TG_TABLE_NAME,
        NEW.sales_receipt_id,
        COALESCE(NEW.transaction_date, NEW.updatedAt),
        (line_item->>'product_id')::uuid,
        COALESCE(line_item->>'product_name', line_item->>'name', v_product_name),
        v_product_barcode,
        v_category_id,
        v_quantity,
        COALESCE(line_item->>'unit_type', 'PIECE'),
        COALESCE((line_item->>'quantity_in_base_units')::integer, v_quantity::integer),
        v_unit_price,
        v_subtotal,
        v_discount_amount,
        COALESCE((line_item->>'discount_percentage')::numeric, 0),
        v_subtotal - v_discount_amount,
        v_vat_code,
        v_vat_rate,
        v_vat_amount,
        v_line_total,
        NEW.customer_id,
        v_customer_name,
        v_customer_tin,
        NEW.cashier_id,
        v_cashier_name,
        line_item->>'notes'
      )
      ON CONFLICT ON CONSTRAINT idx_sales_line_items_unique 
      DO UPDATE SET
        quantity = EXCLUDED.quantity,
        unit_price = EXCLUDED.unit_price,
        subtotal = EXCLUDED.subtotal,
        discount_amount = EXCLUDED.discount_amount,
        subtotal_after_discount = EXCLUDED.subtotal_after_discount,
        vat_amount = EXCLUDED.vat_amount,
        line_total = EXCLUDED.line_total,
        updatedAt = now();
    END LOOP;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION extract_sales_line_items IS 'Automatically extract and normalize line items from JSONB to sales_line_items table';

-- Attach trigger to lineitems_payments
DROP TRIGGER IF EXISTS trg_extract_sales_line_items_lineitems_payments ON lineitems_payments;
CREATE TRIGGER trg_extract_sales_line_items_lineitems_payments
AFTER INSERT OR UPDATE OF line_items ON lineitems_payments
FOR EACH ROW EXECUTE FUNCTION extract_sales_line_items();

-- Attach trigger to invoices
DROP TRIGGER IF EXISTS trg_extract_sales_line_items_invoices ON invoices;
CREATE TRIGGER trg_extract_sales_line_items_invoices
AFTER INSERT OR UPDATE OF line_items ON invoices
FOR EACH ROW EXECUTE FUNCTION extract_sales_line_items();

-- ============================================================================
-- Trigger Function: Extract Payment Line Items from JSONB
-- ============================================================================

CREATE OR REPLACE FUNCTION extract_payment_line_items() RETURNS TRIGGER AS $$
DECLARE
  payment_item JSONB;
  v_cashier_name TEXT;
  v_customer_name TEXT;
  v_customer_tin TEXT;
  v_credit_customer_name TEXT;
BEGIN
  -- Only process if payments is not null and has elements
  IF NEW.payments IS NOT NULL AND jsonb_array_length(NEW.payments) > 0 THEN
    
    -- Get customer details if available
    IF NEW.customer_id IS NOT NULL THEN
      SELECT name, tin INTO v_customer_name, v_customer_tin
      FROM customers WHERE customer_id = NEW.customer_id;
    END IF;
    
    -- Get cashier name if available
    IF NEW.cashier_id IS NOT NULL THEN
      SELECT full_name INTO v_cashier_name
      FROM users WHERE user_id = NEW.cashier_id;
    END IF;
    
    -- Loop through each payment item
    FOR payment_item IN SELECT * FROM jsonb_array_elements(NEW.payments)
    LOOP
      -- Get credit customer name if payment method is Credit
      IF UPPER(payment_item->>'method') = 'CREDIT' AND payment_item->>'customer_id' IS NOT NULL THEN
        SELECT name INTO v_credit_customer_name
        FROM customers WHERE customer_id = (payment_item->>'customer_id')::uuid;
      END IF;
      
      -- Insert normalized payment item
      INSERT INTO payment_line_items (
        org_id,
        branch_id,
        reference_table,
        reference_id,
        transaction_date,
        payment_method,
        payment_amount,
        card_type,
        card_last_four,
        transaction_id,
        reference_number,
        credit_customer_id,
        credit_customer_name,
        credit_terms_days,
        customer_id,
        customer_name,
        customer_tin,
        cashier_id,
        cashier_name,
        notes,
        payment_status
      ) VALUES (
        NEW.org_id,
        NEW.branch_id,
        TG_TABLE_NAME,
        NEW.sales_receipt_id,
        COALESCE(NEW.transaction_date, NEW.updatedAt),
        COALESCE(payment_item->>'method', payment_item->>'type', 'Cash'),
        COALESCE((payment_item->>'amount')::numeric, 0),
        payment_item->>'card_type',
        payment_item->>'card_last_four',
        payment_item->>'transaction_id',
        payment_item->>'reference_number',
        NULLIF((payment_item->>'customer_id')::uuid, '00000000-0000-0000-0000-000000000000'::uuid),
        v_credit_customer_name,
        COALESCE((payment_item->>'credit_terms_days')::integer, 30),
        NEW.customer_id,
        v_customer_name,
        v_customer_tin,
        NEW.cashier_id,
        v_cashier_name,
        payment_item->>'notes',
        COALESCE(payment_item->>'status', 'completed')
      )
      ON CONFLICT DO NOTHING;
    END LOOP;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION extract_payment_line_items IS 'Automatically extract and normalize payment methods from JSONB to payment_line_items table';

-- Attach trigger to lineitems_payments
DROP TRIGGER IF EXISTS trg_extract_payment_line_items_lineitems_payments ON lineitems_payments;
CREATE TRIGGER trg_extract_payment_line_items_lineitems_payments
AFTER INSERT OR UPDATE OF payments ON lineitems_payments
FOR EACH ROW EXECUTE FUNCTION extract_payment_line_items();

-- ============================================================================
-- Materialized View: Product Movement Statistics
-- ============================================================================

CREATE MATERIALIZED VIEW IF NOT EXISTS product_movement_stats AS
SELECT 
  p.product_id,
  p.name AS product_name,
  p.barcode,
  p.org_id,
  p.category_id,
  pc.name AS category_name,
  
  -- Last sale information
  MAX(sli.transaction_date) FILTER (WHERE sli.quantity > 0) AS last_sale_date,
  
  -- 30-day statistics
  SUM(sli.quantity_in_base_units) FILTER (
    WHERE sli.transaction_date >= now() - interval '30 days'
  ) AS sales_30d,
  COUNT(DISTINCT sli.reference_id) FILTER (
    WHERE sli.transaction_date >= now() - interval '30 days'
  ) AS transaction_count_30d,
  SUM(sli.line_total) FILTER (
    WHERE sli.transaction_date >= now() - interval '30 days'
  ) AS revenue_30d,
  
  -- 90-day statistics
  SUM(sli.quantity_in_base_units) FILTER (
    WHERE sli.transaction_date >= now() - interval '90 days'
  ) AS sales_90d,
  COUNT(DISTINCT sli.reference_id) FILTER (
    WHERE sli.transaction_date >= now() - interval '90 days'
  ) AS transaction_count_90d,
  SUM(sli.line_total) FILTER (
    WHERE sli.transaction_date >= now() - interval '90 days'
  ) AS revenue_90d,
  
  -- All-time statistics
  SUM(sli.quantity_in_base_units) AS total_sales_quantity,
  SUM(sli.line_total) AS total_revenue,
  AVG(sli.unit_price) AS avg_unit_price,
  
  -- Current stock (from inventory)
  COALESCE(
    (SELECT SUM(i.current_qty)
     FROM inventory i
     JOIN product_batch_number pbn ON pbn.product_batch_number_id = i.product_batch_number_id
     WHERE pbn.product_id = p.product_id 
       AND i.isDeleted = false), 
    0
  ) AS current_stock,
  
  -- Timestamps
  now() AS last_refreshed_at
FROM products p
LEFT JOIN product_categories pc ON pc.category_id = p.category_id
LEFT JOIN sales_line_items sli ON sli.product_id = p.product_id AND sli.isDeleted = false
WHERE p.isDeleted = false
GROUP BY p.product_id, p.name, p.barcode, p.org_id, p.category_id, pc.name;

CREATE UNIQUE INDEX idx_movement_stats_product ON product_movement_stats(product_id);
CREATE INDEX idx_movement_stats_org ON product_movement_stats(org_id);
CREATE INDEX idx_movement_stats_category ON product_movement_stats(category_id);
CREATE INDEX idx_movement_stats_last_sale ON product_movement_stats(last_sale_date);
CREATE INDEX idx_movement_stats_sales_90d ON product_movement_stats(sales_90d DESC NULLS LAST);

COMMENT ON MATERIALIZED VIEW product_movement_stats IS 'Pre-calculated product movement statistics for fast reporting. Refresh daily.';

-- ============================================================================
-- Refresh Schedule Comment
-- ============================================================================

-- REFRESH COMMAND (run daily via cron/scheduler):
-- REFRESH MATERIALIZED VIEW CONCURRENTLY product_movement_stats;

COMMENT ON MATERIALIZED VIEW product_movement_stats IS 
'Product movement statistics aggregated from sales_line_items. 
Refresh command: REFRESH MATERIALIZED VIEW CONCURRENTLY product_movement_stats;
Recommended: Run daily at 2 AM via cron/scheduler.';

-- ============================================================================
-- Migration Function: Backfill Historical Data
-- ============================================================================

CREATE OR REPLACE FUNCTION migrate_historical_line_items(batch_size INT DEFAULT 1000) 
RETURNS TABLE(
  processed INT,
  sales_items_created INT,
  payment_items_created INT,
  errors INT
) AS $$
DECLARE
  v_processed INT := 0;
  v_sales_created INT := 0;
  v_payment_created INT := 0;
  v_errors INT := 0;
  rec RECORD;
BEGIN
  -- Process lineitems_payments in batches
  FOR rec IN 
    SELECT 
      lp.sales_receipt_id,
      lp.org_id,
      lp.branch_id,
      lp.cashier_id,
      lp.line_items,
      lp.payments,
      lp.customer_id,
      COALESCE(lp.transaction_date, lp.updatedAt) AS transaction_date
    FROM lineitems_payments lp
    WHERE lp.isDeleted = false
      AND lp.line_items IS NOT NULL
      AND jsonb_array_length(lp.line_items) > 0
      AND NOT EXISTS (
        SELECT 1 FROM sales_line_items sli 
        WHERE sli.reference_table = 'lineitems_payments' 
          AND sli.reference_id = lp.sales_receipt_id
      )
    ORDER BY lp.updatedAt DESC
    LIMIT batch_size
  LOOP
    BEGIN
      -- Trigger will handle extraction
      UPDATE lineitems_payments 
      SET updatedAt = updatedAt 
      WHERE sales_receipt_id = rec.sales_receipt_id;
      
      v_processed := v_processed + 1;
      
      -- Count created items
      v_sales_created := v_sales_created + (
        SELECT COUNT(*) FROM sales_line_items 
        WHERE reference_table = 'lineitems_payments' 
          AND reference_id = rec.sales_receipt_id
      );
      
      v_payment_created := v_payment_created + (
        SELECT COUNT(*) FROM payment_line_items 
        WHERE reference_table = 'lineitems_payments' 
          AND reference_id = rec.sales_receipt_id
      );
      
    EXCEPTION WHEN OTHERS THEN
      v_errors := v_errors + 1;
      RAISE NOTICE 'Error processing record %: %', rec.sales_receipt_id, SQLERRM;
    END;
  END LOOP;
  
  RETURN QUERY SELECT v_processed, v_sales_created, v_payment_created, v_errors;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION migrate_historical_line_items IS 
'Backfill historical line items from JSONB to normalized tables. 
Run in batches: SELECT * FROM migrate_historical_line_items(1000);';

-- ============================================================================
-- End of Phase 8 Migration
-- ============================================================================

-- To apply this migration:
-- 1. Review the schema changes
-- 2. Run this migration file: psql -U user -d database -f phase8_inventory_analytics.sql
-- 3. Run backfill: SELECT * FROM migrate_historical_line_items(1000); (repeat as needed)
-- 4. Refresh materialized view: REFRESH MATERIALIZED VIEW CONCURRENTLY product_movement_stats;
-- 5. Set up daily cron job to refresh materialized view
