Напредни PostgreSQL компоненти

1) Интегритетни ограничувања (CHECK/UNIQUE) 1.1 Реакција: различни елементи

ALTER TABLE reaction
  ADD CONSTRAINT reaction_distinct_elements
  CHECK (element1_id <> element2_id);

1.2 Елементи: валидни физички вредности

ALTER TABLE elements
  ADD CONSTRAINT elements_atomic_number_positive CHECK (atomic_number > 0);

ALTER TABLE elements
  ADD CONSTRAINT elements_mass_positive CHECK (atomic_weight > 0);

ALTER TABLE elements
  ADD CONSTRAINT elements_melting_before_boiling CHECK (
    boiling_point IS NULL OR melting_point IS NULL OR melting_point < boiling_point
  );

1.3 Партиципација: без дупликати

ALTER TABLE userparticipatesinexperiment
  ADD CONSTRAINT uq_user_experiment UNIQUE (user_id, experiment_id);

2) Временско печатење на учество

ALTER TABLE userparticipatesinexperiment
  ADD COLUMN IF NOT EXISTS participation_timestamp TIMESTAMPTZ;

UPDATE userparticipatesinexperiment
   SET participation_timestamp = COALESCE(participation_timestamp, NOW());

ALTER TABLE userparticipatesinexperiment
  ALTER COLUMN participation_timestamp SET NOT NULL,
  ALTER COLUMN participation_timestamp SET DEFAULT NOW();

3) Тригер: set_default_safety (на experiment)

CREATE OR REPLACE FUNCTION trg_experiment_default_safety()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.safety_warning IS NULL OR NEW.safety_warning = '' THEN
    NEW.safety_warning := 'Стандардни безбедносни мерки';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS set_default_safety ON experiment;
CREATE TRIGGER set_default_safety
BEFORE INSERT ON experiment
FOR EACH ROW
EXECUTE FUNCTION trg_experiment_default_safety();

4) Погледи (Views) 4.1 vw_students_experiments_detailed

CREATE OR REPLACE VIEW vw_students_experiments_detailed AS
SELECT 
  s.student_id,
  (u.user_name || ' ' || u.user_surname) AS full_name,
  e.experiment_id,
  e.result,
  e.time_stamp AS participation_time
FROM student s
JOIN "User" u ON s.student_id = u.user_id
JOIN userparticipatesinexperiment up ON s.student_id = up.user_id
JOIN experiment e ON up.experiment_id = e.experiment_id
ORDER BY u.user_name, e.time_stamp DESC;

4.2 vw_user_activity_summary

CREATE OR REPLACE VIEW vw_user_activity_summary AS
SELECT 
  u.user_id,
  (u.user_name || ' ' || u.user_surname) AS full_name,
  u.role,
  COUNT(DISTINCT uve.element_id)    AS elements_viewed,
  COUNT(DISTINCT uvl.equipment_id)  AS equipment_viewed,
  COUNT(DISTINCT upe.experiment_id) AS experiments_participated
FROM "User" u
LEFT JOIN userviewselement           uve ON u.user_id = uve.user_id
LEFT JOIN userviewslabequipment      uvl ON u.user_id = uvl.user_id
LEFT JOIN userparticipatesinexperiment upe ON u.user_id = upe.user_id
GROUP BY u.user_id, full_name, u.role
ORDER BY full_name;

4.3 vw_students_experiments_for_teacher

CREATE OR REPLACE VIEW vw_students_experiments_for_teacher AS
SELECT 
  (u.user_name || ' ' || u.user_surname) AS student_name,
  s.student_id,
  e.experiment_id,
  e.result,
  e.time_stamp,
  r.product,
  el1.symbol AS element1_symbol,
  el2.symbol AS element2_symbol,
  up.participation_timestamp AS participation_date,
  s.teacher_id
FROM student s
JOIN "User" u ON s.student_id = u.user_id
JOIN userparticipatesinexperiment up ON s.student_id = up.user_id
JOIN experiment e ON up.experiment_id = e.experiment_id
JOIN reaction  r ON e.reaction_id   = r.reaction_id
JOIN elements el1 ON r.element1_id = el1.element_id
JOIN elements el2 ON r.element2_id = el2.element_id
ORDER BY up.participation_timestamp DESC, student_name;

5) Stored function: create_reaction_and_experiment_fn(...)

create_reaction_and_experiment_fn(
  p_teacher_id    INT,
  p_element1_id   INT,
  p_element2_id   INT,
  p_product       TEXT,
  p_conditions    TEXT,
  p_exp_result    TEXT,
  p_safety        TEXT,
  p_equipment_ids INT[]
) RETURNS TABLE(reaction_id INT, experiment_id INT)

Логика (атомично во една функција):

INSERT во reaction → reaction_id.

Ако p_exp_result е празен → авто-генерира текст „Експеримент со X + Y…“.

INSERT во experiment (time_stamp = CURRENT_TIMESTAMP, safety_warning = p_safety).

Ако има p_equipment_ids → INSERT во experimentlabequipment со unnest(p_equipment_ids).

Враќа {reaction_id, experiment_id}.

Во апликацијата, оваа функција се повикува ако е достапна; ако не, има Python fallback со транскација (исто поведение).

6) Индекси (перформанси)

CREATE INDEX IF NOT EXISTS idx_reaction_elements ON reaction (element1_id, element2_id);
CREATE INDEX IF NOT EXISTS idx_exp_reaction      ON experiment (reaction_id);
CREATE INDEX IF NOT EXISTS idx_up_user           ON userparticipatesinexperiment (user_id);
CREATE INDEX IF NOT EXISTS idx_up_experiment     ON userparticipatesinexperiment (experiment_id);