-- ----------------------------
-- 城市公司档案模块：演示数据
--
-- 目标库 hugray-account（库名含连字符，任何引用都必须用反引号）。
-- 依赖：hugray_account_city_20261012.sql 建的两张表 + hugray_account_user_demo_20261012.sql 造的会员。
--
-- 【§1 为什么必须把 account_user.city 的值造全】
-- 会员列表的「所属城市」筛选改成了取 account_city 的下拉选项、按市名精确匹配，
-- 城市表里没有的市名等于筛不出人。故 §1 先按 `account_user` 的 distinct city 建全，
-- 再加几条「有城市但暂无会员」的（给城市管理页看，也用于验证「档案引用的城市不允许删除」）。
--
-- 【§2 的边界样本（刻意为之，别当脏数据删）】
--   33140003 状态禁用            -> 档案自己的 status='1'
--   33140004 关联会员被降级       -> 建完档案后把该会员从 L4 改回 L1，
--                                  档案列表/详情必须用 LEFT JOIN，否则这条会整体消失
--   33140006 城市被禁用但档案正常  -> 城市 status='1' 不影响已建档案；也因此该城市不允许删除
--   131 拉萨 del_flag='2'        -> 逻辑删除的城市不出现在列表与下拉里
--
-- 可重复执行（先按号段清掉本脚本造的数据，再插入）。
-- ----------------------------

USE `hugray-account`;

-- ---------- 1. 城市主数据 ----------
-- 先清本脚本的号段（城市走 100+，档案走 33140000+），不用无 WHERE 的删除。
DELETE FROM `account_city` WHERE `id` BETWEEN 100 AND 199;
ALTER TABLE `account_city` AUTO_INCREMENT = 100;

