-- ----------------------------
-- 用户模块：会员主表扩充 + 4 张明细表
--
-- 目标库 hugray-account（库名含连字符，任何引用都必须用反引号）。
-- 与 hugray_account_20261009.sql 的区别：该脚本是首建，用 DROP + CREATE；
-- 本脚本是扩充，account_user 里已有数据，故一律用 ALTER，绝不 DROP。
--
-- 【幂等说明】
-- MySQL 的 ALTER TABLE 不支持 ADD COLUMN / ADD INDEX IF NOT EXISTS，
-- 重复执行会因 Duplicate column name / Duplicate key name 中断。
-- 这里用两个临时存储过程做存在性判断，脚本可安全重复执行，
-- 末尾会把过程删掉，不在库里留痕。
--
-- 【枚举取值】本模块所有枚举均直接存值，不建 sys_dict_type：
--   系统等级 system_level  ：C < L1 < L2 < L3 < L4（从低到高）
--   主体类型 subject_type  ：1个人  2个体工商户  3企业
--   认证状态 auth_status   ：0未认证 1审核中 2已认证 3认证失败
--   账户状态 status        ：0正常  1禁用  2注销中  3已注销
--   是否默认 is_default    ：0否  1是
-- 前端在页面内维护同名常量，不入字典表。
-- ----------------------------

USE `hugray-account`;

-- ---------- 幂等辅助过程 ----------
DROP PROCEDURE IF EXISTS `hugray_add_column`;
DROP PROCEDURE IF EXISTS `hugray_add_index`;

DELIMITER $$
CREATE PROCEDURE `hugray_add_column`(IN p_table VARCHAR(64), IN p_column VARCHAR(64), IN p_definition TEXT)
BEGIN
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME   = p_table
                   AND COLUMN_NAME  = p_column) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END$$

CREATE PROCEDURE `hugray_add_index`(IN p_table VARCHAR(64), IN p_index VARCHAR(64), IN p_columns TEXT)
BEGIN
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME   = p_table
                   AND INDEX_NAME   = p_index) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX `', p_index, '` (', p_columns, ')');
    PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

-- ---------- 1. account_user 扩充：用户画像 ----------
CALL `hugray_add_column`('account_user', 'system_level', "varchar(8) DEFAULT 'C' COMMENT '系统等级（C<L1<L2<L3<L4，从低到高）'");
-- 注意：L1 是两个字符，此处必须是 varchar，用 char(1) 会被截断成 L。
CALL `hugray_add_column`('account_user', 'subject_type', "char(1)    DEFAULT '1' COMMENT '主体类型（1个人 2个体工商户 3企业）'");
CALL `hugray_add_column`('account_user', 'auth_status',  "char(1)    DEFAULT '0' COMMENT '认证状态（0未认证 1审核中 2已认证 3认证失败）'");
CALL `hugray_add_column`('account_user', 'city',         "varchar(64) DEFAULT NULL COMMENT '所属城市（暂存城市名称文本，待「城市公司档案」模块建表后对齐编码）'");

-- ---------- 2. account_user 扩充：推荐人 / 供货人关系 ----------
-- 只存 ID，推荐人/供货人的手机号与等级由详情查询自连接 account_user 得到，不冗余存储。
CALL `hugray_add_column`('account_user', 'referrer_id',        "bigint(20) DEFAULT NULL COMMENT '推荐人会员ID（自连接 account_user.user_id）'");
CALL `hugray_add_column`('account_user', 'referrer_bind_time', "datetime   DEFAULT NULL COMMENT '推荐关系绑定时间'");
CALL `hugray_add_column`('account_user', 'supplier_id',        "bigint(20) DEFAULT NULL COMMENT '供货人会员ID（自连接 account_user.user_id）'");
CALL `hugray_add_column`('account_user', 'supplier_bind_time', "datetime   DEFAULT NULL COMMENT '供货关系绑定时间'");

