-- =====================================================================
-- Migration 008: Adds comment_count Column with automatic Trigger
-- Mirrors Pattern from likes_count and dislikes_count.
-- =====================================================================


-- ---------------------------------------------------------------------
-- Block 1: Adds comment_count Column to Contributions
-- ---------------------------------------------------------------------
ALTER TABLE contributions
    ADD COLUMN comment_count INTEGER NOT NULL DEFAULT 0;


-- ---------------------------------------------------------------------
-- Block 2: Backfills existing Comment Counts
-- ---------------------------------------------------------------------
UPDATE contributions c
SET comment_count = (
    SELECT COUNT(*)
    FROM comments cm
    WHERE cm.contribution_id = c.contribution_id
      AND cm.status = 'approved'
);


-- ---------------------------------------------------------------------
-- Block 3: Trigger Function to update comment_count
-- Fires on Status Change on comments. Only counts approved Comments
-- ---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        UPDATE contributions
        SET comment_count = (
            SELECT COUNT(*) FROM comments
            WHERE contribution_id = NEW.contribution_id
              AND status = 'approved'
        )
        WHERE contribution_id = NEW.contribution_id;
    END IF;

    IF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND OLD.contribution_id != NEW.contribution_id) THEN
        UPDATE contributions
        SET comment_count = (
            SELECT COUNT(*) FROM comments
            WHERE contribution_id = OLD.contribution_id
              AND status = 'approved'
        )
        WHERE contribution_id = OLD.contribution_id;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


-- ---------------------------------------------------------------------
-- Block 4: Attaches Trigger to comments Table
-- ---------------------------------------------------------------------
CREATE TRIGGER trigger_update_comment_count
    AFTER INSERT OR DELETE OR UPDATE OF status
    ON comments
    FOR EACH ROW
    EXECUTE FUNCTION update_comment_count();