-- MySQL Triggers for Automatic Contact Group Count Management
-- Database: blackmural_bulk_sms
-- Created: 2025-09-20
--
-- These triggers automatically maintain the total_contacts field in the contact_groups table
-- whenever contacts are added, removed, or moved between groups.

-- Drop existing triggers if they exist
DROP TRIGGER IF EXISTS contact_group_members_after_insert;
DROP TRIGGER IF EXISTS contact_group_members_after_delete;
DROP TRIGGER IF EXISTS contact_group_members_after_update;

-- INSERT Trigger: Automatically increment total_contacts when a contact is added to a group
DELIMITER $$

CREATE TRIGGER contact_group_members_after_insert
AFTER INSERT ON contact_group_members
FOR EACH ROW
BEGIN
    UPDATE contact_groups
    SET total_contacts = (
        SELECT COUNT(*)
        FROM contact_group_members
        WHERE group_id = NEW.group_id
    )
    WHERE id = NEW.group_id;
END$$

DELIMITER ;

-- DELETE Trigger: Automatically decrement total_contacts when a contact is removed from a group
-- Uses GREATEST(0, ...) to prevent negative counts
DELIMITER $$

CREATE TRIGGER contact_group_members_after_delete
AFTER DELETE ON contact_group_members
FOR EACH ROW
BEGIN
    UPDATE contact_groups
    SET total_contacts = GREATEST(0, (
        SELECT COUNT(*)
        FROM contact_group_members
        WHERE group_id = OLD.group_id
    ))
    WHERE id = OLD.group_id;
END$$

DELIMITER ;

-- UPDATE Trigger: Handle contact movement between groups
-- Updates counts for both the old and new groups when group_id changes
DELIMITER $$

CREATE TRIGGER contact_group_members_after_update
AFTER UPDATE ON contact_group_members
FOR EACH ROW
BEGIN
    -- Only update if group_id actually changed
    IF OLD.group_id != NEW.group_id THEN
        -- Update the old group count (decrement)
        UPDATE contact_groups
        SET total_contacts = GREATEST(0, (
            SELECT COUNT(*)
            FROM contact_group_members
            WHERE group_id = OLD.group_id
        ))
        WHERE id = OLD.group_id;

        -- Update the new group count (increment)
        UPDATE contact_groups
        SET total_contacts = (
            SELECT COUNT(*)
            FROM contact_group_members
            WHERE group_id = NEW.group_id
        )
        WHERE id = NEW.group_id;
    END IF;
END$$

DELIMITER ;

-- Initialize existing counts for all groups
-- Run this once to set correct counts for existing data
UPDATE contact_groups cg
SET total_contacts = (
    SELECT COUNT(*)
    FROM contact_group_members cgm
    WHERE cgm.group_id = cg.id
);

-- Verification queries
-- SELECT id, group_name, total_contacts FROM contact_groups;
-- SHOW TRIGGERS;