-- has_trigger_events_test.sql
BEGIN;
SELECT plan(13);
-- SELECT * FROM no_plan();

-- Define a test table
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- Create a trigger on the test table
CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger function logic here
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_trigger
AFTER INSERT OR UPDATE ON test_table
FOR EACH ROW
EXECUTE FUNCTION test_trigger_function();

-- has_trigger_events test function
-- Parameters:
--   p_table_name            - name of table
--   p_trigger_name          - name of trigger function
--   p_expected_events_array - array of events 
--   p_test_description
-- Returns:
--   test_result text
CREATE OR REPLACE FUNCTION has_trigger_events(
    p_table_name text,
    p_trigger_name text,
    p_expected_events_array text[],
    p_test_description text
)
RETURNS TEXT AS $$
DECLARE
    found_events TEXT[];
    extra_events TEXT[];
    missing_events TEXT[];
    result TEXT;
BEGIN
    -- Check if the trigger exists
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.triggers
        WHERE event_object_table = p_table_name
        AND trigger_name = p_trigger_name
    ) THEN
        RETURN ok(false, 'Trigger ' || p_trigger_name || ' does not exist on table ' || p_table_name);
    END IF;

    -- Fetch trigger events based on provided parameters
    found_events := _get_trigger_events(p_table_name, p_trigger_name);

    -- RAISE NOTICE 'test event: %', ARRAY_TO_STRING(found_events, ', ');

    -- Compare expected events with found events to identify missing and extra events
    missing_events := ARRAY(
        SELECT unnest(p_expected_events_array)
        EXCEPT
        SELECT unnest(found_events)
    );

    extra_events := ARRAY(
        SELECT unnest(found_events)
        EXCEPT
        SELECT unnest(p_expected_events_array)
    );

    -- Generate error message using _are function
    result := _are(
        'events',
        extra_events,
        missing_events,
        p_test_description
    );

    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Reusable function to fetch trigger events based on provided parameters
-- Parameters:
--   p_table_name - name of table
--   p_triggger_name - name of trigger function
--   p_expected_timing - optional timing e.g. 'AFTER'
CREATE OR REPLACE FUNCTION _get_trigger_events(
    p_table_name text,
    p_trigger_name text,
    p_expected_timing text DEFAULT NULL
)
RETURNS TEXT[] AS $$
DECLARE
    trigger_timing_filter text;
BEGIN
    IF p_expected_timing IS NOT NULL THEN
        trigger_timing_filter := ' AND trigger_timing = ' || quote_literal(p_expected_timing);
    ELSE
        trigger_timing_filter := '';
    END IF;

    RETURN ARRAY(
        SELECT upper(event_manipulation)
        FROM information_schema.triggers
        WHERE event_object_table = p_table_name
        AND trigger_name = p_trigger_name
        || trigger_timing_filter
    );
END;
$$ LANGUAGE plpgsql;

-- Test Case 1: No missing or extra events (Both INSERT and UPDATE are expected).
SELECT * FROM check_test(
    has_trigger_events('test_table', 'test_trigger', ARRAY['INSERT', 'UPDATE'], 'should have no missing or extra events'),
    true,
    'has_trigger_event( tab, trigger, events, description )'
);

-- Test Case 2: Missing event (Only INSERT is expected).
-- counts as 3 tests has_trigger + description correct + diag correct
SELECT * FROM check_test(
    has_trigger_events('test_table', 'test_trigger', ARRAY['INSERT'], 'should fail with extra event UPDATE'),
    false,
    'has_trigger_events( tab, trigger, events, description )',
    'should fail with extra event UPDATE',
    E'    Extra events:\n        "UPDATE"'
);

-- Test Case 3: Extra event (Only UPDATE is expected).
SELECT * FROM check_test(
    has_trigger_events('test_table', 'test_trigger', ARRAY['UPDATE'], 'should fail with extra event INSERT'),
    false,
    'has_trigger_events( tab, trigger, events, description )',
    'should fail with extra event INSERT',
    E'    Extra events:\n        "INSERT"'
);

-- Test Case 4: Both missing and extra events (Only DELETE is expected).
SELECT * FROM check_test(
    has_trigger_events('test_table', 'test_trigger', ARRAY['DELETE'], 'should fail with both missing and extra events'), 
    false,
    'has_trigger_events( tab, trigger, events, description )',
    'should fail with both missing and extra events',
    E'    Extra events:\n        "INSERT"\n        "UPDATE"\n    Missing events:\n        "DELETE"'
);

-- Test Case 5: Trigger does not exist.
SELECT * FROM check_test(
    has_trigger_events('test_table', 'non_existent_trigger', ARRAY['INSERT'], 'should fail - trigger does not exist'),
    false,
    'has_trigger_events( tab, non_existent_trigger, events, description )'
);

-- Test Case 6: Table does not exist.
SELECT * FROM check_test(
    has_trigger_events('non_existent_table', 'test_trigger', ARRAY['INSERT'], 'should fail - table does not exist'),
    false,
    'has_trigger_events( non_existent_table, trigger, events, description )'
);

-- Test Case 7: Empty events.
SELECT * FROM check_test(
    has_trigger_events('test_table', 'test_trigger', ARRAY[]::TEXT[], 'should fail with both missing and extra events'),
    false,
    'has_trigger_events( table, trigger, ARRAY[], description )'
);

SELECT * FROM finish();
ROLLBACK;

-- End of test case