-- Aitori Stylist release deployment script
-- Target: MySQL 5.7+ / MySQL 8+
-- This script is safe to run once on the production database. It is also
-- idempotent for the schema changes, so a rerun will not add the column or
-- index a second time.

DELIMITER $$

CREATE PROCEDURE `aitori_apply_stylist_release_20260808`()
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'hairstylist_user'
      AND COLUMN_NAME = 'simulation_invite_code'
  ) THEN
    ALTER TABLE `hairstylist_user`
      ADD COLUMN `simulation_invite_code` VARCHAR(8) NULL AFTER `password`;
  END IF;

  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'hairstylist_user'
      AND INDEX_NAME = 'hairstylist_user_simulation_invite_code_key'
  ) THEN
    CREATE UNIQUE INDEX `hairstylist_user_simulation_invite_code_key`
      ON `hairstylist_user` (`simulation_invite_code`);
  END IF;
END$$

DELIMITER ;

CALL `aitori_apply_stylist_release_20260808`();
DROP PROCEDURE `aitori_apply_stylist_release_20260808`;

-- Give the standard local/demo stylist the documented code without replacing
-- a code that has already been issued to a production stylist.
UPDATE `hairstylist_user`
SET `simulation_invite_code` = 'K8HUY7SS'
WHERE `email` = 'stylist@aitori.jp'
  AND `is_deleted` = 0
  AND (`simulation_invite_code` IS NULL OR `simulation_invite_code` = '');

-- Backfill stable 8-character invitation codes for every existing active
-- stylist that does not have one yet. "S" + a zero-padded base-36 id keeps
-- the values in the allowed uppercase alphanumeric format and avoids random
-- collisions for normal account id ranges.
UPDATE `hairstylist_user`
SET `simulation_invite_code` = CONCAT(
  'S',
  LPAD(UPPER(CONV(`id`, 10, 36)), 7, '0')
)
WHERE `is_deleted` = 0
  AND (`simulation_invite_code` IS NULL OR `simulation_invite_code` = '');

-- The template package is a ZIP file used by the AI service, not a browser
-- image. Set matching system template cover URLs to their display previews.
-- template_url remains unchanged and continues to be sent to the AI service.
UPDATE `hair_template`
SET `cover_url` = CASE `template_name`
  WHEN '居家造型' THEN '/images/mvp-simulation-result-10/home-styling.jpg'
  WHEN '居家造型30天后造型' THEN '/images/mvp-simulation-result-10/after-30-days.jpg'
  WHEN '居家造型60天后造型' THEN '/images/mvp-simulation-result-10/after-60-days.jpg'
  ELSE `cover_url`
END
WHERE `is_system_template` = 'YES'
  AND `is_deleted` = 0
  AND `template_name` IN ('居家造型', '居家造型30天后造型', '居家造型60天后造型')
  AND (`cover_url` = '' OR `cover_url` REGEXP '\\.zip(\\?.*)?$');