-- 1.1 会员表里已有的城市：逐字对齐 account_user.city，改一个错别字就会让筛选漏人
INSERT INTO `account_city`
(`id`, `province`, `city_name`, `sort_no`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(100, '上海市',   '上海',   10, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(101, '甘肃省',   '兰州',   20, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(102, '北京市',   '北京',   30, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(103, '江苏省',   '南京',   40, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(104, '江西省',   '南昌',   50, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(105, '福建省',   '厦门',   60, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(106, '安徽省',   '合肥',   70, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(107, '黑龙江省', '哈尔滨', 80, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(108, '天津市',   '天津',   90, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(109, '山西省',   '太原',  100, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(110, '广东省',   '广州',  110, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(111, '四川省',   '成都',  120, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(112, '云南省',   '昆明',  130, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(113, '浙江省',   '杭州',  140, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(114, '湖北省',   '武汉',  150, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(115, '辽宁省',   '沈阳',  160, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(116, '广东省',   '深圳',  170, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(117, '河北省',   '石家庄', 180, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(118, '福建省',   '福州',  190, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(119, '江苏省',   '苏州',  200, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(120, '陕西省',   '西安',  210, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(121, '贵州省',   '贵阳',  220, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(122, '重庆市',   '重庆',  230, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(123, '湖南省',   '长沙',  240, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),
(124, '山东省',   '青岛',  250, '0', '0', 'admin', sysdate(), '', NULL, '存量会员城市'),

-- 1.2 暂无会员的城市：城市管理页要有内容可看，也用来验证「档案引用的城市不允许删除」
(125, '河南省',   '郑州',  260, '0', '0', 'admin', sysdate(), '', NULL, '暂无会员'),
(126, '辽宁省',   '大连',  270, '0', '0', 'admin', sysdate(), '', NULL, '暂无会员'),

-- 1.3 停用城市：不出现在任何下拉里（但已建的档案不受影响，见 33140006）
(127, '青海省',   '西宁',  280, '1', '0', 'admin', sysdate(), '', NULL, '停用示例：下拉不出现'),

-- 1.4 逻辑删除的城市：列表与下拉都不出现
(128, '西藏自治区', '拉萨', 290, '0', '2', 'admin', sysdate(), '', NULL, '逻辑删除示例');

-- ---------- 2. 城市公司档案 ----------
DELETE FROM `account_city_company` WHERE `id` BETWEEN 33140000 AND 33140099;
ALTER TABLE `account_city_company` AUTO_INCREMENT = 33140000;

-- 2.1 常规档案：城市与「在该城市的 L4 会员」一一对上
INSERT INTO `account_city_company`
(`id`, `city_id`, `company_name`, `user_id`, `contact_name`, `contact_phone`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(33140000, 113, '杭州晨禾数字科技有限公司',   104, '陈晨', '13800000104', '0', '0', 'admin', sysdate(), '', NULL, '绑定的会员当前为「注销中」，档案不受影响'),
(33140001, 123, '长沙鸿运商贸有限公司',       113, '韩雪', '13800000113', '0', '0', 'admin', sysdate(), '', NULL, '绑定的会员当前为「已注销」，档案不受影响'),
(33140002, 104, '南昌赣江供应链有限公司',     118, '施婷', '13800000118', '0', '0', 'admin', sysdate(), '', NULL, ''),
-- 2.2 禁用档案：列表能筛出 status='1'
(33140003, 115, '沈阳盛京优选商贸有限公司',   123, '陈静', '13800000123', '1', '0', 'admin', sysdate(), '', NULL, '禁用示例'),
-- 2.3 同一会员关联两个城市的公司：1:1 约束的是城市，不是会员
(33140004, 111, '成都天府云链科技有限公司',   104, '陈晨', '13800000104', '0', '0', 'admin', sysdate(), '', NULL, '同一会员服务两个城市'),
-- 2.4 城市被禁用，档案正常：城市停用不回滚已建档案，故该城市也不允许删除
(33140006, 127, '西宁高原商贸有限公司',       118, '施婷', '13800000118', '0', '0', 'admin', sysdate(), '', NULL, '城市已停用，档案仍正常展示');

-- 2.5 「关联会员后来被降级」的边界样本：
-- 建档案时该会员必须是 L4（服务层会校验），故先临时提级，建档后还原。
-- 档案列表与详情必须对会员表用 LEFT JOIN，否则这条档案会从列表里整体消失。
UPDATE `account_user` SET `system_level` = 'L4' WHERE `user_id` = 101;
INSERT INTO `account_city_company`
(`id`, `city_id`, `company_name`, `user_id`, `contact_name`, `contact_phone`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(33140005, 100, '上海沪通数字科技有限公司', 101, '李娜', '13800000101', '0', '0', 'admin', sysdate(), '', NULL, '建完档案后该会员被降级为 L1');
UPDATE `account_user` SET `system_level` = 'L1' WHERE `user_id` = 101;

-- ---------- 2.6 补齐更新时间 ----------
-- 列表页有「更新时间」列，上面靠 create_time 默认值插入的行 update_time 仍是 NULL，会显示空。
-- 演示数据就当「建后原地没改过」，令两者相等。
UPDATE `account_city`         SET `update_by` = `create_by`, `update_time` = `create_time` WHERE `update_time` IS NULL;
UPDATE `account_city_company` SET `update_by` = `create_by`, `update_time` = `create_time` WHERE `update_time` IS NULL;

-- ---------- 3. 结果核对 ----------
SELECT '城市' AS 项, COUNT(*) AS 值 FROM `account_city` WHERE `del_flag` = '0'
UNION ALL SELECT '城市（正常）',  COUNT(*) FROM `account_city` WHERE `del_flag` = '0' AND `status` = '0'
UNION ALL SELECT '城市（停用）',  COUNT(*) FROM `account_city` WHERE `del_flag` = '0' AND `status` = '1'
UNION ALL SELECT '公司档案',      COUNT(*) FROM `account_city_company` WHERE `del_flag` = '0';

-- 3.1 会员列表筛选口径：每个会员的城市都能在城市表里找到「正常」城市（应为 0 行，否则筛选会漏人）
SELECT u.`city` AS 会员表里筛不出来的城市, COUNT(*) AS 会员数
FROM `account_user` u
WHERE u.`del_flag` = '0' AND u.`city` IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `account_city` c
                  WHERE c.`city_name` = u.`city` AND c.`del_flag` = '0' AND c.`status` = '0')
GROUP BY u.`city`;

-- 3.2 一城一档：同城市未删除档案数 > 1 即为违规（应为 0 行）
SELECT c.`city_name`, COUNT(*) AS 档案数
FROM `account_city_company` cc JOIN `account_city` c ON c.`id` = cc.`city_id`
WHERE cc.`del_flag` = '0'
GROUP BY c.`id`, c.`city_name` HAVING COUNT(*) > 1;

-- 3.3 边界样本自检：降级样本的会员必须已还原为 L1（若为 L4 说明 2.5 的还原语句没跑到）
SELECT `user_id`, `system_level`, `status` FROM `account_user` WHERE `user_id` IN (101, 104) ORDER BY `user_id`;

-- 3.4 档案与城市/会员的联表结果（列表页取的就是这几列）
SELECT cc.`id` AS 档案编号, c.`province` AS 省, c.`city_name` AS 市, cc.`company_name` AS 公司名称,
       cc.`user_id` AS 关联用户账号, u.`phone` AS 会员手机, u.`system_level` AS 会员等级,
       cc.`contact_name` AS 联系人, cc.`contact_phone` AS 联系电话, cc.`status` AS 状态, cc.`update_time` AS 更新时间
FROM `account_city_company` cc
LEFT JOIN `account_city` c ON c.`id` = cc.`city_id` AND c.`del_flag` = '0'
LEFT JOIN `account_user` u ON u.`user_id` = cc.`user_id` AND u.`del_flag` = '0'
WHERE cc.`del_flag` = '0'
ORDER BY cc.`id`;