-- ============================================================================
-- Fix: Update credit_summary view with correct column names
-- ============================================================================

-- Drop and recreate the old update_credit_utilization function with correct signature
DROP FUNCTION IF EXISTS update_credit_utilization(UUID, UUID, UUID);

-- Drop and recreate credit_summary view with correct column names
DROP VIEW IF EXISTS credit_summary CASCADE;

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_utilization_update,  -- Corrected column name
    -- 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.updated_at  -- Corrected column name
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';

-- ============================================================================
-- Success message
-- ============================================================================
DO $$
BEGIN
    RAISE NOTICE 'credit_summary view fixed successfully!';
END$$;
