-- ============================================================================
-- Migration: Unified Billable Parties View & Credit Management Fix
-- ============================================================================
-- Purpose: 
-- 1. Create unified view combining customers and clients
-- 2. Refactor credit_limits to work with both entity types
-- 3. Fix triggers to handle invoices without direct customer_id
-- ============================================================================

-- ============================================================================
-- STEP 1: Create billable_parties unified view
-- ============================================================================

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

-- Create unified view of customers and clients
CREATE OR REPLACE VIEW billable_parties AS
-- Customers (retail/POS)
SELECT 
    customer_id as party_id,
    'customer' as party_type,
    org_id,
    name,
    email,
    phone_number as phone,
    address,
    tin,
    balance,
    risk_rating,
    compliance_status,
    user_id,
    isdeleted,
    updatedat as updated_at
FROM customers
WHERE isdeleted = false

UNION ALL

-- Clients (case management)
SELECT 
    client_id as party_id,
    'client' as party_type,
    org_id,
    name,
    email,
    phone,
    address,
    NULL as tin,  -- clients don't have TIN
    0.00 as balance,  -- clients don't track balance in this table
    NULL as risk_rating,
    NULL as compliance_status,
    user_id,
    isdeleted,
    updatedat as updated_at
FROM clients
WHERE isdeleted = false;

COMMENT ON VIEW billable_parties IS 'Unified view of customers and clients for billing and credit management purposes';

-- ============================================================================
-- STEP 2: Refactor customer_credit_limits to credit_limits
-- ============================================================================

-- Check if the table exists and needs migration
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'customer_credit_limits') THEN
        -- Rename the table
        ALTER TABLE customer_credit_limits RENAME TO credit_limits;
        
        -- Drop old constraints if they exist
        ALTER TABLE credit_limits DROP CONSTRAINT IF EXISTS fk_customer_credit_limits_customer;
        ALTER TABLE credit_limits DROP CONSTRAINT IF EXISTS fk_customer_credit_limits_org;
        ALTER TABLE credit_limits DROP CONSTRAINT IF EXISTS unique_customer_org_credit_limit;
        
        -- Rename customer_id to party_id
        ALTER TABLE credit_limits RENAME COLUMN customer_id TO party_id;
        
        -- Add party_type column if it doesn't exist
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                      WHERE table_name = 'credit_limits' AND column_name = 'party_type') THEN
            ALTER TABLE credit_limits 
                ADD COLUMN party_type VARCHAR(20) DEFAULT 'customer' CHECK (party_type IN ('customer', 'client'));
        END IF;
        
        -- Update existing records to have party_type = 'customer'
        UPDATE credit_limits SET party_type = 'customer' WHERE party_type IS NULL;
        
        -- Make party_type NOT NULL
        ALTER TABLE credit_limits ALTER COLUMN party_type SET NOT NULL;
        
        -- Add new unique constraint
        ALTER TABLE credit_limits
            ADD CONSTRAINT unique_party_org_credit_limit 
            UNIQUE (party_id, party_type, org_id);
        
        -- Update indexes
        DROP INDEX IF EXISTS idx_customer_credit_limits_customer;
        DROP INDEX IF EXISTS idx_customer_credit_limits_org;
        DROP INDEX IF EXISTS idx_customer_credit_limits_on_hold;
        DROP INDEX IF EXISTS idx_customer_credit_limits_utilization;
        
        CREATE INDEX idx_credit_limits_party ON credit_limits(party_id, party_type) WHERE isdeleted = false;
        CREATE INDEX idx_credit_limits_org ON credit_limits(org_id) WHERE isdeleted = false;
        CREATE INDEX idx_credit_limits_on_hold ON credit_limits(is_on_hold) WHERE isdeleted = false AND is_on_hold = true;
        CREATE INDEX idx_credit_limits_utilization ON credit_limits(utilized_credit, credit_limit) WHERE isdeleted = false;
        
        -- Update comments
        COMMENT ON TABLE credit_limits IS 'Credit limits for all billable parties (customers and clients)';
        COMMENT ON COLUMN credit_limits.party_id IS 'References either customer_id or client_id depending on party_type';
        COMMENT ON COLUMN credit_limits.party_type IS 'Type: customer (retail/POS) or client (case management)';
        
        RAISE NOTICE 'Successfully migrated customer_credit_limits to credit_limits';
    ELSE
        RAISE NOTICE 'Table customer_credit_limits does not exist, skipping migration';
    END IF;
END$$;

-- ============================================================================
-- STEP 3: Create helper function to resolve party from invoice
-- ============================================================================

