-- Change invoice_revenues uniqueness from global to per-period
-- A revenue can be on multiple invoices, just not on invoices with overlapping periods

-- Replace the trigger function to check for overlapping periods instead of global uniqueness
CREATE OR REPLACE FUNCTION check_invoice_revenue_unique()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if this revenue is already on an invoice with an overlapping period
    IF EXISTS (
        SELECT 1
        FROM invoice_revenues ir
        JOIN invoices existing ON existing.id = ir.invoice_id
        JOIN invoices new_inv ON new_inv.id = NEW.invoice_id
        WHERE ir.revenue_id = NEW.revenue_id
          AND ir.id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000'::uuid)
          AND existing.start_date <= new_inv.end_date
          AND existing.end_date >= new_inv.start_date
    ) THEN
        RAISE EXCEPTION 'Revenue % is already on an invoice with an overlapping period', NEW.revenue_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- The trigger already exists, so no need to recreate it
-- It will use the updated function automatically