-- ============================================================================
-- PHASE 7 TASK 5: FINANCIAL REPORTS SCHEMA
-- ============================================================================
-- Description: Database views and functions for comprehensive financial reporting
-- Created: 2025-10-18
-- Dependencies: invoices, customers, products, invoice_items, payments tables
-- ============================================================================

-- ============================================================================
-- VIEW 1: REVENUE BY CUSTOMER
-- ============================================================================
-- Purpose: Aggregate revenue metrics per customer with trends
-- Usage: Customer revenue analysis, top customers report

CREATE OR REPLACE VIEW revenue_by_customer AS
SELECT 
    c.customer_id,
    c.org_id,
    c.customer_name,
    c.email,
    c.phone,
    COUNT(DISTINCT i.invoice_id) as total_invoices,
    COUNT(DISTINCT CASE WHEN i.status = 'paid' THEN i.invoice_id END) as paid_invoices,
    COALESCE(SUM(CASE WHEN i.status = 'paid' THEN i.total_amount END), 0) as total_revenue,
    COALESCE(AVG(CASE WHEN i.status = 'paid' THEN i.total_amount END), 0) as avg_invoice_amount,
    COALESCE(SUM(CASE WHEN i.status = 'paid' AND i.invoice_date >= CURRENT_DATE - INTERVAL '30 days' THEN i.total_amount END), 0) as revenue_last_30_days,
    COALESCE(SUM(CASE WHEN i.status = 'paid' AND i.invoice_date >= CURRENT_DATE - INTERVAL '90 days' THEN i.total_amount END), 0) as revenue_last_90_days,
    COALESCE(SUM(CASE WHEN i.status = 'paid' AND EXTRACT(YEAR FROM i.invoice_date) = EXTRACT(YEAR FROM CURRENT_DATE) THEN i.total_amount END), 0) as revenue_ytd,
    MIN(i.invoice_date) as first_invoice_date,
    MAX(i.invoice_date) as last_invoice_date,
    CASE 
        WHEN MAX(i.invoice_date) >= CURRENT_DATE - INTERVAL '30 days' THEN 'active'
        WHEN MAX(i.invoice_date) >= CURRENT_DATE - INTERVAL '90 days' THEN 'recent'
        WHEN MAX(i.invoice_date) >= CURRENT_DATE - INTERVAL '180 days' THEN 'inactive'
        ELSE 'dormant'
    END as customer_status,
    c.created_at as customer_since
FROM customers c
LEFT JOIN invoices i ON c.customer_id = i.customer_id AND i.isdeleted = false
WHERE c.isdeleted = false
GROUP BY c.customer_id, c.org_id, c.customer_name, c.email, c.phone, c.created_at;

COMMENT ON VIEW revenue_by_customer IS 'Customer revenue analysis with trends and status';

-- ============================================================================
-- VIEW 2: REVENUE BY PRODUCT/SERVICE
-- ============================================================================
-- Purpose: Aggregate revenue and sales metrics per product/service
-- Usage: Product performance analysis, best-sellers report

CREATE OR REPLACE VIEW revenue_by_product AS
SELECT 
    p.product_id,
    p.org_id,
    p.product_name,
    p.sku,
    p.category,
    p.unit_price,
    COUNT(DISTINCT ii.invoice_id) as times_sold,
    COALESCE(SUM(ii.quantity), 0) as total_quantity_sold,
    COALESCE(SUM(ii.subtotal), 0) as total_revenue,
    COALESCE(AVG(ii.unit_price), 0) as avg_selling_price,
    COALESCE(SUM(ii.tax_amount), 0) as total_tax_collected,
    COALESCE(SUM(CASE WHEN i.invoice_date >= CURRENT_DATE - INTERVAL '30 days' THEN ii.subtotal END), 0) as revenue_last_30_days,
    COALESCE(SUM(CASE WHEN i.invoice_date >= CURRENT_DATE - INTERVAL '90 days' THEN ii.subtotal END), 0) as revenue_last_90_days,
    COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM i.invoice_date) = EXTRACT(YEAR FROM CURRENT_DATE) THEN ii.subtotal END), 0) as revenue_ytd,
    MIN(i.invoice_date) as first_sale_date,
    MAX(i.invoice_date) as last_sale_date,
    COUNT(DISTINCT i.customer_id) as unique_customers,
    p.is_active,
    p.created_at
FROM products p
LEFT JOIN invoice_items ii ON p.product_id = ii.product_id AND ii.isdeleted = false
LEFT JOIN invoices i ON ii.invoice_id = i.invoice_id AND i.status = 'paid' AND i.isdeleted = false
WHERE p.isdeleted = false
GROUP BY p.product_id, p.org_id, p.product_name, p.sku, p.category, p.unit_price, p.is_active, p.created_at;

