-- Trigger 1

CREATE OR REPLACE FUNCTION trg_prevent_group_delete()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE cnt INTEGER;
BEGIN
  SELECT COUNT(*) INTO cnt FROM Students WHERE group_id = OLD.group_id AND status = 'УЧИТСЯ';
  IF cnt > 0 THEN
    RAISE EXCEPTION 'Cannot delete group %: % active students exist', OLD.group_id, cnt;
  END IF;
  RETURN OLD;
END;
$$;

CREATE TRIGGER prevent_group_delete
BEFORE DELETE ON Groups
FOR EACH ROW
EXECUTE PROCEDURE trg_prevent_group_delete();

-- Trigger 2
CREATE OR REPLACE FUNCTION trg_lowercase_mail()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.mail := lower(NEW.mail);
  RETURN NEW;
END;
$$;

CREATE TRIGGER lowercase_mail
BEFORE INSERT OR UPDATE ON Physicals
FOR EACH ROW
EXECUTE PROCEDURE trg_lowercase_mail();

-- Trigger 3
CREATE OR REPLACE FUNCTION trg_sync_student_status()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  UPDATE Students
     SET status = NEW.new_status
   WHERE id = NEW.student_id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER sync_student_status
AFTER INSERT ON Movement
FOR EACH ROW
EXECUTE PROCEDURE trg_sync_student_status();

-- Trigger SCD 2 
CREATE OR REPLACE FUNCTION trg_supervisors_history()
RETURNS trigger AS $$
BEGIN
  NEW.valid_from = NOW();
  INSERT INTO Supervisors (
    person,
    experience,
    defended_ratio,
    qualification,
    valid_from,
    valid_to
  ) VALUES (
    OLD.person,
    OLD.experience,
    OLD.defended_ratio,
    OLD.qualification,
    OLD.valid_from,
    NOW()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_supervisors_scd
BEFORE UPDATE ON Supervisors
FOR EACH ROW
EXECUTE PROCEDURE trg_supervisors_history();

CREATE OR REPLACE FUNCTION check_unique_supervisor()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM Supervisors 
        WHERE person = NEW.person
        AND experience = NEW.experience
        AND defended_ratio = NEW.defended_ratio
        AND qualification = NEW.qualification
        AND valid_from = NEW.valid_from
        AND (valid_to = NEW.valid_to OR (valid_to IS NULL AND NEW.valid_to IS NULL))
    ) THEN
        RAISE EXCEPTION 'Дублирующая запись в таблице Supervisors. Все поля (кроме id) должны быть уникальными.';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER unique_supervisor_trigger
BEFORE INSERT ON Supervisors
FOR EACH ROW
EXECUTE FUNCTION check_unique_supervisor();