-- 稻城亚丁 · 初始化表结构（表前缀 dc_ 由应用 DB_PREFIX 配置）
-- 须先选中数据库，否则会报 1046 - No database selected
CREATE DATABASE IF NOT EXISTS `daocheng` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `daocheng`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `dc_work_like`;
DROP TABLE IF EXISTS `dc_work_category`;
DROP TABLE IF EXISTS `dc_work_image`;
DROP TABLE IF EXISTS `dc_work`;
DROP TABLE IF EXISTS `dc_mp_user`;
DROP TABLE IF EXISTS `dc_sys_role_menu`;
DROP TABLE IF EXISTS `dc_sys_menu`;
DROP TABLE IF EXISTS `dc_sys_admin`;
DROP TABLE IF EXISTS `dc_sys_role`;
DROP TABLE IF EXISTS `dc_banner`;
DROP TABLE IF EXISTS `dc_map_spot`;

CREATE TABLE `dc_sys_role` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `code` varchar(64) NOT NULL DEFAULT '',
  `remark` varchar(255) DEFAULT '',
  `status` tinyint NOT NULL DEFAULT 1,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_sys_admin` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `password` varchar(255) NOT NULL,
  `nickname` varchar(64) DEFAULT '',
  `role_id` int unsigned NOT NULL DEFAULT 0,
  `status` tinyint NOT NULL DEFAULT 1,
  `api_token` varchar(64) DEFAULT '',
  `token_expire` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_sys_menu` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int unsigned NOT NULL DEFAULT 0,
  `name` varchar(64) NOT NULL,
  `type` tinyint NOT NULL DEFAULT 1 COMMENT '1目录 2菜单 3按钮',
  `path` varchar(255) DEFAULT '',
  `component` varchar(255) DEFAULT '',
  `perms` varchar(128) DEFAULT '',
  `icon` varchar(64) DEFAULT '',
  `sort` int NOT NULL DEFAULT 0,
  `status` tinyint NOT NULL DEFAULT 1,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_sys_role_menu` (
  `role_id` int unsigned NOT NULL,
  `menu_id` int unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_mp_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `client_id` varchar(64) NOT NULL,
  `openid` varchar(64) NOT NULL DEFAULT '',
  `unionid` varchar(64) NOT NULL DEFAULT '',
  `session_key` varchar(128) NOT NULL DEFAULT '',
  `nickname` varchar(64) NOT NULL DEFAULT '游客',
  `avatar` varchar(512) DEFAULT '',
  `bio` varchar(200) DEFAULT '',
  `gender` tinyint NOT NULL DEFAULT 0,
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号账号',
  `pwd_hash` varchar(255) NOT NULL DEFAULT '' COMMENT '手机号登录密码 bcrypt',
  `status` tinyint NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
  `api_token` varchar(64) DEFAULT '',
  `token_expire` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_client` (`client_id`),
  KEY `idx_token` (`api_token`),
  KEY `idx_openid` (`openid`),
  UNIQUE KEY `uk_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_work` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL DEFAULT 0,
  `title` varchar(32) NOT NULL DEFAULT '',
  `description` varchar(500) NOT NULL DEFAULT '',
  `location` varchar(128) NOT NULL DEFAULT '',
  `shoot_date` date DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'pending',
  `reject_reason` varchar(500) NOT NULL DEFAULT '' COMMENT '审核拒绝原因',
  `sort_order` int NOT NULL DEFAULT 0,
  `recommended` tinyint NOT NULL DEFAULT 0,
  `views` int unsigned NOT NULL DEFAULT 0,
  `likes_count` int unsigned NOT NULL DEFAULT 0,
  `comments_count` int unsigned NOT NULL DEFAULT 0,
  `cover_image` varchar(512) NOT NULL DEFAULT '',
  `camera` varchar(128) DEFAULT '',
  `lens` varchar(128) DEFAULT '',
  `exif_settings` varchar(255) DEFAULT '',
  `publish_date` date DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status_sort` (`status`,`sort_order`),
  KEY `idx_user` (`user_id`),
  KEY `idx_create` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_work_image` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int unsigned NOT NULL,
  `url` varchar(512) NOT NULL,
  `sort` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_work` (`work_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_work_category` (
  `work_id` int unsigned NOT NULL,
  `category` varchar(32) NOT NULL,
  PRIMARY KEY (`work_id`,`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_work_like` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_wu` (`work_id`,`user_id`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_banner` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `image_url` varchar(512) NOT NULL,
  `title` varchar(128) NOT NULL DEFAULT '',
  `subtitle` varchar(255) NOT NULL DEFAULT '',
  `sort` int NOT NULL DEFAULT 0,
  `status` tinyint NOT NULL DEFAULT 1,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dc_map_spot` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `lat` decimal(10,6) NOT NULL,
  `lng` decimal(10,6) NOT NULL,
  `description` varchar(500) DEFAULT '',
  `thumbnail` varchar(512) DEFAULT '',
  `photos_count` int unsigned NOT NULL DEFAULT 0,
  `pin_left_pct` decimal(6,3) NOT NULL DEFAULT 50.000,
  `pin_top_pct` decimal(6,3) NOT NULL DEFAULT 50.000,
  `sort` int NOT NULL DEFAULT 0,
  `status` tinyint NOT NULL DEFAULT 1,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- 超级管理员角色
INSERT INTO `dc_sys_role` (`id`, `name`, `code`, `remark`, `status`, `create_time`, `update_time`) VALUES
(1, '超级管理员', 'super', '全部权限', 1, NOW(), NOW());

-- 菜单（目录/菜单/按钮）
INSERT INTO `dc_sys_menu` (`id`, `parent_id`, `name`, `type`, `path`, `component`, `perms`, `icon`, `sort`, `status`, `create_time`, `update_time`) VALUES
(1, 0, '系统管理', 1, '/system', NULL, '', 'setting', 20, 1, NOW(), NOW()),
(2, 1, '管理员', 2, '/system/admin', 'system/admin/index', 'system:admin:list', 'user', 1, 1, NOW(), NOW()),
(3, 1, '角色管理', 2, '/system/role', 'system/role/index', 'system:role:list', 'team', 2, 1, NOW(), NOW()),
(4, 1, '菜单管理', 2, '/system/menu', 'system/menu/index', 'system:menu:list', 'menu', 3, 0, NOW(), NOW()),
(10, 0, '内容管理', 1, '/content', NULL, '', 'picture', 10, 1, NOW(), NOW()),
(11, 10, '轮播图', 2, '/content/banner', 'content/banner/index', 'content:banner:list', 'image', 1, 1, NOW(), NOW()),
(12, 10, '作品审核', 2, '/content/work', 'content/work/index', 'content:work:audit', 'audit', 2, 1, NOW(), NOW()),
(13, 10, '地图锚点', 2, '/content/map', 'content/map/index', 'content:map:list', 'map', 3, 1, NOW(), NOW()),
(100, 2, '新增管理员', 3, '', NULL, 'system:admin:add', '', 1, 1, NOW(), NOW()),
(101, 2, '编辑管理员', 3, '', NULL, 'system:admin:edit', '', 2, 1, NOW(), NOW()),
(102, 3, '分配权限', 3, '', NULL, 'system:role:perm', '', 9, 1, NOW(), NOW()),
(5, 1, '用户管理', 2, '/system/mp-user', 'system/mp-user/index', 'system:mp:list', 'avatar', 4, 1, NOW(), NOW()),
(103, 5, '编辑用户', 3, '', NULL, 'system:mp:edit', '', 1, 1, NOW(), NOW());

INSERT INTO `dc_sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `dc_sys_menu`;

-- 默认管理员 admin / admin123
INSERT INTO `dc_sys_admin` (`id`, `username`, `password`, `nickname`, `role_id`, `status`, `create_time`, `update_time`) VALUES
(1, 'admin', '$2y$12$M3fo.oOKdwX4KzIn148MZuNo71F3YnRjTDFv/w.6x7lpSx.cjIpNS', '管理员', 1, 1, NOW(), NOW());

-- 轮播（路径与小程序 static 一致，前端也可拼接域名）
INSERT INTO `dc_banner` (`image_url`, `title`, `subtitle`, `sort`, `status`, `create_time`, `update_time`) VALUES
('/static/home-top-banner.png', '云雾山海', '四川·稻城亚丁', 1, 1, NOW(), NOW()),
('/static/home-top-banner-2.png', '溪谷漫步', '草甸流水，雪山为幕', 2, 1, NOW(), NOW()),
('/static/home-top-banner-3.png', '碧蓝海子', '雪峰倒映，静坐湖岸', 3, 1, NOW(), NOW());

-- 地图锚点（百分比与小程序 map.vue 大致对应）
INSERT INTO `dc_map_spot` (`name`, `lat`, `lng`, `description`, `thumbnail`, `photos_count`, `pin_left_pct`, `pin_top_pct`, `sort`, `status`, `create_time`, `update_time`) VALUES
('亚丁景区', 29.050000, 100.350000, '三大神山所在地，壮美雪峰与湖泊', '/static/photo-xiannairi-xuefeng.png', 156, 52.000, 38.000, 1, 1, NOW(), NOW()),
('牛奶海', 29.040000, 100.370000, '碧蓝色高山湖泊，犹如一颗宝石', '/static/photo-niunaihai.png', 89, 48.000, 42.000, 2, 1, NOW(), NOW()),
('桑堆草原', 29.120000, 100.250000, '辽阔草甸与牧群', '/static/photo-sangdui-caoyuan.png', 64, 35.000, 55.000, 3, 1, NOW(), NOW());

-- 演示用户（小程序端「山行者」可后续绑定 client_id）
INSERT INTO `dc_mp_user` (`id`, `client_id`, `openid`, `unionid`, `session_key`, `nickname`, `avatar`, `bio`, `gender`, `status`, `create_time`, `update_time`) VALUES
(1, 'demo-client-001', '', '', '', '山行者', '', '热爱高原风光', 0, 1, NOW(), NOW());

-- 已通过作品（节选，与 gallery 演示数据对齐）
INSERT INTO `dc_work` (`id`, `user_id`, `title`, `description`, `location`, `shoot_date`, `status`, `sort_order`, `recommended`, `views`, `likes_count`, `comments_count`, `cover_image`, `camera`, `lens`, `exif_settings`, `publish_date`, `create_time`, `update_time`) VALUES
(1, 1, '仙乃日雪峰', '稻城亚丁自然风光摄影作品。', '亚丁景区', '2026-03-10', 'approved', 1, 1, 1250, 156, 23, '/static/photo-xiannairi-xuefeng.png', 'Sony A7R4', 'FE 24-70mm', 'ISO 100 | f/8 | 1/250s', '2026-03-18', NOW(), NOW()),
(2, 1, '牛奶海', '牛奶海（俄绒措）镶嵌在央迈勇神山脚下的高山湖泊，湖水澄澈如碧玉。', '四川省甘孜州稻城县亚丁景区·牛奶海', '2026-03-08', 'approved', 2, 1, 2100, 528, 45, '/static/photo-niunaihai.png', 'Sony A7R4', 'FE 24-70mm F2.8 GM', 'ISO 100 | f/11 | 1/320s', '2026-03-18', NOW(), NOW()),
(3, 1, '桑堆草原', '稻城亚丁自然风光摄影作品。', '稻城县', NULL, 'approved', 3, 0, 890, 189, 31, '/static/photo-sangdui-caoyuan.png', '—', '—', '—', '2026-03-18', NOW(), NOW());

INSERT INTO `dc_work_category` (`work_id`, `category`) VALUES
(1, 'mountain'), (2, 'lake'), (3, 'meadow');

INSERT INTO `dc_work_image` (`work_id`, `url`, `sort`) VALUES
(1, '/static/photo-xiannairi-xuefeng.png', 0),
(2, '/static/photo-niunaihai.png', 0),
(3, '/static/photo-sangdui-caoyuan.png', 0);

-- 待审核示例（管理后台演示）
INSERT INTO `dc_work` (`id`, `user_id`, `title`, `description`, `location`, `shoot_date`, `status`, `sort_order`, `recommended`, `views`, `likes_count`, `comments_count`, `cover_image`, `publish_date`, `create_time`, `update_time`) VALUES
(100, 1, '五色海晨曦', '待审核作品示例', '亚丁景区', '2026-03-26', 'pending', 0, 0, 0, 0, 0, '/static/photo-niunaihai.png', NULL, NOW(), NOW()),
(101, 1, '草原牧歌', '待审核作品示例', '桑堆草原', NULL, 'pending', 0, 0, 0, 0, 0, '/static/photo-sangdui-caoyuan.png', NULL, NOW(), NOW());

INSERT INTO `dc_work_category` (`work_id`, `category`) VALUES (100, 'lake'), (101, 'meadow');

INSERT INTO `dc_work_image` (`work_id`, `url`, `sort`) VALUES
(100, '/static/photo-niunaihai.png', 0),
(101, '/static/photo-sangdui-caoyuan.png', 0);