-- ============================================================================
-- Phase 7: Finance Integration - Aging Reports Database Views
-- ============================================================================
-- Creates views for accounts receivable aging analysis
--
-- Views Created:
-- 1. invoice_aging - Main aging report with bucket classification
-- 2. aging_summary_by_org - Organization-level aging summary
-- 3. aging_by_customer - Customer-level aging aggregation
--
-- Author: TAF Development Team
-- Date: October 17, 2025
-- Phase: 7 - Finance Integration
-- Task: 1 - Aging Reports
-- ============================================================================

-- Drop existing views if they exist
DROP VIEW IF EXISTS aging_by_customer CASCADE;
DROP VIEW IF EXISTS aging_summary_by_org CASCADE;
DROP VIEW IF EXISTS invoice_aging CASCADE;

-- ============================================================================
-- View 1: Invoice Aging - Main aging report view
-- ============================================================================
-- This view provides a comprehensive aging analysis of all outstanding invoices
-- Aging buckets: current, 0-30, 30-60, 60-90, 90+ days overdue
--
-- Note: The TAF schema uses reference_table/reference_id to link invoices:
--  - reference_table = 'quotations' -> quotation_id contains customer_id
--  - reference_table = 'customers' -> direct customer link
--  - reference_table = 'clients' -> direct client link  
-- We calculate due_date as invoice_date + 30 days (can be enhanced with payment terms)
-- ============================================================================

CREATE OR REPLACE VIEW invoice_aging AS
SELECT 
    -- Invoice details
    i.invoice_id,
    i.invoice_number,
    i.org_id,
    
    -- Customer information (resolved through reference chain)
    COALESCE(
        q.customer_id,
        CASE WHEN i.reference_table = 'customers' THEN i.reference_id END,
        CASE WHEN i.reference_table = 'clients' THEN i.reference_id END
    ) as customer_id,
    COALESCE(cust.name, cli.name, 'Unknown Customer') as customer_name,
    COALESCE(cust.email, cli.email) as customer_email,
    COALESCE(cust.phone_number, cli.phone) as customer_phone,
    
    -- Invoice dates and amounts
    i.invoice_date,
    -- Calculate due_date: invoice_date + 30 days (default payment terms)
    -- TODO: Enhance with actual payment terms when available
    (i.invoice_date + INTERVAL '30 days')::date as due_date,
    i.amount as total_amount,
    COALESCE(i.amount_paid, 0) as paid_amount,
    (i.amount - COALESCE(i.amount_paid, 0)) as outstanding_amount,
    i.status,
    
    -- Aging calculations
    CURRENT_DATE - (i.invoice_date + INTERVAL '30 days')::date as days_overdue,
    
    -- Aging bucket classification
    CASE 
        WHEN i.status = 'paid' THEN 'paid'
        WHEN (i.invoice_date + INTERVAL '30 days')::date >= CURRENT_DATE THEN 'current'
        WHEN (CURRENT_DATE - (i.invoice_date + INTERVAL '30 days')::date) BETWEEN 1 AND 30 THEN '0-30'
        WHEN (CURRENT_DATE - (i.invoice_date + INTERVAL '30 days')::date) BETWEEN 31 AND 60 THEN '30-60'
        WHEN (CURRENT_DATE - (i.invoice_date + INTERVAL '30 days')::date) BETWEEN 61 AND 90 THEN '60-90'
        WHEN (CURRENT_DATE - (i.invoice_date + INTERVAL '30 days')::date) > 90 THEN '90+'
        ELSE 'current'
    END as aging_bucket,
    
    -- Payment status indicator
    CASE 
        WHEN i.status = 'paid' THEN 'paid'
        WHEN i.status = 'cancelled' THEN 'cancelled'
        WHEN (i.amount - COALESCE(i.amount_paid, 0)) <= 0 THEN 'paid'
        WHEN COALESCE(i.amount_paid, 0) > 0 THEN 'partial'
        WHEN (i.invoice_date + INTERVAL '30 days')::date < CURRENT_DATE THEN 'overdue'
        ELSE 'current'
    END as payment_status,
    
    -- Metadata
    i.sent_at,
    i.taxcore_submitted_at,
    i.updatedat as last_updated
    
