-- ============================================================================
-- Fix: Update credit_utilization function with correct column names
-- ============================================================================

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 with correct column names
    UPDATE credit_limits
    SET utilized_credit = v_total_utilization,
        available_credit = v_available_credit,
        is_on_hold = v_should_hold,
        last_utilization_update = CURRENT_TIMESTAMP,  -- Corrected column name
        updated_by = p_updated_by,
        updated_at = CURRENT_TIMESTAMP  -- Corrected column name
    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';

DO $$
BEGIN
    RAISE NOTICE 'update_credit_utilization function fixed successfully!';
END$$;
