-- ===================================================================== -- GuestBook Consolidation Migration -- Target database: bigsness_guestbook2026 -- Date: 2026-05-09 -- -- WHAT THIS DOES: -- 1. Creates event_types table (the new flexible event-type system) -- 2. Seeds it with 14 event types -- 3. Adds event_type_id column to events table (default = 'other') -- 4. Backfills existing events to event_type_id = 14 ('other') so nothing breaks -- -- HOW TO RUN: -- Open phpMyAdmin -> select bigsness_guestbook2026 -> Import tab -> -- upload this file -> Go. -- -- IDEMPOTENT: Safe to run more than once. Uses IF NOT EXISTS / ON DUPLICATE. -- ===================================================================== -- --------------------------------------------------------------------- -- 1. event_types table -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `event_types` ( `id` INT NOT NULL AUTO_INCREMENT, `slug` VARCHAR(64) NOT NULL, `display_name` VARCHAR(128) NOT NULL, `icon_emoji` VARCHAR(8) NOT NULL DEFAULT '✨', `sort_order` INT NOT NULL DEFAULT 100, `is_active` TINYINT(1) NOT NULL DEFAULT 1, -- Customizable copy fields (each event type personalizes the experience) `welcome_headline` VARCHAR(255) NOT NULL, `welcome_subheadline` VARCHAR(500) NOT NULL, `recording_prompt` VARCHAR(500) NOT NULL, `photo_prompt` VARCHAR(500) NOT NULL, `qr_printout_copy` VARCHAR(500) NOT NULL, `host_onboarding_tip` VARCHAR(500) NOT NULL, `email_subject` VARCHAR(255) NOT NULL, `email_body_intro` TEXT NOT NULL, `thank_you_copy` VARCHAR(500) NOT NULL, `slideshow_intro` VARCHAR(500) NOT NULL, `gallery_title` VARCHAR(128) NOT NULL, `default_duration_days` INT NOT NULL DEFAULT 90, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_event_types_slug` (`slug`), KEY `idx_event_types_active_sort` (`is_active`, `sort_order`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- 2. Seed the 14 event types (ON DUPLICATE KEY UPDATE makes it idempotent) -- --------------------------------------------------------------------- INSERT INTO `event_types` (`slug`, `display_name`, `icon_emoji`, `sort_order`, `welcome_headline`, `welcome_subheadline`, `recording_prompt`, `photo_prompt`, `qr_printout_copy`, `host_onboarding_tip`, `email_subject`, `email_body_intro`, `thank_you_copy`, `slideshow_intro`, `gallery_title`, `default_duration_days`) VALUES -- 1 ------------------------------------------------------------------- ('wedding', 'Wedding', 'πŸ’', 10, 'Leave a message for the happy couple', 'Share your love, advice, or favorite memory β€” they''ll treasure it forever.', 'What''s your wish for {host_name} as they begin this new chapter?', 'Snap a photo from the day you want them to remember.', 'Scan the QR code to leave a video, photo, or audio message for the couple.', 'Most wedding hosts collect 30–80 messages. Place QR cards on every table.', 'A guest book message for {host_name}', 'Hi! {host_name} is collecting messages from everyone at the wedding. It only takes a minute and they''ll cherish it forever.', 'Thank you β€” your message has been added to the guest book.', 'Messages from the people who celebrated with you', 'Wedding Guest Book', 365), -- 2 ------------------------------------------------------------------- ('memorial', 'Memorial / Celebration of Life', 'πŸ•―οΈ', 20, 'Share a memory of {host_name}', 'Your words, voice, or favorite photo will mean the world to the family.', 'What memory of {host_name} would you most like to share?', 'Share a photo that captures who they were.', 'Scan to share a memory, photo, or video of {host_name}.', 'Place QR cards at the entrance and reception. Most families receive 40–100 memories.', 'A memory of {host_name}', 'The family of {host_name} is collecting memories from everyone whose life they touched. Your message will be kept and treasured.', 'Thank you for sharing this memory. The family will treasure it.', 'Memories of a life well lived', 'In Memory', 730), -- 3 ------------------------------------------------------------------- ('birthday', 'Birthday', 'πŸŽ‚', 30, 'Wish {host_name} a happy birthday', 'Leave a message, video, or photo they''ll smile at for years.', 'What''s your favorite memory or birthday wish for {host_name}?', 'Share a fun photo of you two.', 'Scan to leave a birthday message for {host_name}.', 'Place QR cards at the entrance and on the cake table for max participation.', 'A birthday message for {host_name}', '{host_name} is celebrating, and we''re collecting messages from everyone who came. Just takes a minute!', 'Thank you β€” your birthday message has been added.', 'Birthday wishes from the people who showed up', 'Birthday Guest Book', 180), -- 4 ------------------------------------------------------------------- ('milestone-birthday', 'Milestone Birthday (50/60/75/etc.)', 'πŸŽ‰', 40, 'Celebrate {host_name}''s milestone', 'They''ve made it this far β€” leave a message they''ll keep forever.', 'What does {host_name} mean to you, and what wish do you have for the years ahead?', 'Share a photo from somewhere along the journey.', 'Scan to leave {host_name} a milestone message.', 'Hosts often share these messages at the party itself β€” collect early.', 'A milestone message for {host_name}', '{host_name} is hitting a milestone, and the family is collecting messages from everyone who has been part of the journey.', 'Thank you β€” your message is part of the milestone.', 'Reflections from a life''s worth of friendship', 'Milestone Guest Book', 365), -- 5 ------------------------------------------------------------------- ('anniversary', 'Anniversary', 'πŸ’ž', 50, 'Celebrate {host_name}''s anniversary', 'Share a memory or wish for the years ahead.', 'What''s your favorite memory of them as a couple?', 'Share a photo from somewhere in their journey together.', 'Scan to leave an anniversary message.', 'Anniversary parties tend to skew older guests β€” large QR codes help.', 'An anniversary message for {host_name}', '{host_name} is celebrating an anniversary, and we''re collecting messages from everyone who''s been part of their story.', 'Thank you β€” your message is part of the celebration.', 'A love story, told by the people who watched it grow', 'Anniversary Guest Book', 365), -- 6 ------------------------------------------------------------------- ('baby-shower', 'Baby Shower', 'πŸ‘Ά', 60, 'Welcome the little one', 'Leave a message of advice, hopes, or love for the new arrival.', 'What advice or wish do you have for the new parents?', 'Share a photo to welcome the little one.', 'Scan to leave a message for the new baby.', 'Some parents play these messages back on the baby''s first birthday β€” beautiful keepsake.', 'A message for the baby', '{host_name} is expecting, and we''re collecting messages of love and advice from everyone in the village.', 'Thank you β€” your message has been saved for the baby.', 'Messages from the village that''s ready to welcome you', 'Baby Shower Guest Book', 1095), -- 7 ------------------------------------------------------------------- ('graduation', 'Graduation', 'πŸŽ“', 70, 'Celebrate {host_name}''s graduation', 'Share advice, congratulations, or a memory from the journey.', 'What advice do you have for {host_name} as they head into what''s next?', 'Share a photo from somewhere along the way.', 'Scan to leave {host_name} a graduation message.', 'Graduation guest books are wonderful for first-job nerves years later.', 'A graduation message for {host_name}', '{host_name} is graduating, and we''re collecting messages of congratulations and advice for what''s next.', 'Thank you β€” your message has been added.', 'Words from the people who got them here', 'Graduation Guest Book', 365), -- 8 ------------------------------------------------------------------- ('retirement', 'Retirement', 'πŸ†', 80, 'Celebrate {host_name}''s retirement', 'Share a memory, thank-you, or wish for the next chapter.', 'What''s your favorite memory of working with {host_name}?', 'Share a photo from the years you worked together.', 'Scan to leave {host_name} a retirement message.', 'Coworkers across decades often want to contribute β€” share the link widely.', 'A retirement message for {host_name}', '{host_name} is retiring, and we''re collecting messages from everyone they worked with over the years.', 'Thank you β€” your message has been added.', 'A career, told by the people who lived it alongside', 'Retirement Guest Book', 365), -- 9 ------------------------------------------------------------------- ('corporate', 'Corporate Event / Conference', 'πŸ’Ό', 90, 'Share your takeaways', 'Leave a message, photo, or testimonial from the event.', 'What''s the most valuable thing you''re taking away?', 'Share a photo from the event.', 'Scan to leave a message or testimonial from the event.', 'Corporate hosts use these for testimonials and post-event recap reels.', 'Your message from {host_name}', 'Thanks for joining {host_name}. We''re collecting takeaways and testimonials from everyone who attended.', 'Thank you for your message.', 'Voices from the event', 'Event Messages', 180), -- 10 ------------------------------------------------------------------ ('religious', 'Religious Ceremony', 'β›ͺ', 100, 'Celebrate {host_name}', 'Leave a blessing, message, or memory.', 'What blessing or wish do you have for {host_name}?', 'Share a photo from the ceremony or earlier in the journey.', 'Scan to leave a blessing or message for {host_name}.', 'Works beautifully for bar/bat mitzvahs, quinceaΓ±eras, baptisms, confirmations.', 'A blessing for {host_name}', '{host_name}''s family is collecting blessings and messages from everyone present today.', 'Thank you β€” your blessing has been added.', 'Blessings from the community', 'Ceremony Guest Book', 365), -- 11 ------------------------------------------------------------------ ('reunion', 'Reunion', '🀝', 110, 'Reconnect at the reunion', 'Share a memory, an update on your life, or a hello.', 'What''s your favorite memory from back then? What are you up to now?', 'Share a photo β€” then or now.', 'Scan to leave a memory or update for the reunion guest book.', 'Family, class, military β€” reunions are perfect for catching up at scale.', 'A reunion message', 'We''re collecting memories and life updates from everyone at the reunion. Just takes a minute.', 'Thank you β€” your message is part of the reunion archive.', 'Catching up, remembering, reconnecting', 'Reunion Guest Book', 365), -- 12 ------------------------------------------------------------------ ('holiday', 'Holiday Gathering', 'πŸŽ„', 120, 'Holiday wishes from the gathering', 'Leave a message for the family from this year''s gathering.', 'What''s your favorite memory or wish from this year''s gathering?', 'Share a photo from the gathering.', 'Scan to leave a holiday message from the gathering.', 'Annual hosts can build a multi-year archive β€” same QR every year.', 'A holiday message from the gathering', 'We''re collecting messages from everyone at this year''s gathering. Quick way to remember the year together.', 'Thank you β€” your holiday message is saved.', 'A year remembered, together', 'Holiday Guest Book', 365), -- 13 ------------------------------------------------------------------ ('housewarming', 'Housewarming', '🏠', 130, 'Welcome them home', 'Leave a message, blessing, or memory to mark the new chapter.', 'What wish do you have for {host_name} in their new home?', 'Share a photo β€” of the house, the day, or the people.', 'Scan to leave a housewarming message.', 'Some hosts print a small book of these for the coffee table.', 'A housewarming message for {host_name}', '{host_name} just moved in, and we''re collecting messages to mark the new chapter.', 'Thank you β€” your message is part of the new home.', 'Welcomes to a brand new chapter', 'Housewarming Guest Book', 365), -- 14 ------------------------------------------------------------------ ('other', 'Other / Custom', '✨', 999, 'Leave a message', 'Share a video, photo, or audio message for {host_name}.', 'Leave a message for {host_name}.', 'Share a photo.', 'Scan to leave {host_name} a message.', 'You can fully customize all the wording from your event dashboard.', 'A message for {host_name}', '{host_name} is collecting messages, and they''d love to hear from you.', 'Thank you β€” your message has been saved.', 'Messages from the people who were there', 'Guest Book', 180) ON DUPLICATE KEY UPDATE `display_name` = VALUES(`display_name`), `icon_emoji` = VALUES(`icon_emoji`), `sort_order` = VALUES(`sort_order`), `welcome_headline` = VALUES(`welcome_headline`), `welcome_subheadline` = VALUES(`welcome_subheadline`), `recording_prompt` = VALUES(`recording_prompt`), `photo_prompt` = VALUES(`photo_prompt`), `qr_printout_copy` = VALUES(`qr_printout_copy`), `host_onboarding_tip` = VALUES(`host_onboarding_tip`), `email_subject` = VALUES(`email_subject`), `email_body_intro` = VALUES(`email_body_intro`), `thank_you_copy` = VALUES(`thank_you_copy`), `slideshow_intro` = VALUES(`slideshow_intro`), `gallery_title` = VALUES(`gallery_title`), `default_duration_days` = VALUES(`default_duration_days`); -- --------------------------------------------------------------------- -- 3. Add event_type_id to events table (safe on re-run) -- --------------------------------------------------------------------- -- MySQL/MariaDB versions vary on IF NOT EXISTS support for ALTER. Use a -- procedural check to keep this idempotent across versions. DROP PROCEDURE IF EXISTS `sp_add_event_type_column`; DELIMITER $$ CREATE PROCEDURE `sp_add_event_type_column`() BEGIN IF NOT EXISTS ( SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'events' AND COLUMN_NAME = 'event_type_id' ) THEN ALTER TABLE `events` ADD COLUMN `event_type_id` INT NULL DEFAULT NULL AFTER `id`; END IF; IF NOT EXISTS ( SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'events' AND INDEX_NAME = 'idx_events_event_type_id' ) THEN ALTER TABLE `events` ADD KEY `idx_events_event_type_id` (`event_type_id`); END IF; END$$ DELIMITER ; CALL `sp_add_event_type_column`(); DROP PROCEDURE `sp_add_event_type_column`; -- --------------------------------------------------------------------- -- 4. Backfill: any pre-existing events with NULL event_type_id default -- to "other" (id of the 'other' row). -- --------------------------------------------------------------------- UPDATE `events` e JOIN `event_types` et ON et.`slug` = 'other' SET e.`event_type_id` = et.`id` WHERE e.`event_type_id` IS NULL; -- --------------------------------------------------------------------- -- DONE. -- ---------------------------------------------------------------------