-- ---------- 3. account_user 扩充：关联业务统计（先建列存 0，待订单/库存模块开发后回填） ----------
-- 金额一律 decimal，不用 float/double；sell_profit_total 允许负数。
CALL `hugray_add_column`('account_user', 'buy_order_count',      "int(11)       NOT NULL DEFAULT 0    COMMENT '购买订单数（待回填）'");
CALL `hugray_add_column`('account_user', 'buy_amount_total',     "decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '购买金额合计（待回填）'");
CALL `hugray_add_column`('account_user', 'sell_order_count',     "int(11)       NOT NULL DEFAULT 0    COMMENT '销售订单数（待回填）'");
CALL `hugray_add_column`('account_user', 'sell_amount_total',    "decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '销售金额合计（待回填）'");
CALL `hugray_add_column`('account_user', 'purchase_order_count', "int(11)       NOT NULL DEFAULT 0    COMMENT '采购订单数（待回填）'");
CALL `hugray_add_column`('account_user', 'purchase_amount_total',"decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '采购金额合计（待回填）'");
CALL `hugray_add_column`('account_user', 'sell_profit_total',    "decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '销售利润合计（可为负，待回填）'");
CALL `hugray_add_column`('account_user', 'virtual_stock_qty',    "bigint(20)    NOT NULL DEFAULT 0    COMMENT '虚拟库存数量（待回填）'");
CALL `hugray_add_column`('account_user', 'virtual_stock_value',  "decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '虚拟库存货值（待回填）'");

-- ---------- 4. account_user 扩充：status 语义变更 + 索引 ----------
-- 账户状态由 2 值扩为 4 值（0正常 1禁用 2注销中 3已注销），char(1) 类型不变，仅改注释。
-- 旧页面 views/user/account/index.vue 用 sys_normal_disable 字典渲染 status，改后对 2/3 会显示异常；
-- 该页已无菜单入口，本次不动。
ALTER TABLE `account_user`
  MODIFY COLUMN `status` char(1) DEFAULT '0' COMMENT '账户状态（0正常 1禁用 2注销中 3已注销）';

-- 对应列表页的筛选条件建索引。user_id 走主键，不重复建。
CALL `hugray_add_index`('account_user', 'idx_account_user_level',       '`system_level`');
CALL `hugray_add_index`('account_user', 'idx_account_user_auth',        '`auth_status`');
CALL `hugray_add_index`('account_user', 'idx_account_user_subject',     '`subject_type`');
CALL `hugray_add_index`('account_user', 'idx_account_user_city',        '`city`');
CALL `hugray_add_index`('account_user', 'idx_account_user_status',      '`status`');
CALL `hugray_add_index`('account_user', 'idx_account_user_create_time', '`create_time`');
CALL `hugray_add_index`('account_user', 'idx_account_user_referrer',    '`referrer_id`');
CALL `hugray_add_index`('account_user', 'idx_account_user_supplier',    '`supplier_id`');

