-- ----------------------------
-- 城市公司档案模块：城市主数据 + 城市公司档案
--
-- 目标库 hugray-account（库名含连字符，任何引用都必须用反引号）。
-- 两张表都是新建，故用 CREATE TABLE IF NOT EXISTS，脚本可重复执行。
-- 演示数据另见 hugray_city_company_demo_20261012.sql。
--
-- 【与 account_user.city 的关系——改城市数据前必读】
-- 会员表的 city 存的是城市名称文本，本次只把「城市筛选下拉的数据源」换成 account_city，
-- 会员表不动、存量数据不迁移。因此 account_city.city_name 必须与 account_user.city 的存量文本
-- 逐字对齐（存「杭州」而不是「杭州市」），否则会员列表按城市筛选会筛不出人。
-- 城市名按「全国唯一」处理：会员表只存市名，同名的两个城市会让筛选口径产生歧义。
--
-- 【枚举取值】沿用本库约定，直接存值、不建 sys_dict_type，前端维护同名常量：
--   状态 status        ：0正常  1禁用
--   删除标志 del_flag  ：0存在  2删除
-- ----------------------------

USE `hugray-account`;

-- ---------- 1. account_city：城市主数据 ----------
-- province 只作展示与录入（新增时省市二级联动选择），筛选与业务关联一律走 city_name。
-- 不加 UNIQUE(city_name)：逻辑删除的残留行会永久占用唯一键，「同名至多一条」只能应用层保证
-- （与 account_certification、account_user.username 同一个坑）。
CREATE TABLE IF NOT EXISTS `account_city` (
  `id`          bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '城市编号',
  `province`    varchar(32)  NOT NULL DEFAULT ''      COMMENT '所属省份（二级联动选择所得，存全称，如「广东省」）',
  `city_name`   varchar(32)  NOT NULL                 COMMENT '城市名称（与 account_user.city 的存量文本逐字对齐）',
  `sort_no`     int(11)      DEFAULT 0                COMMENT '排序（升序，越小越靠前）',
  `status`      char(1)      DEFAULT '0'              COMMENT '状态（0正常 1禁用）',
  `del_flag`    char(1)      DEFAULT '0'              COMMENT '删除标志（0存在 2删除）',
  `create_by`   varchar(64)  DEFAULT ''               COMMENT '创建者',
  `create_time` datetime     DEFAULT NULL             COMMENT '创建时间',
  `update_by`   varchar(64)  DEFAULT ''               COMMENT '更新者',
  `update_time` datetime     DEFAULT NULL             COMMENT '更新时间',
  `remark`      varchar(500) DEFAULT NULL             COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_account_city_name` (`city_name`),
  KEY `idx_account_city_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COMMENT='城市主数据表（会员列表城市筛选与城市公司档案共用）';

-- ---------- 2. account_city_company：城市公司档案 ----------
-- 一个城市至多一条档案（1:1）。同城市重复建档由服务层拦，不加 UNIQUE(city_id)：
-- 同上，逻辑删除的残留行会占死唯一键。
-- 档案编号是自增 id，起始 33140000 对齐原型里的 8 位编号观感（业务上无特殊含义）。
-- user_id 指向被绑定的会员（限 L4，见服务层校验）；联系人/手机号是档案自己的字段，
-- 不冗余到 account_certification——同一家公司换联系人时不该动会员的认证资料。
CREATE TABLE IF NOT EXISTS `account_city_company` (
  `id`            bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '档案编号',
  `city_id`       bigint(20)   NOT NULL                COMMENT '所属城市（account_city.id）',
  `company_name`  varchar(128) NOT NULL                COMMENT '公司名称',
  `user_id`       bigint(20)   NOT NULL                COMMENT '关联用户账号（account_user.user_id，限 L4 会员）',
  `contact_name`  varchar(64)  NOT NULL                COMMENT '联系人',
  `contact_phone` varchar(20)  NOT NULL                COMMENT '联系电话',
  `status`        char(1)      DEFAULT '0'             COMMENT '状态（0正常 1禁用）',
  `del_flag`      char(1)      DEFAULT '0'             COMMENT '删除标志（0存在 2删除）',
  `create_by`     varchar(64)  DEFAULT ''              COMMENT '创建者',
  `create_time`   datetime     DEFAULT NULL            COMMENT '创建时间',
  `update_by`     varchar(64)  DEFAULT ''              COMMENT '更新者',
  `update_time`   datetime     DEFAULT NULL            COMMENT '更新时间',
  `remark`        varchar(500) DEFAULT NULL            COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_city_company_city` (`city_id`),
  KEY `idx_city_company_user` (`user_id`),
  KEY `idx_city_company_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=33140000 DEFAULT CHARSET=utf8mb4 COMMENT='城市公司档案表（一个城市一条）';

-- ---------- 3. 结果核对 ----------
SELECT `table_name`, `table_comment` FROM `information_schema`.`tables`
WHERE `table_schema` = 'hugray-account' AND `table_name` IN ('account_city', 'account_city_company');
SELECT `table_name`, `column_name`, `column_type`, `column_comment`
FROM `information_schema`.`columns`
WHERE `table_schema` = 'hugray-account' AND `table_name` IN ('account_city', 'account_city_company')
ORDER BY `table_name`, `ordinal_position`;