-- ============================================================================
-- Phase 7 Task 1: Invoice Aging Reports
-- ============================================================================
-- Creates database view and supporting structures for invoice aging analysis
--
-- Components:
--   1. invoice_aging view - Calculates aging for all invoices
--   2. Helper function - age_bucket calculation
--   3. Indexes for performance
--
-- Created: October 17, 2025
-- Status: Production Ready
-- ============================================================================

-- Drop existing objects if they exist
DROP VIEW IF EXISTS invoice_aging CASCADE;
DROP FUNCTION IF EXISTS get_age_bucket(integer) CASCADE;

-- ============================================================================
-- FUNCTION: get_age_bucket
-- ============================================================================
-- Returns aging bucket name based on days overdue
-- Buckets: Current, 1-30, 31-60, 61-90, 90+ days
CREATE OR REPLACE FUNCTION get_age_bucket(days_overdue integer)
RETURNS text AS $$
BEGIN
    IF days_overdue <= 0 THEN
        RETURN 'Current';
    ELSIF days_overdue <= 30 THEN
        RETURN '1-30 days';
    ELSIF days_overdue <= 60 THEN
        RETURN '31-60 days';
    ELSIF days_overdue <= 90 THEN
        RETURN '61-90 days';
    ELSE
        RETURN '90+ days';
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- ============================================================================
-- VIEW: invoice_aging
-- ============================================================================
-- Comprehensive invoice aging view with customer details and payment history
CREATE OR REPLACE VIEW invoice_aging AS
SELECT 
    -- Invoice identification
    i.invoice_id,
    i.invoice_number,
    i.invoice_date,
    i.due_date,
    i.status,
    i.org_id,
    
    -- Customer information
    c.customer_id,
    c.customer_name,
    c.email as customer_email,
    c.phone as customer_phone,
    
    -- Financial amounts
    i.subtotal,
    i.tax_amount,
    i.total_amount,
    COALESCE(i.amount_paid, 0) as amount_paid,
    (i.total_amount - COALESCE(i.amount_paid, 0)) as balance_due,
    
    -- Aging calculation
    CASE 
        WHEN i.status = 'paid' THEN 0
        ELSE GREATEST(0, CURRENT_DATE - i.due_date)
    END as days_overdue,
    
    -- Age bucket
    CASE 
        WHEN i.status = 'paid' THEN 'Paid'
        ELSE get_age_bucket(CURRENT_DATE - i.due_date)
    END as age_bucket,
    
    -- Payment information
    i.last_payment_date,
    i.payment_terms,
    
    -- Status flags
    CASE 
        WHEN i.status = 'paid' THEN false
        WHEN CURRENT_DATE > i.due_date THEN true
        ELSE false
    END as is_overdue,
    
    CASE 
        WHEN i.status = 'paid' THEN false
        WHEN CURRENT_DATE - i.due_date > 90 THEN true
        ELSE false
    END as is_severely_overdue,
    
    -- Timestamps
    i.created_at,
    i.updated_at,
    i.finalized_at
    
FROM invoices i
LEFT JOIN customers c ON i.customer_id = c.customer_id
WHERE i.status IN ('finalized', 'paid', 'overdue')
  AND i.isdeleted = false
  AND c.isdeleted = false;

-- Add comment
COMMENT ON VIEW invoice_aging IS 'Invoice aging analysis with customer details and payment tracking';

-- ============================================================================
-- INDEXES for Performance
-- ============================================================================

-- Index on invoice due_date for aging calculations
CREATE INDEX IF NOT EXISTS idx_invoices_due_date 
ON invoices(due_date) 
WHERE status IN ('finalized', 'overdue') AND isdeleted = false;

-- Index on invoice status and org_id for filtering
CREATE INDEX IF NOT EXISTS idx_invoices_status_org 
ON invoices(status, org_id) 
WHERE isdeleted = false;

-- Index on customer_id for joins
CREATE INDEX IF NOT EXISTS idx_invoices_customer_id 
ON invoices(customer_id) 
WHERE isdeleted = false;

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Test the view
SELECT 
    age_bucket,
    COUNT(*) as invoice_count,
    SUM(balance_due) as total_balance,
    AVG(days_overdue) as avg_days_overdue
FROM invoice_aging
WHERE org_id = (SELECT org_id FROM orgs LIMIT 1)
GROUP BY age_bucket
ORDER BY 
    CASE age_bucket
        WHEN 'Current' THEN 1
        WHEN '1-30 days' THEN 2
        WHEN '31-60 days' THEN 3
        WHEN '61-90 days' THEN 4
        WHEN '90+ days' THEN 5
        WHEN 'Paid' THEN 6
    END;

-- Test severely overdue invoices
SELECT 
    invoice_number,
    customer_name,
    balance_due,
    days_overdue,
    age_bucket
FROM invoice_aging
WHERE is_severely_overdue = true
ORDER BY days_overdue DESC
LIMIT 10;

-- Summary by customer
SELECT 
    customer_name,
    COUNT(*) as invoice_count,
    SUM(balance_due) as total_balance,
    MAX(days_overdue) as max_days_overdue
FROM invoice_aging
WHERE balance_due > 0
  AND status != 'paid'
GROUP BY customer_id, customer_name
ORDER BY total_balance DESC
LIMIT 10;

-- ============================================================================
-- GRANT PERMISSIONS
-- ============================================================================

-- Grant select on view to application role
GRANT SELECT ON invoice_aging TO taf;

-- Grant execute on function
GRANT EXECUTE ON FUNCTION get_age_bucket(integer) TO taf;

-- ============================================================================
-- END OF PHASE 7 TASK 1 - AGING REPORTS SCHEMA
-- ============================================================================