CREATE OR REPLACE FUNCTION get_invoice_party(
    p_invoice_id uuid,
    OUT party_id uuid,
    OUT party_type varchar(20)
) AS $$
BEGIN
    -- First check if invoice has direct customer_id
    SELECT i.customer_id, 'customer'
    INTO party_id, party_type
    FROM invoices i
    WHERE i.invoice_id = p_invoice_id 
      AND i.customer_id IS NOT NULL
      AND i.isdeleted = false;
    
    IF FOUND THEN
        RETURN;
    END IF;
    
    -- Check if invoice references a quotation with customer_id
    SELECT q.customer_id, 'customer'
    INTO party_id, party_type
    FROM invoices i
    JOIN quotations q ON i.reference_table = 'quotations' AND i.reference_id = q.quotation_id
    WHERE i.invoice_id = p_invoice_id
      AND q.customer_id IS NOT NULL
      AND i.isdeleted = false
      AND q.isdeleted = false;
    
    IF FOUND THEN
        RETURN;
    END IF;
    
    -- Check if invoice references a case (which has a client)
    SELECT c.client_id, 'client'
    INTO party_id, party_type
    FROM invoices i
    JOIN cases cs ON i.reference_table = 'cases' AND i.reference_id = cs.case_id
    JOIN clients c ON cs.client_id = c.client_id
    WHERE i.invoice_id = p_invoice_id
      AND i.isdeleted = false
      AND cs.isdeleted = false
      AND c.isdeleted = false;
    
    IF FOUND THEN
        RETURN;
    END IF;
    
    -- Check if invoice directly references a client
    SELECT i.reference_id, 'client'
    INTO party_id, party_type
    FROM invoices i
    WHERE i.invoice_id = p_invoice_id
      AND i.reference_table = 'clients'
      AND i.isdeleted = false;
    
    IF FOUND THEN
        RETURN;
    END IF;
    
    -- If nothing found, return NULL
    party_id := NULL;
    party_type := NULL;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_invoice_party IS 'Resolves the billable party (customer or client) for an invoice by checking customer_id, quotations, cases, or direct references';

-- ============================================================================
-- STEP 4: Update credit utilization function to use party concept
-- ============================================================================

CREATE OR REPLACE FUNCTION update_credit_utilization(
    p_party_id uuid,
    p_party_type varchar(20),
    p_org_id uuid,
    p_updated_by uuid DEFAULT NULL
)
RETURNS void AS $$
DECLARE
    v_credit_limit_id uuid;
    v_total_outstanding numeric(10,2);
    v_total_pending_quotations numeric(10,2);
    v_total_utilization numeric(10,2);
    v_credit_limit numeric(10,2);
    v_available_credit numeric(10,2);
    v_should_hold boolean;
BEGIN
    -- Skip if party_id is NULL
    IF p_party_id IS NULL OR p_party_type IS NULL THEN
        RETURN;
    END IF;
    
    -- Get credit limit record
    SELECT credit_limit_id, credit_limit
    INTO v_credit_limit_id, v_credit_limit
    FROM credit_limits
    WHERE party_id = p_party_id
      AND party_type = p_party_type
      AND org_id = p_org_id
      AND isdeleted = false;
    
    -- If no credit limit exists, nothing to update
    IF v_credit_limit_id IS NULL THEN
        RETURN;
    END IF;
    
    -- Calculate outstanding invoices for this party
    IF p_party_type = 'customer' THEN
        -- For customers: check invoices with direct customer_id or via quotations
        SELECT COALESCE(SUM(i.amount - COALESCE(i.amount_paid, 0)), 0)
        INTO v_total_outstanding
        FROM invoices i
        WHERE i.org_id = p_org_id
          AND i.isdeleted = false
          AND i.status NOT IN ('paid', 'cancelled', 'void')
          AND (
              i.customer_id = p_party_id
              OR i.reference_id IN (
                  SELECT quotation_id FROM quotations 
                  WHERE customer_id = p_party_id AND isdeleted = false
              )
          );
          
        -- Calculate pending quotations
        SELECT COALESCE(SUM(total_amount), 0)
        INTO v_total_pending_quotations
        FROM quotations
        WHERE customer_id = p_party_id
          AND org_id = p_org_id
          AND status IN ('sent', 'draft')
          AND isdeleted = false;
          
    ELSIF p_party_type = 'client' THEN
        -- For clients: check invoices via cases or direct client reference
        SELECT COALESCE(SUM(i.amount - COALESCE(i.amount_paid, 0)), 0)
        INTO v_total_outstanding
        FROM invoices i
        WHERE i.org_id = p_org_id
          AND i.isdeleted = false
          AND i.status NOT IN ('paid', 'cancelled', 'void')
          AND (
              (i.reference_table = 'clients' AND i.reference_id = p_party_id)
              OR i.reference_id IN (
                  SELECT case_id FROM cases 
                  WHERE client_id = p_party_id AND isdeleted = false
              )
          );
          
        -- Clients typically don't have pending quotations, but check anyway
        SELECT COALESCE(SUM(total_amount), 0)
        INTO v_total_pending_quotations
        FROM quotations
        WHERE client_id = p_party_id
          AND org_id = p_org_id
          AND status IN ('sent', 'draft')
          AND isdeleted = false;
    END IF;
    
    -- Calculate total utilization
    v_total_utilization := COALESCE(v_total_outstanding, 0) + COALESCE(v_total_pending_quotations, 0);
    v_available_credit := v_credit_limit - v_total_utilization;
    
    -- Determine if hold should be applied
    v_should_hold := (v_total_utilization > v_credit_limit);
    
    -- Update credit limit record
    UPDATE credit_limits
    SET utilized_credit = v_total_utilization,
        available_credit = v_available_credit,
        is_on_hold = v_should_hold,
        last_calculated_at = CURRENT_TIMESTAMP,
        updated_by = p_updated_by,
        updatedat = CURRENT_TIMESTAMP
    WHERE credit_limit_id = v_credit_limit_id;
    
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION update_credit_utilization IS 'Update credit utilization for a billable party (customer or client) based on outstanding invoices and pending quotations';