COMMENT ON VIEW revenue_by_product IS 'Product/service revenue analysis with sales metrics';

-- ============================================================================
-- VIEW 3: SALES TAX SUMMARY
-- ============================================================================
-- Purpose: Aggregate tax collected by rate and jurisdiction
-- Usage: Tax remittance reporting, compliance

CREATE OR REPLACE VIEW sales_tax_summary AS
SELECT 
    i.org_id,
    COALESCE(i.tax_rate, 0) as tax_rate,
    COUNT(DISTINCT i.invoice_id) as invoice_count,
    COALESCE(SUM(i.subtotal_amount), 0) as taxable_amount,
    COALESCE(SUM(i.tax_amount), 0) as tax_collected,
    COALESCE(SUM(i.total_amount), 0) as total_with_tax,
    COALESCE(SUM(CASE WHEN i.status = 'paid' THEN i.tax_amount END), 0) as tax_collected_paid,
    COALESCE(SUM(CASE WHEN i.status IN ('draft', 'finalized') THEN i.tax_amount END), 0) as tax_pending,
    COALESCE(SUM(CASE WHEN i.invoice_date >= CURRENT_DATE - INTERVAL '30 days' THEN i.tax_amount END), 0) as tax_last_30_days,
    COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM i.invoice_date) = EXTRACT(MONTH FROM CURRENT_DATE) 
                      AND EXTRACT(YEAR FROM i.invoice_date) = EXTRACT(YEAR FROM CURRENT_DATE) 
                      THEN i.tax_amount END), 0) as tax_current_month,
    COALESCE(SUM(CASE WHEN EXTRACT(QUARTER FROM i.invoice_date) = EXTRACT(QUARTER FROM CURRENT_DATE) 
                      AND EXTRACT(YEAR FROM i.invoice_date) = EXTRACT(YEAR FROM CURRENT_DATE) 
                      THEN i.tax_amount END), 0) as tax_current_quarter,
    COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM i.invoice_date) = EXTRACT(YEAR FROM CURRENT_DATE) 
                      THEN i.tax_amount END), 0) as tax_ytd,
    MIN(i.invoice_date) as earliest_invoice,
    MAX(i.invoice_date) as latest_invoice
FROM invoices i
WHERE i.isdeleted = false
GROUP BY i.org_id, i.tax_rate;

COMMENT ON VIEW sales_tax_summary IS 'Sales tax collection summary by rate and period';

-- ============================================================================
-- VIEW 4: PAYMENT COLLECTION METRICS
-- ============================================================================
-- Purpose: Payment collection analysis with method breakdown
-- Usage: Cash flow analysis, payment method performance

CREATE OR REPLACE VIEW payment_collection_metrics AS
SELECT 
    p.org_id,
    p.payment_method,
    COUNT(DISTINCT p.payment_id) as payment_count,
    COUNT(DISTINCT p.invoice_id) as invoices_paid,
    COALESCE(SUM(p.amount), 0) as total_collected,
    COALESCE(AVG(p.amount), 0) as avg_payment_amount,
    COALESCE(SUM(CASE WHEN p.payment_date >= CURRENT_DATE - INTERVAL '30 days' THEN p.amount END), 0) as collected_last_30_days,
    COALESCE(SUM(CASE WHEN p.payment_date >= CURRENT_DATE - INTERVAL '90 days' THEN p.amount END), 0) as collected_last_90_days,
    COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM p.payment_date) = EXTRACT(MONTH FROM CURRENT_DATE) 
                      AND EXTRACT(YEAR FROM p.payment_date) = EXTRACT(YEAR FROM CURRENT_DATE) 
                      THEN p.amount END), 0) as collected_current_month,
    COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM p.payment_date) = EXTRACT(YEAR FROM CURRENT_DATE) 
                      THEN p.amount END), 0) as collected_ytd,
    MIN(p.payment_date) as first_payment_date,
    MAX(p.payment_date) as last_payment_date
FROM payments p
WHERE p.isdeleted = false
GROUP BY p.org_id, p.payment_method;

COMMENT ON VIEW payment_collection_metrics IS 'Payment collection analysis by method and period';

-- ============================================================================
-- VIEW 5: OUTSTANDING INVOICES SUMMARY
-- ============================================================================
-- Purpose: Summary of unpaid invoices by status and aging
-- Usage: Accounts receivable reporting, collections management

