-- =====================================================================
--  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.
-- ---------------------------------------------------------------------
