-- Author: fruit.com.cn
-- 转码缩略图轴：默认档位配置 + 单集雪碧图/VTT 元数据（幂等）

CREATE TABLE IF NOT EXISTS media_thumbnail (
  id             BIGINT PRIMARY KEY,
  episode_id     BIGINT       NOT NULL,
  drama_id       BIGINT       NOT NULL DEFAULT 0,
  vtt_object_key VARCHAR(512) NOT NULL DEFAULT '',
  sprite_prefix  VARCHAR(512) NOT NULL DEFAULT '',
  interval_sec   INT          NOT NULL DEFAULT 1,
  columns        INT          NOT NULL DEFAULT 10,
  rows           INT          NOT NULL DEFAULT 1,
  thumb_width    INT          NOT NULL DEFAULT 160,
  thumb_height   INT          NOT NULL DEFAULT 0,
  frame_count    INT          NOT NULL DEFAULT 0,
  duration_sec   INT          NOT NULL DEFAULT 0,
  size_bytes     BIGINT       NOT NULL DEFAULT 0,
  status         VARCHAR(16)  NOT NULL DEFAULT 'FAILED',
  error_msg      VARCHAR(500) NOT NULL DEFAULT '',
  created_at     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted        SMALLINT     NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX IF NOT EXISTS uk_media_thumbnail_episode
  ON media_thumbnail (episode_id);
CREATE INDEX IF NOT EXISTS idx_media_thumbnail_drama
  ON media_thumbnail (drama_id);
COMMENT ON TABLE media_thumbnail IS '单集缩略图雪碧图与 VTT 时间轴';

DROP TRIGGER IF EXISTS trg_media_thumbnail_updated_at ON media_thumbnail;
CREATE TRIGGER trg_media_thumbnail_updated_at
BEFORE UPDATE ON media_thumbnail
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO sys_config (id, config_key, config_value, remark) VALUES
  (2026102201,
   'media.transcode.profile',
   '{"enabled":true,"segmentSeconds":6,"ladder":[' ||
   '{"quality":"360p","width":640,"height":360,"videoBitrateKbps":400,"audioBitrateKbps":64,"enabled":true},' ||
   '{"quality":"480p","width":854,"height":480,"videoBitrateKbps":800,"audioBitrateKbps":64,"enabled":true},' ||
   '{"quality":"720p","width":1280,"height":720,"videoBitrateKbps":1600,"audioBitrateKbps":96,"enabled":true},' ||
   '{"quality":"1080p","width":1920,"height":1080,"videoBitrateKbps":3200,"audioBitrateKbps":128,"enabled":true}],' ||
   '"sprite":{"enabled":true,"intervalSeconds":1,"columns":10,"thumbWidth":160}}',
   '转码档位与缩略图轴配置')
ON CONFLICT (config_key) DO NOTHING;