-- ============================================================================
-- Phase 8: Inventory Analytics Module Registration
-- Purpose: Register 10 analytics modules that use existing controllers
-- ============================================================================

-- Insert Analytics Module Category
INSERT INTO modules (module_id, name, description, icon, sort_order, isDeleted, updatedAt)
VALUES 
  (uuid_generate_v7(), 'Inventory Analytics', 'Sales and inventory analytics dashboard', 'fa-chart-bar', 100, false, now())
ON CONFLICT DO NOTHING;

-- Get the parent module_id for reference
DO $$
DECLARE
  v_parent_module_id uuid;
BEGIN
  SELECT module_id INTO v_parent_module_id 
  FROM modules 
  WHERE name = 'Inventory Analytics' 
  AND isDeleted = false 
  LIMIT 1;

  -- Module 1: VAT Summary Report
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'VAT Summary Report',
    'VAT collected by tax code and rate',
    'fa-file-invoice',
    1,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'vat_code,vat_rate',
        'columns', 'subtotal_after_discount,vat_amount,line_total',
        'functions', 'sum,sum,sum',
        'orderBy', 'vat_code'
      ),
      'display', jsonb_build_object(
        'type', 'table',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'vat_code', 'label', 'VAT Code'),
          jsonb_build_object('field', 'vat_rate', 'label', 'Rate', 'format', 'percentage'),
          jsonb_build_object('field', 'sum_subtotal_after_discount', 'label', 'Taxable Amount', 'format', 'currency'),
          jsonb_build_object('field', 'sum_vat_amount', 'label', 'VAT Amount', 'format', 'currency'),
          jsonb_build_object('field', 'sum_line_total', 'label', 'Total', 'format', 'currency')
        ),
        'showTotals', true
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel', 'pdf')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 2: Payment Method Summary
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Payment Method Summary',
    'Payment distribution by method',
    'fa-credit-card',
    2,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'payment_line_items',
        'groupBy', 'payment_method',
        'columns', 'payment_amount',
        'functions', 'sum,count',
        'orderBy', 'sum_payment_amount',
        'orderDir', 'DESC'
      ),
      'display', jsonb_build_object(
        'type', 'table_chart',
        'chartType', 'doughnut',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'payment_method', 'label', 'Payment Method'),
          jsonb_build_object('field', 'count', 'label', 'Transactions', 'format', 'number'),
          jsonb_build_object('field', 'sum_payment_amount', 'label', 'Total Amount', 'format', 'currency')
        ),
        'showPercentages', true
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'branch_id', 'type', 'select', 'label', 'Branch'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 3: Daily Sales Summary
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Daily Sales Summary',
    'Daily revenue and transaction trends',
    'fa-chart-line',
    3,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'DATE(transaction_date)',
        'columns', 'line_total,quantity_in_base_units',
        'functions', 'sum,sum,count',
        'orderBy', 'DATE(transaction_date)',
        'orderDir', 'DESC'
      ),
      'display', jsonb_build_object(
        'type', 'chart_stats',
        'chartType', 'line',
        'stats', jsonb_build_array(
          jsonb_build_object('label', 'Total Days', 'value', 'count(*)'),
          jsonb_build_object('label', 'Total Revenue', 'value', 'sum(line_total)', 'format', 'currency'),
          jsonb_build_object('label', 'Avg Daily Revenue', 'value', 'avg(line_total)', 'format', 'currency')
        )
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'branch_id', 'type', 'select', 'label', 'Branch'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel', 'chart_image')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 4: Top 10 Products by Revenue
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Top Products by Revenue',
    'Best selling products ranked by revenue',
    'fa-trophy',
    4,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'product_id,product_name,product_barcode',
        'columns', 'line_total,quantity_in_base_units',
        'functions', 'sum,sum,count',
        'orderBy', 'sum_line_total',
        'orderDir', 'DESC',
        'limit', 10
      ),
      'display', jsonb_build_object(
        'type', 'ranked_table_chart',
        'chartType', 'horizontal_bar',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'product_name', 'label', 'Product'),
          jsonb_build_object('field', 'product_barcode', 'label', 'Barcode'),
          jsonb_build_object('field', 'sum_quantity_in_base_units', 'label', 'Units Sold', 'format', 'number'),
          jsonb_build_object('field', 'count', 'label', 'Transactions', 'format', 'number'),
          jsonb_build_object('field', 'sum_line_total', 'label', 'Revenue', 'format', 'currency')
        ),
        'showRank', true,
        'showPercentages', true
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'category_id', 'type', 'select', 'label', 'Category'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel', 'pdf')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 5: Cashier Performance
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Cashier Performance',
    'Sales performance by cashier',
    'fa-user-tie',
    5,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'cashier_id,cashier_name',
        'columns', 'line_total,quantity_in_base_units',
        'functions', 'sum,sum,count',
        'orderBy', 'sum_line_total',
        'orderDir', 'DESC'
      ),
      'display', jsonb_build_object(
        'type', 'performance_table',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'cashier_name', 'label', 'Cashier'),
          jsonb_build_object('field', 'count', 'label', 'Transactions', 'format', 'number'),
          jsonb_build_object('field', 'sum_quantity_in_base_units', 'label', 'Units Sold', 'format', 'number'),
          jsonb_build_object('field', 'sum_line_total', 'label', 'Total Revenue', 'format', 'currency'),
          jsonb_build_object('field', 'avg_transaction', 'label', 'Avg Transaction', 'format', 'currency', 'computed', 'sum_line_total / count')
        ),
        'showTotals', true
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'branch_id', 'type', 'select', 'label', 'Branch'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 6: Customer Purchase Summary
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Customer Purchase Summary',
    'Top customers by revenue',
    'fa-users',
    6,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'customer_id,customer_name,customer_tin',
        'columns', 'line_total',
        'functions', 'sum,count',
        'orderBy', 'sum_line_total',
        'orderDir', 'DESC',
        'limit', 20
      ),
      'display', jsonb_build_object(
        'type', 'table',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'customer_name', 'label', 'Customer'),
          jsonb_build_object('field', 'customer_tin', 'label', 'TIN'),
          jsonb_build_object('field', 'count', 'label', 'Transactions', 'format', 'number'),
          jsonb_build_object('field', 'sum_line_total', 'label', 'Total Revenue', 'format', 'currency')
        )
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 7: Branch Performance Comparison
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Branch Performance',
    'Sales comparison by branch',
    'fa-building',
    7,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'branch_id',
        'columns', 'line_total,quantity_in_base_units',
        'functions', 'sum,sum,count',
        'orderBy', 'sum_line_total',
        'orderDir', 'DESC'
      ),
      'display', jsonb_build_object(
        'type', 'table_chart',
        'chartType', 'bar',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'branch_name', 'label', 'Branch', 'lookup', 'branches.name'),
          jsonb_build_object('field', 'count', 'label', 'Transactions', 'format', 'number'),
          jsonb_build_object('field', 'sum_line_total', 'label', 'Revenue', 'format', 'currency')
        )
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 8: Hourly Sales Pattern
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Hourly Sales Pattern',
    'Sales distribution by hour of day',
    'fa-clock',
    8,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'EXTRACT(HOUR FROM transaction_date)',
        'columns', 'line_total',
        'functions', 'sum,count',
        'orderBy', 'EXTRACT(HOUR FROM transaction_date)'
      ),
      'display', jsonb_build_object(
        'type', 'chart',
        'chartType', 'bar',
        'xAxis', 'hour',
        'yAxis', 'sum_line_total',
        'title', 'Revenue by Hour of Day'
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'branch_id', 'type', 'select', 'label', 'Branch'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'chart_image')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 9: Transaction Export
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'Full Transaction Export',
    'Export complete transaction data',
    'fa-download',
    9,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'export',
      'controller', 'generic',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'orderBy', 'transaction_date',
        'orderDir', 'DESC',
        'limit', 10000
      ),
      'display', jsonb_build_object(
        'type', 'table',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'transaction_date', 'label', 'Date', 'format', 'datetime'),
          jsonb_build_object('field', 'transaction_number', 'label', 'Transaction #'),
          jsonb_build_object('field', 'product_name', 'label', 'Product'),
          jsonb_build_object('field', 'quantity', 'label', 'Quantity', 'format', 'number'),
          jsonb_build_object('field', 'unit_price', 'label', 'Unit Price', 'format', 'currency'),
          jsonb_build_object('field', 'line_total', 'label', 'Total', 'format', 'currency'),
          jsonb_build_object('field', 'customer_name', 'label', 'Customer'),
          jsonb_build_object('field', 'cashier_name', 'label', 'Cashier')
        ),
        'pagination', true,
        'pageSize', 100
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'branch_id', 'type', 'select', 'label', 'Branch'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range'),
        jsonb_build_object('field', 'customer_id', 'type', 'autocomplete', 'label', 'Customer')
      ),
      'exports', jsonb_build_array('csv', 'excel')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

  -- Module 10: VAT by Category
  INSERT INTO modules (module_id, name, description, icon, sort_order, parent_module_id, config, isDeleted, updatedAt)
  VALUES (
    uuid_generate_v7(),
    'VAT by Category',
    'VAT breakdown by product category',
    'fa-layer-group',
    10,
    v_parent_module_id,
    jsonb_build_object(
      'type', 'analytics',
      'controller', 'aggregate_rows',
      'dataSource', jsonb_build_object(
        'table', 'sales_line_items',
        'groupBy', 'category_id,vat_code',
        'columns', 'subtotal_after_discount,vat_amount,line_total',
        'functions', 'sum,sum,sum',
        'orderBy', 'sum_line_total',
        'orderDir', 'DESC'
      ),
      'display', jsonb_build_object(
        'type', 'table',
        'columns', jsonb_build_array(
          jsonb_build_object('field', 'category_name', 'label', 'Category', 'lookup', 'product_categories.name'),
          jsonb_build_object('field', 'vat_code', 'label', 'VAT Code'),
          jsonb_build_object('field', 'sum_subtotal_after_discount', 'label', 'Taxable Amount', 'format', 'currency'),
          jsonb_build_object('field', 'sum_vat_amount', 'label', 'VAT Amount', 'format', 'currency'),
          jsonb_build_object('field', 'sum_line_total', 'label', 'Total', 'format', 'currency')
        ),
        'showTotals', true
      ),
      'filters', jsonb_build_array(
        jsonb_build_object('field', 'org_id', 'type', 'select', 'label', 'Organization'),
        jsonb_build_object('field', 'transaction_date', 'type', 'daterange', 'label', 'Date Range')
      ),
      'exports', jsonb_build_array('csv', 'excel')
    ),
    false,
    now()
  )
  ON CONFLICT DO NOTHING;