-- ============================================================================
-- STEP 5: Update invoice trigger to use party resolution
-- ============================================================================

DROP TRIGGER IF EXISTS trg_update_credit_on_invoice_change ON invoices;
DROP FUNCTION IF EXISTS trigger_update_credit_on_invoice_change();

CREATE OR REPLACE FUNCTION trigger_update_credit_on_invoice_change()
RETURNS TRIGGER AS $$
DECLARE
    v_party_id uuid;
    v_party_type varchar(20);
BEGIN
    -- For INSERT or UPDATE, resolve party from NEW record
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        -- Resolve the party for this invoice
        SELECT party_id, party_type 
        INTO v_party_id, v_party_type
        FROM get_invoice_party(NEW.invoice_id);
        
        -- Update credit utilization if party was found
        IF v_party_id IS NOT NULL THEN
            PERFORM update_credit_utilization(v_party_id, v_party_type, NEW.org_id, NEW.updated_by);
        END IF;
    END IF;
    
    -- For DELETE or UPDATE of key fields, also update old party
    IF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND (
        OLD.customer_id IS DISTINCT FROM NEW.customer_id OR
        OLD.reference_id IS DISTINCT FROM NEW.reference_id OR
        OLD.reference_table IS DISTINCT FROM NEW.reference_table
    )) THEN
        -- Resolve the party for OLD record
        SELECT party_id, party_type 
        INTO v_party_id, v_party_type
        FROM get_invoice_party(OLD.invoice_id);
        
        -- Update credit utilization if party was found
        IF v_party_id IS NOT NULL THEN
            PERFORM update_credit_utilization(v_party_id, v_party_type, OLD.org_id, OLD.updated_by);
        END IF;
    END IF;
    
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_credit_on_invoice_change
AFTER INSERT OR UPDATE OR DELETE ON invoices
FOR EACH ROW
EXECUTE FUNCTION trigger_update_credit_on_invoice_change();

COMMENT ON FUNCTION trigger_update_credit_on_invoice_change IS 'Trigger to update credit utilization when invoices change, resolving party from customer_id, quotations, or cases';

-- ============================================================================
-- STEP 6: Update quotation trigger to use party concept
-- ============================================================================

DROP TRIGGER IF EXISTS trg_update_credit_on_quotation_change ON quotations;
DROP FUNCTION IF EXISTS trigger_update_credit_on_quotation_change();

CREATE OR REPLACE FUNCTION trigger_update_credit_on_quotation_change()
RETURNS TRIGGER AS $$
DECLARE
    v_party_type varchar(20);
