-- 明星数据表结构
CREATE TABLE IF NOT EXISTS `tpl_app_star_data` (
  `id` INT(11) NOT NULL COMMENT '明星ID（使用接口返回的id字段）',
  `name` VARCHAR(255) DEFAULT NULL COMMENT '姓名',
  `avatar` VARCHAR(512) DEFAULT NULL COMMENT '头像URL（带https:前缀）',
  `avatar_oss` VARCHAR(512) DEFAULT NULL COMMENT '头像OSS存储路径',
  `alias` VARCHAR(255) DEFAULT NULL COMMENT '别名',
  `nationality` VARCHAR(100) DEFAULT NULL COMMENT '国籍（英文）',
  `constellation` VARCHAR(50) DEFAULT NULL COMMENT '星座',
  `weight` VARCHAR(50) DEFAULT NULL COMMENT '体重',
  `birthplace` VARCHAR(255) DEFAULT NULL COMMENT '出生地',
  `agency` VARCHAR(255) DEFAULT NULL COMMENT '经纪公司',
  `graduated_school` VARCHAR(255) DEFAULT NULL COMMENT '毕业院校',
  `foreign_name` VARCHAR(255) DEFAULT NULL COMMENT '外文名',
  `ethnicity` VARCHAR(50) DEFAULT NULL COMMENT '民族',
  `blood_type` VARCHAR(50) DEFAULT NULL COMMENT '血型（纯字母）',
  `height` VARCHAR(50) DEFAULT NULL COMMENT '身高',
  `birthday` VARCHAR(100) DEFAULT NULL COMMENT '出生日期（YYYY-MM-DD格式）',
  `profession` VARCHAR(255) DEFAULT NULL COMMENT '职业',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='明星数据表';

CREATE TABLE `tpl_app_user_devices` (
    `device_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户设备唯一ID (x-device-id)',
    `device_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备类型 (x-device-type)',
    `device_model` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备型号 (x-device-model)',
    `os_version` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作系统版本 (x-os-version)',
    `app_version` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'App 版本号 (x-app-version)',
    `timezone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '客户端时区 (x-timezone)',
    `region` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户地区信息 (x-region)',
    `language` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '语言 (Accept-Language)',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后活跃时间',
    PRIMARY KEY (`device_id`),
    KEY `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `tpl_app_processed_images` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `hostname` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    `region` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户地区代码 (x-region)',
    `file_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other',
    `nickname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `score` decimal(10,4) NOT NULL DEFAULT '0.0000',
    `is_cropped_face` tinyint(1) NOT NULL DEFAULT '0',
    `size_bytes` bigint(20) unsigned NOT NULL DEFAULT '0',
    `last_modified` datetime NOT NULL,
    `bos_uploaded` tinyint(1) NOT NULL DEFAULT '0',
    `extra_metadata` json DEFAULT NULL,
    `processing_duration_ms` int(11) DEFAULT NULL,
    `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_face_image_file_path` (`file_path`),
    KEY `idx_category` (`category`),
    KEY `idx_nickname` (`nickname`),
    KEY `idx_last_modified` (`last_modified`),
    KEY `idx_region` (`region`)
) ENGINE=InnoDB AUTO_INCREMENT=62501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;