-- ============================================================================
-- Phase 7 Task 1: Aging Reports - Database View
-- ============================================================================
-- Creates invoice_aging view for accounts receivable aging analysis
-- 
-- Created: October 17, 2025
-- Status: Production Ready
-- ============================================================================

-- Drop existing view if exists
DROP VIEW IF EXISTS invoice_aging CASCADE;

-- Create invoice_aging view
CREATE OR REPLACE VIEW invoice_aging AS
SELECT 
    i.invoice_id,
    i.invoice_number,
    i.org_id,
    i.customer_id,
    c.name as customer_name,
    c.email as customer_email,
    c.phone as customer_phone,
    i.invoice_date,
    i.due_date,
    i.total_amount,
    i.status,
    
    -- Calculate paid amount from payments table
    COALESCE(SUM(p.amount), 0) as paid_amount,
    
    -- Calculate outstanding amount
    i.total_amount - COALESCE(SUM(p.amount), 0) as outstanding_amount,
    
    -- Calculate days overdue (negative if not due yet)
    CURRENT_DATE - i.due_date as days_overdue,
    
    -- Aging bucket classification
    CASE 
        WHEN i.status = 'paid' THEN 'paid'
        WHEN i.due_date >= CURRENT_DATE THEN 'current'
        WHEN CURRENT_DATE - i.due_date BETWEEN 1 AND 30 THEN '0-30'
        WHEN CURRENT_DATE - i.due_date BETWEEN 31 AND 60 THEN '30-60'
        WHEN CURRENT_DATE - i.due_date BETWEEN 61 AND 90 THEN '60-90'
        WHEN CURRENT_DATE - i.due_date > 90 THEN '90+'
        ELSE 'current'
    END as aging_bucket,
    
    -- Payment status
    CASE 
        WHEN i.status = 'paid' THEN 'paid'
        WHEN i.status = 'cancelled' THEN 'cancelled'
        WHEN i.total_amount - COALESCE(SUM(p.amount), 0) = 0 THEN 'paid'
        WHEN COALESCE(SUM(p.amount), 0) > 0 THEN 'partial'
        WHEN i.due_date < CURRENT_DATE THEN 'overdue'
        ELSE 'current'
    END as payment_status,
    
    -- Last payment information
    MAX(p.payment_date) as last_payment_date,
    MAX(p.amount) as last_payment_amount,
    
    -- Invoice metadata
    i.created_at as invoice_created_at,
    i.updatedat as invoice_updated_at

FROM invoices i
LEFT JOIN customers c ON i.customer_id = c.customer_id
LEFT JOIN payments p ON i.invoice_id = p.invoice_id AND p.isdeleted = false
WHERE i.isdeleted = false
  AND i.status IN ('finalized', 'overdue', 'partial', 'paid')
GROUP BY 
    i.invoice_id, 
    i.invoice_number,
    i.org_id,
    i.customer_id,
    c.name,
    c.email,
    c.phone,
    i.invoice_date,
    i.due_date,
    i.total_amount,
    i.status,
    i.created_at,
    i.updatedat;

-- Create index on the view for performance
CREATE INDEX IF NOT EXISTS idx_invoices_aging_lookup 
ON invoices(org_id, due_date, status) 
WHERE isdeleted = false AND status IN ('finalized', 'overdue', 'partial', 'paid');

-- Create index for customer lookups
CREATE INDEX IF NOT EXISTS idx_invoices_customer_aging 
ON invoices(customer_id, due_date) 
WHERE isdeleted = false AND status IN ('finalized', 'overdue', 'partial', 'paid');

-- Comment on view
COMMENT ON VIEW invoice_aging IS 'Accounts receivable aging analysis view with aging buckets (current, 0-30, 30-60, 60-90, 90+ days overdue)';

-- Verification query
SELECT 
    aging_bucket,
    payment_status,
    COUNT(*) as invoice_count,
    SUM(outstanding_amount) as total_outstanding,
    AVG(days_overdue) as avg_days_overdue
FROM invoice_aging
GROUP BY aging_bucket, payment_status
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
        WHEN 'paid' THEN 6
    END;

-- ============================================================================
-- MATERIALIZED VIEW (Optional - for large datasets)
-- ============================================================================
-- If performance is an issue with large datasets (100k+ invoices),
-- uncomment this section to create a materialized view that can be refreshed periodically

/*
DROP MATERIALIZED VIEW IF EXISTS invoice_aging_materialized CASCADE;

CREATE MATERIALIZED VIEW invoice_aging_materialized AS
SELECT * FROM invoice_aging;

-- Create indexes on materialized view
CREATE INDEX idx_invoice_aging_mat_org ON invoice_aging_materialized(org_id);
CREATE INDEX idx_invoice_aging_mat_customer ON invoice_aging_materialized(org_id, customer_id);
CREATE INDEX idx_invoice_aging_mat_bucket ON invoice_aging_materialized(org_id, aging_bucket);
CREATE INDEX idx_invoice_aging_mat_status ON invoice_aging_materialized(payment_status);

-- Refresh command (run this via cron every hour or on-demand)
-- REFRESH MATERIALIZED VIEW invoice_aging_materialized;
*/

-- ============================================================================
-- HELPER VIEWS
-- ============================================================================

-- Aging summary by organization
CREATE OR REPLACE VIEW aging_summary_by_org AS
SELECT 
    org_id,
    COUNT(*) as total_invoices,
    SUM(outstanding_amount) as total_outstanding,
    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,
    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
FROM invoice_aging
WHERE aging_bucket != 'paid'
GROUP BY org_id;

COMMENT ON VIEW aging_summary_by_org IS 'Aging report summary aggregated by organization';

-- Aging by customer
CREATE OR REPLACE VIEW aging_by_customer AS
SELECT 
    org_id,
    customer_id,
    customer_name,
    customer_email,
    customer_phone,
    COUNT(*) as invoice_count,
    SUM(outstanding_amount) as total_outstanding,
    AVG(days_overdue) FILTER (WHERE days_overdue > 0) as avg_days_overdue,
    MAX(days_overdue) as max_days_overdue,
    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,
    MAX(last_payment_date) as last_payment_date,
    SUM(last_payment_amount) as last_payment_total
FROM invoice_aging
WHERE aging_bucket != 'paid'
GROUP BY org_id, customer_id, customer_name, customer_email, customer_phone;

COMMENT ON VIEW aging_by_customer IS 'Aging report aggregated by customer';

-- ============================================================================
-- VERIFICATION AND TESTING
-- ============================================================================

DO $$
DECLARE
    view_count INTEGER;
BEGIN
    -- Check if view was created
    SELECT COUNT(*) INTO view_count
    FROM information_schema.views
    WHERE table_name IN ('invoice_aging', 'aging_summary_by_org', 'aging_by_customer');
    
    IF view_count = 3 THEN
        RAISE NOTICE '✅ Successfully created 3 aging report views';
    ELSE
        RAISE WARNING 'Expected 3 views, found %', view_count;
    END IF;
END $$;

-- Display sample data
SELECT 
    'Sample Aging Data' as report_name,
    aging_bucket,
    COUNT(*) as count,
    SUM(outstanding_amount)::DECIMAL(15,2) as total
FROM invoice_aging
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
        WHEN 'paid' THEN 6
    END;

-- ============================================================================
-- END OF PHASE 7 TASK 1 - AGING REPORTS DATABASE VIEW
-- ============================================================================