FROM invoices i
-- Join to quotations to get customer_id
LEFT JOIN quotations q 
    ON i.reference_table = 'quotations' AND i.reference_id = q.quotation_id
-- Join to customers (either from quotation or direct reference)
LEFT JOIN customers cust 
    ON COALESCE(q.customer_id, CASE WHEN i.reference_table = 'customers' THEN i.reference_id END) = cust.customer_id
-- Join to clients (for client-based invoices)
LEFT JOIN clients cli 
    ON CASE WHEN i.reference_table = 'clients' THEN i.reference_id END = cli.client_id
WHERE i.isdeleted = false
  AND i.status IS NOT NULL
  AND i.amount > 0; -- Only include invoices with amounts

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_invoices_aging_lookup 
ON invoices(org_id, invoice_date, status) 
WHERE isdeleted = false AND amount > 0;

CREATE INDEX IF NOT EXISTS idx_invoices_reference_lookup
ON invoices(reference_table, reference_id)
WHERE isdeleted = false;

-- ============================================================================
-- View 2: Aging Summary by Organization
-- ============================================================================
-- Provides a summary of aging data at the organization level
-- Useful for dashboard widgets showing total outstanding by aging bucket
-- ============================================================================

CREATE OR REPLACE VIEW aging_summary_by_org AS
SELECT 
    org_id,
    
    -- Total counts and amounts
    COUNT(*) as total_invoices,
    SUM(outstanding_amount) as total_outstanding,
    
    -- Aging bucket amounts
    SUM(CASE WHEN aging_bucket = 'current' THEN outstanding_amount ELSE 0 END) as current_amount,
    SUM(CASE WHEN aging_bucket = '0-30' THEN outstanding_amount ELSE 0 END) as overdue_0_30,
    SUM(CASE WHEN aging_bucket = '30-60' THEN outstanding_amount ELSE 0 END) as overdue_30_60,
    SUM(CASE WHEN aging_bucket = '60-90' THEN outstanding_amount ELSE 0 END) as overdue_60_90,
    SUM(CASE WHEN aging_bucket = '90+' THEN outstanding_amount ELSE 0 END) as overdue_90_plus,
    
    -- Aging bucket counts
    COUNT(CASE WHEN aging_bucket = 'current' THEN 1 END) as current_count,
    COUNT(CASE WHEN aging_bucket = '0-30' THEN 1 END) as overdue_0_30_count,
    COUNT(CASE WHEN aging_bucket = '30-60' THEN 1 END) as overdue_30_60_count,
    COUNT(CASE WHEN aging_bucket = '60-90' THEN 1 END) as overdue_60_90_count,
    COUNT(CASE WHEN aging_bucket = '90+' THEN 1 END) as overdue_90_plus_count,
    
    -- Average days overdue
    AVG(CASE WHEN aging_bucket != 'current' AND aging_bucket != 'paid' THEN days_overdue END) as avg_days_overdue,
    MAX(days_overdue) as max_days_overdue,
    
    -- Last updated
    MAX(last_updated) as last_updated
    
FROM invoice_aging
WHERE aging_bucket != 'paid'  -- Exclude paid invoices from aging analysis
GROUP BY org_id;

-- ============================================================================
-- View 3: Aging by Customer
-- ============================================================================
-- Provides aging data aggregated by customer
-- Useful for customer-level drill-down reports and identifying problem customers
-- ============================================================================

CREATE OR REPLACE VIEW aging_by_customer AS
SELECT 
    org_id,
    customer_id,
    customer_name,
    customer_email,
    customer_phone,
    
    -- Invoice counts and totals
    COUNT(*) as invoice_count,
    SUM(outstanding_amount) as total_outstanding,
    
    -- Aging statistics
    AVG(days_overdue) as avg_days_overdue,
    MAX(days_overdue) as max_days_overdue,
    
    -- Aging bucket breakdown
    SUM(CASE WHEN aging_bucket = 'current' THEN outstanding_amount ELSE 0 END) as current_amount,
    SUM(CASE WHEN aging_bucket = '0-30' THEN outstanding_amount ELSE 0 END) as overdue_0_30,
    SUM(CASE WHEN aging_bucket = '30-60' THEN outstanding_amount ELSE 0 END) as overdue_30_60,
    SUM(CASE WHEN aging_bucket = '60-90' THEN outstanding_amount ELSE 0 END) as overdue_60_90,
    SUM(CASE WHEN aging_bucket = '90+' THEN outstanding_amount ELSE 0 END) as overdue_90_plus,
    
    -- Payment tracking
    MIN(invoice_date) as first_invoice_date,
    MAX(invoice_date) as last_invoice_date,
    MAX(sent_at) as last_sent_date,
    
    -- Metadata
    MAX(last_updated) as last_updated
    