CREATE OR REPLACE VIEW outstanding_invoices_summary AS
SELECT 
    i.org_id,
    i.status,
    COUNT(DISTINCT i.invoice_id) as invoice_count,
    COUNT(DISTINCT i.customer_id) as unique_customers,
    COALESCE(SUM(i.total_amount), 0) as total_amount,
    COALESCE(SUM(i.amount_paid), 0) as amount_paid,
    COALESCE(SUM(i.total_amount - COALESCE(i.amount_paid, 0)), 0) as amount_outstanding,
    COALESCE(AVG(i.total_amount - COALESCE(i.amount_paid, 0)), 0) as avg_outstanding,
    -- Aging buckets
    COALESCE(SUM(CASE 
        WHEN i.due_date >= CURRENT_DATE THEN i.total_amount - COALESCE(i.amount_paid, 0)
        ELSE 0 
    END), 0) as current_amount,
    COALESCE(SUM(CASE 
        WHEN i.due_date < CURRENT_DATE 
        AND i.due_date >= CURRENT_DATE - INTERVAL '30 days' 
        THEN i.total_amount - COALESCE(i.amount_paid, 0)
        ELSE 0 
    END), 0) as overdue_0_30,
    COALESCE(SUM(CASE 
        WHEN i.due_date < CURRENT_DATE - INTERVAL '30 days'
        AND i.due_date >= CURRENT_DATE - INTERVAL '60 days'
        THEN i.total_amount - COALESCE(i.amount_paid, 0)
        ELSE 0 
    END), 0) as overdue_30_60,
    COALESCE(SUM(CASE 
        WHEN i.due_date < CURRENT_DATE - INTERVAL '60 days'
        AND i.due_date >= CURRENT_DATE - INTERVAL '90 days'
        THEN i.total_amount - COALESCE(i.amount_paid, 0)
        ELSE 0 
    END), 0) as overdue_60_90,
    COALESCE(SUM(CASE 
        WHEN i.due_date < CURRENT_DATE - INTERVAL '90 days'
        THEN i.total_amount - COALESCE(i.amount_paid, 0)
        ELSE 0 
    END), 0) as overdue_90_plus,
    MIN(i.invoice_date) as earliest_invoice,
    MAX(i.invoice_date) as latest_invoice,
    MIN(i.due_date) as earliest_due_date,
    MAX(i.due_date) as latest_due_date
FROM invoices i
WHERE i.isdeleted = false 
  AND i.status IN ('draft', 'finalized', 'overdue', 'partial')
  AND (i.total_amount - COALESCE(i.amount_paid, 0)) > 0
GROUP BY i.org_id, i.status;

COMMENT ON VIEW outstanding_invoices_summary IS 'Outstanding invoices summary with aging buckets';

-- ============================================================================
-- VIEW 6: REVENUE TRENDS (Monthly)
-- ============================================================================
-- Purpose: Monthly revenue trends for visualization
-- Usage: Revenue trend charts, forecasting

CREATE OR REPLACE VIEW revenue_trends_monthly AS
SELECT 
    i.org_id,
    DATE_TRUNC('month', i.invoice_date) as month,
    EXTRACT(YEAR FROM i.invoice_date) as year,
    EXTRACT(MONTH FROM i.invoice_date) as month_num,
    COUNT(DISTINCT i.invoice_id) as invoice_count,
    COUNT(DISTINCT i.customer_id) as unique_customers,
    COALESCE(SUM(i.subtotal_amount), 0) as subtotal,
    COALESCE(SUM(i.tax_amount), 0) as tax,
    COALESCE(SUM(i.total_amount), 0) as total_revenue,
    COALESCE(SUM(CASE WHEN i.status = 'paid' THEN i.total_amount END), 0) as paid_revenue,
    COALESCE(SUM(CASE WHEN i.status IN ('draft', 'finalized') THEN i.total_amount END), 0) as pending_revenue,
    COALESCE(AVG(i.total_amount), 0) as avg_invoice_amount
FROM invoices i
WHERE i.isdeleted = false
  AND i.invoice_date >= CURRENT_DATE - INTERVAL '24 months'
GROUP BY i.org_id, DATE_TRUNC('month', i.invoice_date), EXTRACT(YEAR FROM i.invoice_date), EXTRACT(MONTH FROM i.invoice_date)
ORDER BY month DESC;

COMMENT ON VIEW revenue_trends_monthly IS 'Monthly revenue trends for last 24 months';

-- ============================================================================
-- VIEW 7: COLLECTION EFFICIENCY METRICS
-- ============================================================================
-- Purpose: Calculate collection efficiency and DSO (Days Sales Outstanding)
-- Usage: Performance metrics, KPI dashboards

