-- 稻城天文台展台 · daochengpad
-- 字符集 utf8mb4，表前缀 dc_（与 ThinkPHP DB_PREFIX 一致）

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `dc_kiosk_kv` (
  `config_key`   VARCHAR(64)  NOT NULL COMMENT '配置键',
  `config_value` LONGTEXT     NULL COMMENT 'JSON',
  `updated_at`   DATETIME     NULL,
  PRIMARY KEY (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dc_knowledge` (
  `id`          VARCHAR(36)  NOT NULL,
  `type`        VARCHAR(16)  NOT NULL COMMENT '文字/图片/视频',
  `title`       VARCHAR(512) NOT NULL,
  `content`     MEDIUMTEXT   NULL,
  `entry_date`  DATE         NULL,
  `tags`        JSON         NULL,
  `image`       LONGTEXT     NULL COMMENT '封面：/uploads/... 或历史 base64',
  `video_url`   LONGTEXT     NULL COMMENT '视频：/uploads/... 或历史 base64',
  `sort_order`  INT          NOT NULL DEFAULT 0,
  `created_at`  DATETIME     NULL,
  `updated_at`  DATETIME     NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sort` (`sort_order`),
  KEY `idx_date` (`entry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dc_observatory_history` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `kind`        VARCHAR(16)  NOT NULL DEFAULT '图集',
  `title`       VARCHAR(512) NOT NULL,
  `summary`     TEXT         NULL,
  `date_str`    VARCHAR(32)  NULL,
  `thumb`       TEXT         NULL,
  `sort_order`  INT          NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dc_realtime_image` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(255) NOT NULL,
  `time_str`    VARCHAR(64)  NULL,
  `telescope`   VARCHAR(128) NULL,
  `exposure`    VARCHAR(64)  NULL,
  `image_url`   TEXT         NULL,
  `sort_order`  INT          NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dc_observation_status` (
  `id`             TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `weather_label`  VARCHAR(64)  NULL,
  `seeing`         VARCHAR(64)  NULL,
  `transparency`   VARCHAR(32)  NULL,
  `moon_phase`     VARCHAR(64)  NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dc_observation_daily` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `record_date`   DATE         NOT NULL,
  `observations`  INT          NOT NULL DEFAULT 0,
  `quality`       VARCHAR(32)  NULL,
  `weather`       VARCHAR(32)  NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_date` (`record_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;