CREATE OR REPLACE FUNCTION delete_generic_text() RETURNS TRIGGER AS $delete_generic_text$
    DECLARE
        tid integer;
        typ owner_type_enum;
    BEGIN

        IF (TG_OP = 'DELETE') THEN
        
            IF (TG_TABLE_NAME = 'core_data_category') THEN

                typ = 'data category';
            
            ELSIF (TG_TABLE_NAME = 'core_language_section') THEN
            
                typ = 'language section';

            ELSIF (TG_TABLE_NAME = 'core_linguistic_section') THEN
            
                typ = 'linguistic section';

            ELSIF (TG_TABLE_NAME = 'generic_text') THEN
            
                typ = 'text';

            END IF;
            
            tid = OLD.id;
            
            DELETE FROM
                generic_text
             WHERE
                 generic_text.owner = tid
              AND
                 generic_text.owner_type = typ
              ;

        END IF;

        RETURN NULL;

    END;
$delete_generic_text$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS core_data_category_delete_generic_text ON core_data_category;
CREATE TRIGGER core_data_category_delete_generic_text
AFTER DELETE ON core_data_category
    FOR EACH ROW EXECUTE PROCEDURE delete_generic_text();

DROP TRIGGER IF EXISTS core_language_section_delete_generic_text ON core_language_section;
CREATE TRIGGER core_language_section_delete_generic_text
AFTER DELETE ON core_language_section
    FOR EACH ROW EXECUTE PROCEDURE delete_generic_text();

DROP TRIGGER IF EXISTS core_linguistic_section_delete_generic_text ON core_linguistic_section;
CREATE TRIGGER core_linguistic_section_delete_generic_text
AFTER DELETE ON core_linguistic_section
    FOR EACH ROW EXECUTE PROCEDURE delete_generic_text();

DROP TRIGGER IF EXISTS generic_text_delete_generic_text ON generic_text;
CREATE TRIGGER generic_text_delete_generic_text
AFTER DELETE ON generic_text
    FOR EACH ROW EXECUTE PROCEDURE delete_generic_text();

CREATE OR REPLACE FUNCTION delete_generic_name() RETURNS TRIGGER AS $delete_generic_name$
    DECLARE
        id integer;
        tp owner_type_enum;
    BEGIN

        IF (TG_OP = 'DELETE') THEN
        
            IF (TG_TABLE_NAME = 'core_data_category') THEN

                tp = 'data category';
            
            ELSIF (TG_TABLE_NAME = 'core_language_section') THEN
            
                tp = 'language section';

            END IF;
            
            id = OLD.id;
            
            DELETE FROM
                generic_name
             WHERE
                generic_name.owner = id
              AND
                generic_name.owner_type = tp
              ;

        END IF;

        RETURN NULL;

    END;
$delete_generic_name$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS core_data_category_delete_generic_name ON core_data_category;
CREATE TRIGGER core_data_category_delete_generic_name
AFTER DELETE ON core_data_category
    FOR EACH ROW EXECUTE PROCEDURE delete_generic_name();

DROP TRIGGER IF EXISTS core_language_section_delete_generic_name ON core_language_section;
CREATE TRIGGER core_language_section_delete_generic_name
AFTER DELETE ON core_language_section
    FOR EACH ROW EXECUTE PROCEDURE delete_generic_name();

-- triggers to maintain the full text search vectors/indexes
CREATE OR REPLACE FUNCTION update_search_identifier() RETURNS TRIGGER AS $update_search_identifier$
    DECLARE
	lang RECORD;
    BEGIN

	-- update the language independent search vector
	NEW.search = to_tsvector('simple',NEW.identifier);

	-- update the language specific search vector
	NEW.search_language = to_tsvector('english',NEW.identifier);

	RETURN NEW;

    END;
$update_search_identifier$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS core_data_category_update_search ON core_data_category;
CREATE TRIGGER core_data_category_update_search
BEFORE INSERT OR UPDATE ON core_data_category
    FOR EACH ROW EXECUTE PROCEDURE update_search_identifier();

CREATE OR REPLACE FUNCTION update_search_text() RETURNS TRIGGER AS $update_search_text$
    DECLARE
	lang RECORD;
    BEGIN

	SELECT
		*
	INTO
		lang
	FROM
		core_language
	WHERE
		id = NEW.language
	;

	-- update the language independent search vector
	NEW.search = to_tsvector('simple',NEW.text);

	-- update the language specific search vector
	NEW.search_language = to_tsvector(CAST(lang.search AS regconfig),NEW.text);

	RETURN NEW;

    END;
$update_search_text$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS generic_text_update_search ON generic_text;
CREATE TRIGGER generic_text_update_search
BEFORE INSERT OR UPDATE ON generic_text
    FOR EACH ROW EXECUTE PROCEDURE update_search_text();

CREATE OR REPLACE FUNCTION update_search_name() RETURNS TRIGGER AS $update_search_name$
    DECLARE
	lang RECORD;
    BEGIN

	SELECT
		*
	INTO
		lang
	FROM
		core_language
	WHERE
		id = NEW.language
	;

	-- update the language independent search vector
	NEW.search = to_tsvector('simple',NEW.name);

	-- update the language specific search vector
	NEW.search_language = to_tsvector(CAST(lang.search AS regconfig),NEW.name);

	RETURN NEW;

    END;
$update_search_name$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS generic_name_update_search ON generic_name;
CREATE TRIGGER generic_name_update_search
BEFORE INSERT OR UPDATE ON generic_name
    FOR EACH ROW EXECUTE PROCEDURE update_search_name();

CREATE OR REPLACE FUNCTION prevent_super_cycle() RETURNS TRIGGER AS $update_search_name$
    DECLARE
    BEGIN
    
    IF (dc_super_cycle(ARRAY[NEW.dc],NEW.super))
    THEN
        RAISE EXCEPTION 'Making DC[%] the super of simple DC[%] would create a cycle, so the insert was canceled!', NEW.super, NEW.dc;
    END IF;

	RETURN NEW;

    END;
$update_search_name$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS prevent_dc_super_cycles ON link_simple_data_category_super;
CREATE TRIGGER prevent_dc_super_cycles
BEFORE INSERT OR UPDATE ON link_simple_data_category_super
    FOR EACH ROW EXECUTE PROCEDURE prevent_super_cycle();

