== Triggers ==

=== Build Price Calculation Trigger (build_total_price) ===

**What it does:** Automatically recalculates the `total_price` of a build whenever components are added, removed, or modified. \\
**When it triggers:** AFTER `INSERT`, `UPDATE`, or `DELETE` on the `build_component` table. \\
**Why it is useful:** Ensures the build price is always mathematically correct and consistent with the sum of its parts, removing the need for manual calculation in the application layer. \\

{{{
CREATE OR REPLACE FUNCTION update_build_total_price()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
DECLARE
    target_build_id INT;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        target_build_id := OLD.build_id;
    ELSE
        target_build_id := NEW.build_id;
    END IF;

    UPDATE "build"
    SET "total_price" = (
        SELECT COALESCE(SUM(c.price * bc.num_components), 0)
        FROM "build_component" bc
        JOIN "components" c ON bc.component_id = c.id
        WHERE bc.build_id = target_build_id
    )
    WHERE id = target_build_id;

    RETURN NULL;
END;
$$;

DROP TRIGGER IF EXISTS trigger_auto_update_price ON "build_component";
CREATE TRIGGER trigger_auto_update_price
AFTER INSERT OR UPDATE OR DELETE ON "build_component"
FOR EACH ROW
EXECUTE FUNCTION update_build_total_price();
}}}

=== Self-Review Prevention Trigger (check_review_validity) ===

**What it does:** Checks if the user trying to write a review is the creator of the build. If they are, it raises an exception. \\
**When it triggers:** BEFORE `INSERT` on the `review` table. \\
**Why it is useful:** Enforces data integrity by preventing users from artificially boosting their own build's popularity with fake reviews. \\

{{{
CREATE OR REPLACE FUNCTION check_review_validity()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
DECLARE
    build_owner_id INT;
BEGIN
    SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id;

    IF NEW.user_id = build_owner_id THEN
        RAISE EXCEPTION 'Cannot review own builds';
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trigger_check_self_review ON "review";
CREATE TRIGGER trigger_check_self_review
BEFORE INSERT ON "review"
FOR EACH ROW
EXECUTE FUNCTION check_review_validity();
}}}

=== Self-Rating Prevention Trigger (check_rating_validity) ===
**What it does:** Checks if the user trying to rate a build (1-5 stars) is the creator of the build. If so, it blocks the action. \\
**When it triggers:** BEFORE `INSERT` on the `rating_build` table. \\
**Why it is useful:** Enforces data integrity by preventing users from artificially boosting their own build's popularity with 5 star ratings. \\

{{{
CREATE OR REPLACE FUNCTION check_rating_validity()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
DECLARE
    build_owner_id INT;
BEGIN
    SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id;

    IF NEW.user_id = build_owner_id THEN
        RAISE EXCEPTION 'Cannot rate own builds.';
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trigger_check_self_rating ON "rating_build";
CREATE TRIGGER trigger_check_self_rating
BEFORE INSERT ON "rating_build"
FOR EACH ROW
EXECUTE FUNCTION check_rating_validity();
}}}