FROM invoice_aging
WHERE aging_bucket != 'paid'
  AND customer_id IS NOT NULL
GROUP BY org_id, customer_id, customer_name, customer_email, customer_phone;

-- Create index for customer aging lookups
CREATE INDEX IF NOT EXISTS idx_quotations_customer_org
ON quotations(customer_id, org_id)
WHERE isdeleted = false;

-- ============================================================================
-- Optional: Materialized View for Large Datasets
-- ============================================================================
-- For organizations with 100k+ invoices, consider using materialized views
-- for better performance. Uncomment the following to enable:
--
-- CREATE MATERIALIZED VIEW IF NOT EXISTS mv_invoice_aging AS
-- SELECT * FROM invoice_aging;
--
-- CREATE UNIQUE INDEX ON mv_invoice_aging (invoice_id);
-- CREATE INDEX ON mv_invoice_aging (org_id, aging_bucket);
-- CREATE INDEX ON mv_invoice_aging (customer_id);
--
-- -- Refresh materialized view (run via cron hourly)
-- -- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_invoice_aging;
-- ============================================================================

-- ============================================================================
-- Verification
-- ============================================================================
DO $$
DECLARE
    view_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO view_count
    FROM information_schema.views
    WHERE table_schema = 'public'
      AND table_name IN ('invoice_aging', 'aging_summary_by_org', 'aging_by_customer');
    
    IF view_count <  3 THEN
        RAISE WARNING 'Expected 3 views, found %', view_count;
    ELSE
        RAISE NOTICE 'Successfully created % aging views', view_count;
    END IF;
END $$;

-- Display sample data from views
SELECT 
    'invoice_aging' as view_name,
    COUNT(*) as row_count,
    COUNT(DISTINCT org_id) as org_count,
    COUNT(DISTINCT customer_id) as customer_count
FROM invoice_aging

UNION ALL

SELECT 
    'aging_summary_by_org' as view_name,
    COUNT(*) as row_count,
    COUNT(DISTINCT org_id) as org_count,
    0 as customer_count
FROM aging_summary_by_org

UNION ALL

SELECT 
    'aging_by_customer' as view_name,
    COUNT(*) as row_count,
    COUNT(DISTINCT org_id) as org_count,
    COUNT(DISTINCT customer_id) as customer_count
FROM aging_by_customer;

-- Display aging distribution
SELECT 
    aging_bucket,
    COUNT(*) as invoice_count,
    SUM(outstanding_amount) as total_outstanding,
    ROUND(AVG(days_overdue), 1) as avg_days_overdue
FROM invoice_aging
WHERE aging_bucket != 'paid'
GROUP BY aging_bucket
ORDER BY 
    CASE aging_bucket
        WHEN 'current' THEN 1
        WHEN '0-30' THEN 2
        WHEN '30-60' THEN 3
        WHEN '60-90' THEN 4
        WHEN '90+' THEN 5
    END;

-- ============================================================================
-- Deployment Instructions
-- ============================================================================
-- 1. Deploy this migration:
--    docker cp migrations/phase7_aging_reports_v2.sql postgres:/tmp/
--    docker exec postgres psql -U taf -d devdb -f /tmp/phase7_aging_reports_v2.sql
--
-- 2. Verify views created:
--    docker exec postgres psql -U taf -d devdb -c "SELECT table_name FROM information_schema.views WHERE table_name LIKE 'aging%'"
--
-- 3. Test queries:
--    SELECT * FROM invoice_aging LIMIT 10;
--    SELECT * FROM aging_summary_by_org;
--    SELECT * FROM aging_by_customer ORDER BY total_outstanding DESC LIMIT 10;
--
-- 4. Next steps:
--    - Deploy FinanceController.php
--    - Deploy phase7_finance_modules.sql
--    - Test API endpoints
-- ============================================================================
