CREATE TABLE `shot_generation_plans` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `project_id` BIGINT NOT NULL,
  `episode_id` BIGINT NOT NULL,
  `shot_id` BIGINT NOT NULL,
  `revision` INTEGER NOT NULL,
  `plan_version` VARCHAR(80) NOT NULL,
  `plan_hash` VARCHAR(64) NOT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'frozen',
  `source_engine_version` VARCHAR(50) NOT NULL,
  `provider_code` VARCHAR(100) NOT NULL,
  `provider_id` BIGINT NULL,
  `model_name` VARCHAR(160) NULL,
  `endpoint` VARCHAR(255) NULL,
  `capability_version` VARCHAR(100) NULL,
  `route_tier` VARCHAR(50) NULL,
  `effective_mode` VARCHAR(30) NULL,
  `resolution_recommendation` VARCHAR(30) NULL,
  `effective_generation_resolution` VARCHAR(30) NULL,
  `aspect_ratio` VARCHAR(30) NULL,
  `duration` DECIMAL(6, 2) NULL,
  `sound_enabled` BOOLEAN NOT NULL DEFAULT true,
  `multi_shot` BOOLEAN NOT NULL DEFAULT false,
  `native_4k_candidate` BOOLEAN NOT NULL DEFAULT false,
  `project_native_4k_enabled` BOOLEAN NOT NULL DEFAULT false,
  `required_assets_json` JSON NULL,
  `element_plan_json` JSON NULL,
  `voice_plan_json` JSON NULL,
  `keyframe_plan_json` JSON NULL,
  `video_request_json` JSON NULL,
  `router_decision_json` JSON NOT NULL,
  `quality_policy_json` JSON NOT NULL,
  `retry_policy_json` JSON NOT NULL,
  `fallback_chain_json` JSON NOT NULL,
  `cost_policy_json` JSON NULL,
  `provider_snapshot_json` JSON NULL,
  `prompt_snapshot_json` JSON NULL,
  `frozen_by_user_id` BIGINT NULL,
  `frozen_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

  UNIQUE INDEX `sgp_shot_revision_key` (`shot_id`, `revision`),
  UNIQUE INDEX `sgp_shot_hash_key` (`shot_id`, `plan_hash`),
  INDEX `sgp_project_episode_shot_idx` (`project_id`, `episode_id`, `shot_id`),
  INDEX `sgp_provider_capability_idx` (`provider_code`, `capability_version`),
  INDEX `sgp_status_created_idx` (`status`, `created_at`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `storyboard_shots`
  ADD COLUMN `active_generation_plan_id` BIGINT NULL,
  ADD INDEX `ss_active_generation_plan_idx` (`active_generation_plan_id`);

ALTER TABLE `shot_images`
  ADD COLUMN `generation_plan_id` BIGINT NULL,
  ADD INDEX `si_generation_plan_idx` (`generation_plan_id`);

ALTER TABLE `video_clips`
  ADD COLUMN `generation_plan_id` BIGINT NULL,
  ADD INDEX `vc_generation_plan_idx` (`generation_plan_id`);

ALTER TABLE `render_tasks`
  ADD COLUMN `generation_plan_id` BIGINT NULL,
  ADD INDEX `rt_generation_plan_idx` (`generation_plan_id`);