DELIMITER $$

CREATE PROCEDURE insert_post_with_tags(
    IN post_text TEXT,
    IN post_video VARCHAR(255),
    IN tag_list TEXT
)
BEGIN
    DECLARE last_post_id BIGINT UNSIGNED;
    DECLARE random_user_id BIGINT UNSIGNED;
    DECLARE current_tag VARCHAR(255);
    DECLARE remaining_tags TEXT;

    SET remaining_tags = tag_list;

    SET random_user_id = FLOOR(1 + (RAND() * 100));

INSERT INTO posts (user_id, text, video, created_at, updated_at)
VALUES (random_user_id, post_text, post_video, NOW(), NOW());
SET last_post_id = LAST_INSERT_ID();

    WHILE LOCATE(',', remaining_tags) > 0 DO
        SET current_tag = TRIM(SUBSTRING_INDEX(remaining_tags, ',', 1));
        SET remaining_tags = TRIM(SUBSTRING(remaining_tags, LOCATE(',', remaining_tags) + 1));

INSERT INTO tags (name, created_at, updated_at)
SELECT current_tag, NOW(), NOW()
    WHERE NOT EXISTS (SELECT 1 FROM tags WHERE name = current_tag);

INSERT INTO taggables (tag_id, taggable_id, taggable_type, created_at, updated_at)
VALUES (
           (SELECT id FROM tags WHERE name = current_tag),
           last_post_id,
           'post',
           NOW(),
           NOW()
       );
END WHILE;


    IF LENGTH(remaining_tags) > 0 THEN
        SET current_tag = TRIM(remaining_tags);

INSERT INTO tags (name, created_at, updated_at)
SELECT current_tag, NOW(), NOW()
    WHERE NOT EXISTS (SELECT 1 FROM tags WHERE name = current_tag);

INSERT INTO taggables (tag_id, taggable_id, taggable_type, created_at, updated_at)
VALUES (
           (SELECT id FROM tags WHERE name = current_tag),
           last_post_id,
           'post',
           NOW(),
           NOW()
       );
END IF;
END$$

DELIMITER ;
