template-saas-database-v2/ddl/schemas/commissions/03-functions.sql

-- ============================================
-- TEMPLATE-SAAS: Commissions Functions
-- Version: 1.0.0
-- Module: SAAS-020
-- ============================================

-- ============================================
-- Function: Calculate tiered commission rate
-- Returns the applicable rate for a given amount based on tiers
-- ============================================
CREATE OR REPLACE FUNCTION commissions.apply_tiered_rate(
    p_tiers JSONB,
    p_amount DECIMAL(15, 2)
)
RETURNS DECIMAL(5, 2) AS $$
DECLARE
    v_tier JSONB;
    v_rate DECIMAL(5, 2) := 0;
BEGIN
    -- Tiers format: [{"from": 0, "to": 1000, "rate": 5}, {"from": 1000, "to": 5000, "rate": 7.5}, ...]
    -- Find the tier that matches the amount
    FOR v_tier IN SELECT * FROM jsonb_array_elements(p_tiers)
    LOOP
        IF p_amount >= (v_tier->>'from')::DECIMAL
           AND (v_tier->>'to' IS NULL OR p_amount < (v_tier->>'to')::DECIMAL) THEN
            v_rate := (v_tier->>'rate')::DECIMAL;
            EXIT;
        END IF;
    END LOOP;

    RETURN v_rate;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION commissions.apply_tiered_rate IS 'Calculate commission rate from tiered configuration';

-- ============================================
-- Function: Calculate commission for a transaction
-- Returns the commission amount based on scheme configuration
-- ============================================
CREATE OR REPLACE FUNCTION commissions.calculate_commission(
    p_scheme_id UUID,
    p_user_id UUID,
    p_amount DECIMAL(15, 2),
    p_tenant_id UUID
)
RETURNS TABLE (
    rate_applied DECIMAL(5, 2),
    commission_amount DECIMAL(15, 2)
) AS $$
DECLARE
    v_scheme RECORD;
    v_assignment RECORD;
    v_rate DECIMAL(5, 2);
    v_commission DECIMAL(15, 2);
BEGIN
    -- Get scheme configuration
    SELECT * INTO v_scheme
    FROM commissions.schemes
    WHERE id = p_scheme_id
      AND tenant_id = p_tenant_id
      AND is_active = TRUE
      AND deleted_at IS NULL;

    IF NOT FOUND THEN
        RETURN QUERY SELECT 0::DECIMAL(5,2), 0::DECIMAL(15,2);
        RETURN;
    END IF;

    -- Check minimum amount threshold
    IF p_amount < v_scheme.min_amount THEN
        RETURN QUERY SELECT 0::DECIMAL(5,2), 0::DECIMAL(15,2);
        RETURN;
    END IF;

    -- Get user's custom rate if exists
    SELECT custom_rate INTO v_assignment
    FROM commissions.assignments
    WHERE scheme_id = p_scheme_id
      AND user_id = p_user_id
      AND tenant_id = p_tenant_id
      AND is_active = TRUE
      AND starts_at <= NOW()
      AND (ends_at IS NULL OR ends_at > NOW())
    LIMIT 1;

    -- Determine rate based on scheme type
    CASE v_scheme.type
        WHEN 'percentage' THEN
            v_rate := COALESCE(v_assignment.custom_rate, v_scheme.rate);
            v_commission := p_amount * (v_rate / 100);

        WHEN 'fixed' THEN
            v_rate := 0;
            v_commission := v_scheme.fixed_amount;

        WHEN 'tiered' THEN
            v_rate := commissions.apply_tiered_rate(v_scheme.tiers, p_amount);
            IF v_assignment.custom_rate IS NOT NULL THEN
                v_rate := v_assignment.custom_rate;
            END IF;
            v_commission := p_amount * (v_rate / 100);
    END CASE;

    -- Apply maximum cap if defined
    IF v_scheme.max_amount IS NOT NULL AND v_commission > v_scheme.max_amount THEN
        v_commission := v_scheme.max_amount;
    END IF;

    RETURN QUERY SELECT v_rate, ROUND(v_commission, 2);
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION commissions.calculate_commission IS 'Calculate commission for a transaction based on scheme';

-- ============================================
-- Function: Close a commission period
-- Aggregates totals and updates status
-- ============================================
CREATE OR REPLACE FUNCTION commissions.close_period(
    p_period_id UUID,
    p_closed_by UUID
)
RETURNS BOOLEAN AS $$
DECLARE
    v_period RECORD;
    v_totals RECORD;
BEGIN
    -- Get period and verify it's open
    SELECT * INTO v_period
    FROM commissions.periods
    WHERE id = p_period_id
      AND status = 'open'
    FOR UPDATE;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    -- Calculate totals from entries
    SELECT
        COUNT(*)::INT as entry_count,
        COALESCE(SUM(commission_amount), 0) as total
    INTO v_totals
    FROM commissions.entries
    WHERE period_id = p_period_id
      AND status IN ('pending', 'approved');

    -- Update period
    UPDATE commissions.periods
    SET status = 'closed',
        closed_at = NOW(),
        closed_by = p_closed_by,
        total_entries = v_totals.entry_count,
        total_amount = v_totals.total
    WHERE id = p_period_id;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION commissions.close_period IS 'Close a commission period and calculate totals';

-- ============================================
-- Function: Get user earnings summary
-- Returns commission totals for a user
-- ============================================
CREATE OR REPLACE FUNCTION commissions.get_user_earnings(
    p_user_id UUID,
    p_tenant_id UUID,
    p_start_date TIMESTAMPTZ DEFAULT NULL,
    p_end_date TIMESTAMPTZ DEFAULT NULL
)
RETURNS TABLE (
    total_pending DECIMAL(15, 2),
    total_approved DECIMAL(15, 2),
    total_paid DECIMAL(15, 2),
    total_entries INT,
    currency VARCHAR(3)
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COALESCE(SUM(CASE WHEN e.status = 'pending' THEN e.commission_amount ELSE 0 END), 0) as total_pending,
        COALESCE(SUM(CASE WHEN e.status = 'approved' THEN e.commission_amount ELSE 0 END), 0) as total_approved,
        COALESCE(SUM(CASE WHEN e.status = 'paid' THEN e.commission_amount ELSE 0 END), 0) as total_paid,
        COUNT(*)::INT as total_entries,
        COALESCE(MAX(e.currency), 'USD') as currency
    FROM commissions.entries e
    WHERE e.user_id = p_user_id
      AND e.tenant_id = p_tenant_id
      AND e.status NOT IN ('rejected', 'cancelled')
      AND (p_start_date IS NULL OR e.created_at >= p_start_date)
      AND (p_end_date IS NULL OR e.created_at <= p_end_date);
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION commissions.get_user_earnings IS 'Get earnings summary for a user';

-- ============================================
-- Function: Auto-approve commissions after X days
-- Can be called by a scheduled job
-- ============================================
CREATE OR REPLACE FUNCTION commissions.auto_approve_pending(
    p_tenant_id UUID,
    p_days_threshold INT DEFAULT 7
)
RETURNS INT AS $$
DECLARE
    v_count INT;
BEGIN
    WITH updated AS (
        UPDATE commissions.entries
        SET status = 'approved',
            approved_at = NOW(),
            updated_at = NOW()
        WHERE tenant_id = p_tenant_id
          AND status = 'pending'
          AND created_at < NOW() - (p_days_threshold || ' days')::INTERVAL
        RETURNING id
    )
    SELECT COUNT(*) INTO v_count FROM updated;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION commissions.auto_approve_pending IS 'Auto-approve pending commissions after threshold days';