-- ---------- 5. account_certification：主体资料（1:1） ----------
-- 细节：不加 UNIQUE(user_id)。「主体认证审核」（菜单 2202）意味着存在审核流转与驳回重提，
-- 未来可能保留历史行；且若改用逻辑删除，唯一约束会被残留行永久占用。
-- account_user.username 刻意不加唯一索引就是同一个坑，见 hugray_account_20261009.sql 的注释。
CREATE TABLE IF NOT EXISTS `account_certification` (
  `id`                   bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id`              bigint(20)   NOT NULL                COMMENT '会员ID（account_user.user_id）',
  `real_name`            varchar(64)  DEFAULT NULL            COMMENT '真实姓名',
  `id_card`              varchar(32)  DEFAULT NULL            COMMENT '身份证号',
  `company_name`         varchar(128) DEFAULT NULL            COMMENT '企业/个体工商户名称',
  `credit_code`          varchar(64)  DEFAULT NULL            COMMENT '统一社会信用代码',
  `legal_person`         varchar(64)  DEFAULT NULL            COMMENT '法定代表人',
  `registered_region`    varchar(128) DEFAULT NULL            COMMENT '注册地区',
  `company_address`      varchar(255) DEFAULT NULL            COMMENT '企业地址',
  `register_date`        date         DEFAULT NULL            COMMENT '注册日期',
  `license_valid_period` varchar(32)  DEFAULT NULL            COMMENT '营业执照有效期（存文本，如 2024-01-01~2034-01-01 或 长期）',
  `license_image`        varchar(500) DEFAULT NULL            COMMENT '营业执照图片地址',
  `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_cert_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COMMENT='会员主体认证资料表';

-- CREATE TABLE IF NOT EXISTS 对已存在的表不会补列，故显式补一次（幂等）。
CALL `hugray_add_column`('account_certification', 'del_flag', "char(1) DEFAULT '0' COMMENT '删除标志（0存在 2删除）'");

-- ---------- 6. account_address：收货地址（1:N） ----------
-- is_default 的「每用户至多一个」由应用层在事务内保证（先把同用户其他行置 0），
-- MySQL 没有部分唯一索引，无法在 DDL 层约束。
CREATE TABLE IF NOT EXISTS `account_address` (
  `id`              bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id`         bigint(20)   NOT NULL                COMMENT '会员ID（account_user.user_id）',
  `consignee`       varchar(64)  DEFAULT NULL            COMMENT '收货人',
  `consignee_phone` varchar(20)  DEFAULT NULL            COMMENT '收货人电话',
  `address`         varchar(255) DEFAULT NULL            COMMENT '收货地址',
  `is_default`      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_address_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COMMENT='会员收货地址表';

CALL `hugray_add_column`('account_address', 'del_flag', "char(1) DEFAULT '0' COMMENT '删除标志（0存在 2删除）'");

-- ---------- 7. account_agreement：签署协议（1:N） ----------
CREATE TABLE IF NOT EXISTS `account_agreement` (
  `id`             bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id`        bigint(20)   NOT NULL                COMMENT '会员ID（account_user.user_id）',
  `agreement_name` varchar(128) DEFAULT NULL            COMMENT '协议名称',
  -- 正文用 text 不用 varchar：整篇合同远超 varchar 上限；text 列不允许 DEFAULT，故不写默认值
  `agreement_content` text                              COMMENT '协议正文（签署时的内容快照）',
  `sign_time`      datetime     DEFAULT NULL            COMMENT '签署时间',
  `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_agreement_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COMMENT='会员签署协议记录表';

CALL `hugray_add_column`('account_agreement', 'del_flag', "char(1) DEFAULT '0' COMMENT '删除标志（0存在 2删除）'");
-- 兼容已按旧版脚本建过表的库：CREATE 加了列，这里再兜一次
CALL `hugray_add_column`('account_agreement', 'agreement_content', "text COMMENT '协议正文（签署时的内容快照）'");

-- ---------- 8. account_warehouse：云仓（1:N） ----------
CREATE TABLE IF NOT EXISTS `account_warehouse` (
  `id`             bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id`        bigint(20)   NOT NULL                COMMENT '会员ID（account_user.user_id）',
  `product_name`   varchar(128) DEFAULT NULL            COMMENT '商品名称',
  `stock_qty`      bigint(20)   NOT NULL DEFAULT 0      COMMENT '库存数量',
  `min_stock_qty`  bigint(20)   NOT NULL DEFAULT 0      COMMENT '预警库存',
  `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_warehouse_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COMMENT='会员云仓库存表';

CALL `hugray_add_column`('account_warehouse', 'del_flag', "char(1) DEFAULT '0' COMMENT '删除标志（0存在 2删除）'");

-- ---------- 9. account_certification_apply：主体认证申请审核（1:N） ----------
--
-- 与 account_certification 的分工：
--   account_certification       存"当前生效"的认证资料，一个会员一条，详情页读它；
--   account_certification_apply 存"每次提交的申请"，驳回后重提会多一条，审核历史全留。
-- 因此申请表把资料整份冗余快照下来，不引用 account_certification——
-- 否则资料一改，历史申请上展示的就跟着变了，审核记录失去意义。
--
-- 审核状态与 account_user.auth_status 的联动（由服务层保证，不在本脚本）：
--   提交申请 -> auth_status='1' 审核中
--   审核通过 -> auth_status='2' 已认证，并把申请资料 upsert 进 account_certification
--   审核驳回 -> auth_status='3' 认证失败
CREATE TABLE IF NOT EXISTS `account_certification_apply` (
  `id`                   bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '主键，同时作为申请号',
  `user_id`              bigint(20)   NOT NULL                COMMENT '会员ID（account_user.user_id）',
  `subject_type`         char(1)      DEFAULT NULL            COMMENT '主体类型（1个人 2个体工商户 3企业），提交时快照',
  `real_name`            varchar(64)  DEFAULT NULL            COMMENT '用户名称',
  `id_card`              varchar(32)  DEFAULT NULL            COMMENT '用户身份证号',
  `company_name`         varchar(128) DEFAULT NULL            COMMENT '企业名称',
  `credit_code`          varchar(32)  DEFAULT NULL            COMMENT '统一社会信用代码',
  `legal_person`         varchar(64)  DEFAULT NULL            COMMENT '企业法人',
  `registered_region`    varchar(128) DEFAULT NULL            COMMENT '企业注册地区',
  `company_address`      varchar(255) DEFAULT NULL            COMMENT '企业地址',
  `register_date`        date         DEFAULT NULL            COMMENT '注册日期',
  `license_valid_period` varchar(32)  DEFAULT NULL            COMMENT '营业执照有效期间',
  `license_image`        varchar(500) DEFAULT NULL            COMMENT '营业执照图片',
  `audit_status`         char(1)      DEFAULT '0'             COMMENT '审核状态（0待审核 1已通过 2已驳回）',
  `audit_by`             varchar(64)  DEFAULT ''              COMMENT '审核人',
  `audit_time`           datetime     DEFAULT NULL            COMMENT '审核时间',
  `audit_remark`         varchar(500) DEFAULT NULL            COMMENT '审核意见（驳回原因）',
  `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_cert_apply_user_id` (`user_id`),
  KEY `idx_cert_apply_audit_status` (`audit_status`),
  KEY `idx_cert_apply_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COMMENT='会员主体认证申请审核表';

-- ---------- 10. account_cancel_apply：会员注销申请审核（1:N） ----------
--
-- 与 account_certification_apply 结构一致，理由也一致：申请表把资料整份冗余快照下来，
-- 不引用 account_user / account_certification——会员的档案随时会变，而审核记录要能独立复现
-- "当时申请的是谁、什么主体"。两张表刻意保持同构，审核页才有同一套列与筛选口径。
--
-- 账户状态与 account_user.status 的联动（由服务层保证，不在本脚本）：
--   提交申请 -> status='2' 注销中（能提交申请说明账号可用，不会是禁用态）
--   审核通过 -> status='3' 已注销
--   审核驳回 -> status='0' 正常（放回可用状态；否则会员永久卡在"注销中"）
CREATE TABLE IF NOT EXISTS `account_cancel_apply` (
  `id`                   bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '主键，同时作为申请号',
  `user_id`              bigint(20)   NOT NULL                COMMENT '会员ID（account_user.user_id）',
  `subject_type`         char(1)      DEFAULT NULL            COMMENT '主体类型（1个人 2个体工商户 3企业），提交时快照',
  `real_name`            varchar(64)  DEFAULT NULL            COMMENT '用户名称',
  `id_card`              varchar(32)  DEFAULT NULL            COMMENT '用户身份证号',
  `company_name`         varchar(128) DEFAULT NULL            COMMENT '企业名称',
  `credit_code`          varchar(32)  DEFAULT NULL            COMMENT '统一社会信用代码',
  `legal_person`         varchar(64)  DEFAULT NULL            COMMENT '企业法人',
  `registered_region`    varchar(128) DEFAULT NULL            COMMENT '企业注册地区',
  `company_address`      varchar(255) DEFAULT NULL            COMMENT '企业地址',
  `register_date`        date         DEFAULT NULL            COMMENT '注册日期',
  `license_valid_period` varchar(32)  DEFAULT NULL            COMMENT '营业执照有效期间',
  `license_image`        varchar(500) DEFAULT NULL            COMMENT '营业执照图片',
  `audit_status`         char(1)      DEFAULT '0'             COMMENT '审核状态（0待审核 1已通过 2已驳回）',
  `audit_by`             varchar(64)  DEFAULT ''              COMMENT '审核人',
  `audit_time`           datetime     DEFAULT NULL            COMMENT '审核时间',
  `audit_remark`         varchar(500) DEFAULT NULL            COMMENT '审核意见（驳回原因）',
  `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_cancel_apply_user_id` (`user_id`),
  KEY `idx_cancel_apply_audit_status` (`audit_status`),
  KEY `idx_cancel_apply_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COMMENT='会员注销申请审核表';

-- ---------- 11. 清理辅助过程 ----------
DROP PROCEDURE IF EXISTS `hugray_add_column`;
DROP PROCEDURE IF EXISTS `hugray_add_index`;

-- ---------- 12. 结果核对 ----------
SELECT `TABLE_NAME`, `TABLE_COMMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE `TABLE_SCHEMA` = DATABASE() ORDER BY `TABLE_NAME`;

SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `COLUMN_DEFAULT`, `COLUMN_COMMENT`
FROM INFORMATION_SCHEMA.COLUMNS
WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'account_user'
ORDER BY `ORDINAL_POSITION`;