-- ============================================================
-- G004 Media Engine Foundation — Safe Migration
-- ============================================================
--
-- This migration is SAFE to run on existing installations.
-- It does NOT alter existing tables or columns.
--
-- What it does:
--   1. Adds a unique constraint on (invitation_id, section_slug)
--      in invitation_sections — required for the ON DUPLICATE KEY
--      UPDATE upsert pattern used by InvitationModel::saveMediaSection().
--      If the constraint already exists, the ALTER is skipped via
--      the stored procedure wrapper below.
--
--   2. For every existing invitation that does NOT yet have a
--      'media' row in invitation_sections, inserts an empty
--      default row (INSERT IGNORE). This ensures the media manager
--      page works for all existing workspaces immediately.
--
-- NOTE: Run this once after deploying G004.
--      Safe to re-run — all statements are idempotent.
--
-- @ref G004
-- ============================================================

-- Step 1: Add unique constraint if not already present.
-- We wrap it in a procedure so the script is idempotent.
DROP PROCEDURE IF EXISTS g004_add_unique_constraint;

DELIMITER $$
CREATE PROCEDURE g004_add_unique_constraint()
BEGIN
    -- Check if the unique key already exists
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.TABLE_CONSTRAINTS
        WHERE TABLE_SCHEMA   = DATABASE()
          AND TABLE_NAME     = 'invitation_sections'
          AND CONSTRAINT_NAME = 'uq_invitation_sections_slug'
          AND CONSTRAINT_TYPE = 'UNIQUE'
    ) THEN
        ALTER TABLE `invitation_sections`
            ADD UNIQUE KEY `uq_invitation_sections_slug` (`invitation_id`, `section_slug`);
    END IF;
END$$
DELIMITER ;

CALL g004_add_unique_constraint();
DROP PROCEDURE IF EXISTS g004_add_unique_constraint;

-- Step 2: Seed default empty 'media' section rows for all existing invitations.
-- INSERT IGNORE is safe — skips silently if the row already exists
-- (once the unique constraint above is applied).

INSERT IGNORE INTO `invitation_sections`
    (`invitation_id`, `section_slug`, `is_visible`, `sort_order`, `section_config`)
SELECT
    i.id,
    'media',
    1,
    99,
    '{"hero":null,"couple":[],"gallery":[],"story":[],"music_cover":null,"music_file":null,"gift":null}'
FROM `invitations` i
WHERE NOT EXISTS (
    SELECT 1
    FROM `invitation_sections` s
    WHERE s.invitation_id = i.id
      AND s.section_slug  = 'media'
);

-- ============================================================
-- End of G004 migration
-- ============================================================