END $$;

-- ============================================================================
-- Create indexes to support analytics queries
-- ============================================================================

-- Composite index for VAT by category report
CREATE INDEX IF NOT EXISTS idx_sales_line_items_category_vat 
  ON sales_line_items(category_id, vat_code, transaction_date)
  WHERE isDeleted = false;

-- Index for hourly pattern analysis
CREATE INDEX IF NOT EXISTS idx_sales_line_items_hour 
  ON sales_line_items(EXTRACT(HOUR FROM transaction_date))
  WHERE isDeleted = false;

-- Index for customer analysis
CREATE INDEX IF NOT EXISTS idx_sales_line_items_customer_date 
  ON sales_line_items(customer_id, transaction_date)
  WHERE isDeleted = false AND customer_id IS NOT NULL;

-- ============================================================================
-- Grant permissions for analytics modules
-- ============================================================================

COMMENT ON TABLE sales_line_items IS 'Phase 8: Normalized sales line items for analytics. Populated automatically by triggers.';
COMMENT ON TABLE payment_line_items IS 'Phase 8: Normalized payment line items for analytics. Populated automatically by triggers.';

-- Success message
DO $$
BEGIN
  RAISE NOTICE 'Phase 8 Inventory Analytics modules registered successfully';
  RAISE NOTICE '10 modules created using GenericController and AggregateRowsController';
  RAISE NOTICE 'Access via: /FrontEnd/pages/inventory-analytics.html';
END $$;