BEGIN
    -- Determine party type based on which ID is populated
    IF NEW.customer_id IS NOT NULL THEN
        v_party_type := 'customer';
    ELSIF NEW.client_id IS NOT NULL THEN
        v_party_type := 'client';
    ELSE
        -- No party identified, skip
        RETURN NEW;
    END IF;
    
    -- For INSERT or UPDATE
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        IF NEW.customer_id IS NOT NULL THEN
            PERFORM update_credit_utilization(NEW.customer_id, 'customer', NEW.org_id, NEW.updated_by);
        ELSIF NEW.client_id IS NOT NULL THEN
            PERFORM update_credit_utilization(NEW.client_id, 'client', NEW.org_id, NEW.updated_by);
        END IF;
    END IF;
    
    -- For DELETE or UPDATE of customer/client
    IF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND (
        OLD.customer_id IS DISTINCT FROM NEW.customer_id OR
        OLD.client_id IS DISTINCT FROM NEW.client_id
    )) THEN
        IF OLD.customer_id IS NOT NULL THEN
            PERFORM update_credit_utilization(OLD.customer_id, 'customer', OLD.org_id, OLD.updated_by);
        ELSIF OLD.client_id IS NOT NULL THEN
            PERFORM update_credit_utilization(OLD.client_id, 'client', OLD.org_id, OLD.updated_by);
        END IF;
    END IF;
    
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_credit_on_quotation_change
AFTER INSERT OR UPDATE OR DELETE ON quotations
FOR EACH ROW
EXECUTE FUNCTION trigger_update_credit_on_quotation_change();

COMMENT ON FUNCTION trigger_update_credit_on_quotation_change IS 'Trigger to update credit utilization when quotations change for customers or clients';

-- ============================================================================
-- STEP 7: Create updated invoice_aging view using billable_parties
-- ============================================================================

DROP VIEW IF EXISTS invoice_aging CASCADE;

CREATE OR REPLACE VIEW invoice_aging AS
SELECT 
    -- Invoice details
    i.invoice_id,
    i.invoice_number,
    i.org_id,
    
    -- Party information (unified customer/client)
    bp.party_id,
    bp.party_type,
    bp.name as party_name,
    bp.email as party_email,
    bp.phone as party_phone,
    bp.tin as party_tin,
    
    -- Invoice dates and amounts
    i.invoice_date,
    (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 IN ('paid', 'cancelled', 'void') 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 IN ('paid', 'cancelled', 'void') THEN i.status
        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,
    
    -- Reference information
    i.reference_table,
    i.reference_id,
    
    -- Metadata
    i.sent_at,
    i.taxcore_submitted_at,
    i.updatedat as last_updated
    
FROM invoices i
CROSS JOIN LATERAL get_invoice_party(i.invoice_id) AS party_info
LEFT JOIN billable_parties bp ON bp.party_id = party_info.party_id AND bp.party_type = party_info.party_type
WHERE i.isdeleted = false
  AND i.amount > 0;

COMMENT ON VIEW invoice_aging IS 'Invoice aging report with unified customer/client party information';

-- ============================================================================
-- STEP 8: Create credit_summary view for easy reporting
-- ============================================================================

CREATE OR REPLACE VIEW credit_summary AS
SELECT 
    cl.credit_limit_id,
    cl.party_id,
    cl.party_type,
    bp.name as party_name,
    bp.email as party_email,
    bp.phone as party_phone,
    cl.org_id,
    cl.credit_limit,
    cl.utilized_credit,
    cl.available_credit,
    cl.payment_terms_days,
    cl.is_on_hold,
    cl.hold_reason,
    cl.last_calculated_at,
    -- Calculate utilization percentage
    CASE 
        WHEN cl.credit_limit > 0 THEN 
            ROUND((cl.utilized_credit / cl.credit_limit * 100), 2)
        ELSE 0 
    END as utilization_percentage,
    -- Status indicator
    CASE 
        WHEN cl.is_on_hold THEN 'on_hold'
        WHEN cl.utilized_credit >= cl.credit_limit THEN 'at_limit'
        WHEN cl.utilized_credit >= (cl.credit_limit * 0.9) THEN 'warning'
        ELSE 'good'
    END as status,
    cl.updated_by,
    cl.updatedat
FROM credit_limits cl
JOIN billable_parties bp ON bp.party_id = cl.party_id AND bp.party_type = cl.party_type
WHERE cl.isdeleted = false;

COMMENT ON VIEW credit_summary IS 'Credit summary with party details and utilization metrics';

-- ============================================================================
-- Migration Complete
-- ============================================================================

-- Summary message
DO $$
BEGIN
    RAISE NOTICE '============================================================================';
    RAISE NOTICE 'Migration completed successfully!';
    RAISE NOTICE '============================================================================';
    RAISE NOTICE 'Created:';
    RAISE NOTICE '  - billable_parties view (customers + clients)';
    RAISE NOTICE '  - get_invoice_party() function';
    RAISE NOTICE '  - Updated update_credit_utilization() function';
    RAISE NOTICE '  - Updated invoice and quotation triggers';
    RAISE NOTICE '  - invoice_aging view';
    RAISE NOTICE '  - credit_summary view';
    RAISE NOTICE '';
    RAISE NOTICE 'Migrated:';
    RAISE NOTICE '  - customer_credit_limits -> credit_limits (with party_type)';
    RAISE NOTICE '============================================================================';
END$$;