CREATE OR REPLACE VIEW collection_efficiency_metrics AS
SELECT 
    i.org_id,
    COUNT(DISTINCT i.invoice_id) as total_invoices,
    COUNT(DISTINCT CASE WHEN i.status = 'paid' THEN i.invoice_id END) as paid_invoices,
    COALESCE(SUM(i.total_amount), 0) as total_billed,
    COALESCE(SUM(CASE WHEN i.status = 'paid' THEN i.total_amount END), 0) as total_collected,
    COALESCE(SUM(i.total_amount - COALESCE(i.amount_paid, 0)), 0) as total_outstanding,
    -- Collection rate
    CASE 
        WHEN SUM(i.total_amount) > 0 
        THEN (SUM(CASE WHEN i.status = 'paid' THEN i.total_amount END) / SUM(i.total_amount) * 100)
        ELSE 0 
    END as collection_rate_percent,
    -- Average days to collect
    COALESCE(AVG(CASE 
        WHEN i.status = 'paid' AND p.payment_date IS NOT NULL 
        THEN EXTRACT(DAY FROM (p.payment_date - i.invoice_date))
    END), 0) as avg_days_to_collect,
    -- DSO (Days Sales Outstanding)
    CASE 
        WHEN SUM(CASE WHEN i.invoice_date >= CURRENT_DATE - INTERVAL '90 days' THEN i.total_amount END) > 0
        THEN (
            SUM(i.total_amount - COALESCE(i.amount_paid, 0)) / 
            (SUM(CASE WHEN i.invoice_date >= CURRENT_DATE - INTERVAL '90 days' THEN i.total_amount END) / 90)
        )
        ELSE 0
    END as days_sales_outstanding
FROM invoices i
LEFT JOIN payments p ON i.invoice_id = p.invoice_id AND p.isdeleted = false
WHERE i.isdeleted = false
GROUP BY i.org_id;

COMMENT ON VIEW collection_efficiency_metrics IS 'Collection efficiency and DSO calculations';

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

-- Index on invoice_date for date range queries
CREATE INDEX IF NOT EXISTS idx_invoices_invoice_date 
ON invoices(invoice_date) WHERE isdeleted = false;

-- Index on payment_date for collection reports
CREATE INDEX IF NOT EXISTS idx_payments_payment_date 
ON payments(payment_date) WHERE isdeleted = false;

-- Index on payment_method for payment analysis
CREATE INDEX IF NOT EXISTS idx_payments_payment_method 
ON payments(org_id, payment_method) WHERE isdeleted = false;

-- Index on product category for category analysis
CREATE INDEX IF NOT EXISTS idx_products_category 
ON products(org_id, category) WHERE isdeleted = false;

-- Index on customer for revenue analysis
CREATE INDEX IF NOT EXISTS idx_invoices_customer_status 
ON invoices(customer_id, status) WHERE isdeleted = false;

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

-- Test revenue_by_customer view
SELECT * FROM revenue_by_customer LIMIT 5;

-- Test revenue_by_product view
SELECT * FROM revenue_by_product ORDER BY total_revenue DESC LIMIT 10;

-- Test sales_tax_summary view
SELECT * FROM sales_tax_summary;

-- Test payment_collection_metrics view
SELECT * FROM payment_collection_metrics;

-- Test outstanding_invoices_summary view
SELECT * FROM outstanding_invoices_summary;

-- Test revenue_trends_monthly view
SELECT * FROM revenue_trends_monthly ORDER BY month DESC LIMIT 12;

-- Test collection_efficiency_metrics view
SELECT * FROM collection_efficiency_metrics;

-- ============================================================================
-- ROLLBACK (if needed)
-- ============================================================================
/*
DROP VIEW IF EXISTS revenue_by_customer CASCADE;
DROP VIEW IF EXISTS revenue_by_product CASCADE;
DROP VIEW IF EXISTS sales_tax_summary CASCADE;
DROP VIEW IF EXISTS payment_collection_metrics CASCADE;
DROP VIEW IF EXISTS outstanding_invoices_summary CASCADE;
DROP VIEW IF EXISTS revenue_trends_monthly CASCADE;
DROP VIEW IF EXISTS collection_efficiency_metrics CASCADE;

DROP INDEX IF EXISTS idx_invoices_invoice_date;
DROP INDEX IF EXISTS idx_payments_payment_date;
DROP INDEX IF EXISTS idx_payments_payment_method;
DROP INDEX IF EXISTS idx_products_category;
DROP INDEX IF EXISTS idx_invoices_customer_status;
*/
