DO $$
DECLARE
    lecture_violations BIGINT;
    laboratory_violations BIGINT;
    practice_violations BIGINT;
BEGIN
    SELECT count(*)
    INTO lecture_violations
    FROM schedule_rules
    WHERE mod(lecture_academic_hours, 2) <> 0;

    SELECT count(*)
    INTO laboratory_violations
    FROM schedule_rules
    WHERE mod(laboratory_academic_hours, 2) <> 0;

    SELECT count(*)
    INTO practice_violations
    FROM schedule_rules
    WHERE mod(practice_academic_hours, 2) <> 0;

    IF lecture_violations > 0
        OR laboratory_violations > 0
        OR practice_violations > 0 THEN
        RAISE EXCEPTION USING
            ERRCODE = '23514',
            MESSAGE = format(
                'Невозможно применить инвариант чётности академических часов правил расписания: лекции=%s, лабораторные=%s, практики=%s. Исправьте данные tenant-БД и повторите миграцию.',
                lecture_violations,
                laboratory_violations,
                practice_violations
            );
    END IF;
END $$;

DO $$
DECLARE
    duplicate_slot_groups BIGINT;
BEGIN
    SELECT count(*)
    INTO duplicate_slot_groups
    FROM (
        SELECT
            schedule_rule_id,
            day_of_week,
            parity,
            time_slot_id,
            teacher_id,
            classroom_id,
            lesson_type_id,
            lesson_format
        FROM schedule_rule_slots
        GROUP BY
            schedule_rule_id,
            day_of_week,
            parity,
            time_slot_id,
            teacher_id,
            classroom_id,
            lesson_type_id,
            lesson_format
        HAVING count(*) > 1
    ) duplicates;

    IF duplicate_slot_groups > 0 THEN
        RAISE EXCEPTION USING
            ERRCODE = '23505',
            MESSAGE = format(
                'Невозможно применить инвариант уникальности слотов правил расписания: найдено групп точных дублей=%s. Исправьте данные tenant-БД и повторите миграцию.',
                duplicate_slot_groups
            );
    END IF;
END $$;

ALTER TABLE schedule_rules
    ADD CONSTRAINT chk_schedule_rules_academic_hours_even
        CHECK (
            mod(lecture_academic_hours, 2) = 0
            AND mod(laboratory_academic_hours, 2) = 0
            AND mod(practice_academic_hours, 2) = 0
        ) NOT VALID;

ALTER TABLE schedule_rules
    VALIDATE CONSTRAINT chk_schedule_rules_academic_hours_even;

ALTER TABLE schedule_rule_slots
    ADD CONSTRAINT uq_schedule_rule_slots_exact_payload
        UNIQUE (
            schedule_rule_id,
            day_of_week,
            parity,
            time_slot_id,
            teacher_id,
            classroom_id,
            lesson_type_id,
            lesson_format
        );
