DROP PROCEDURE IF EXISTS add_product_in_change_log;;
CREATE PROCEDURE add_product_in_change_log(IN updated_product_id INT(11))
READS SQL DATA
BEGIN
    INSERT INTO products_change_log
        SET product_id = updated_product_id
        ON DUPLICATE KEY UPDATE product_id = updated_product_id;
END;;

DROP TRIGGER IF EXISTS after_products_update;;
CREATE TRIGGER after_products_update
    AFTER UPDATE ON products
    FOR EACH ROW 
BEGIN
    IF NEW.shopify_id IS NULL OR NEW.shopify_id = OLD.shopify_id THEN
        CALL add_product_in_change_log(NEW.product_id);
    END IF;
END;;

DROP TRIGGER IF EXISTS after_products_insert;;
CREATE TRIGGER after_products_insert 
    AFTER INSERT ON products
    FOR EACH ROW 
BEGIN
    CALL add_product_in_change_log(NEW.product_id);
END;;

DROP TRIGGER IF EXISTS after_products_images_update;;
CREATE TRIGGER after_products_images_update
    AFTER UPDATE ON product_images
    FOR EACH ROW 
BEGIN
    IF NEW.shopify_id IS NULL OR NEW.shopify_id = OLD.shopify_id THEN
        CALL add_product_in_change_log(NEW.product_id);
    END IF;
END;;

DROP TRIGGER IF EXISTS after_products_images_insert;;
CREATE TRIGGER after_products_images_insert 
    AFTER INSERT ON product_images
    FOR EACH ROW 
BEGIN
    CALL add_product_in_change_log(NEW.product_id);
END;;

DROP TRIGGER IF EXISTS after_product_product_options_update;;
CREATE TRIGGER after_product_product_options_update
    AFTER UPDATE ON product_product_options
    FOR EACH ROW 
BEGIN
    CALL add_product_in_change_log(NEW.product_id);
END;;

DROP TRIGGER IF EXISTS after_product_product_options_insert;;
CREATE TRIGGER after_product_product_options_insert 
    AFTER INSERT ON product_product_options
    FOR EACH ROW 
BEGIN
    CALL add_product_in_change_log(NEW.product_id);
END;;
