-- sql提交须知：
-- 1. 请按如下分类提交sql！！！
-- 2. 表名前不可有库名！！！
-- 3. 表结构部分，新添加的表，不可先创建后修改！！！
--
-- Version
-- uat
-- prod

------------------------------------
-- 表结构
------------------------------------

ALTER TABLE `zh_tender`
ADD COLUMN `share_token` varchar(20) NOT NULL DEFAULT '' COMMENT '标段分享token' AFTER `filter_fund`,
ADD COLUMN `share_expiration` datetime NULL COMMENT '分享token到期时间' AFTER `share_token`;

ALTER TABLE `zh_stage`
ADD COLUMN `update_calc_time` timestamp NULL COMMENT '最后修改计算数据时间' AFTER `cache_time_l`,
ADD COLUMN `update_expr_calc_time` timestamp NULL COMMENT '最后使用公式计算时间' AFTER `update_calc_time`;

ALTER TABLE `zh_contract`
ADD COLUMN `rela_tender` int NULL DEFAULT NULL COMMENT '关联标段id' AFTER `exist_supplement`,
ADD COLUMN `settle_price` decimal(30, 8) NOT NULL DEFAULT 0 COMMENT '结算金额（独立字段，非合同结算功能）' AFTER `total_price`;

ALTER TABLE `zh_contract_pay`
ADD COLUMN `rela_stage` varchar(255) NULL DEFAULT NULL COMMENT '关联的标段期id或者独立支付期id' AFTER `remark`;

ALTER TABLE `zh_project_col_set`
ADD COLUMN `list` json NULL COMMENT '列表页列设置（见/const/project_setting）' AFTER `info`;

ALTER TABLE `zh_contract`
ADD COLUMN `selected` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '报表用，是否选择' AFTER `create_time`;

ALTER TABLE `zh_contract_tree`
ADD COLUMN `selected` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '报表用，是否选择' AFTER `calc2`;

ALTER TABLE `zh_contract_pay`
ADD COLUMN `kp_price` decimal(30, 8) NULL DEFAULT 0 COMMENT '开票金额' AFTER `pay_type`,
ADD COLUMN `kp_time` datetime NULL DEFAULT NULL COMMENT '开票时间' AFTER `kp_price`;

ALTER TABLE `zh_stage_audit`
ADD COLUMN `audit_locked_lid` varchar(5000) NOT NULL DEFAULT '' COMMENT '审批退回锁定台账id' AFTER `audit_locked`;

ALTER TABLE `zh_material_exponent`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '上涨幅度(%)' AFTER `basic_times`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_material_exponent_history`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '本期上涨幅度(%)' AFTER `basic_times`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '本期下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '本期有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_material_exponent_shard`
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_filing`
ADD COLUMN `upload_tips` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '上传说明' AFTER `tips`;

ALTER TABLE `zh_filing`
ADD COLUMN `create_uid` int(11) unsigned NOT NULL DEFAULT 0
COMMENT '新版配置目录添加人ID'
AFTER `add_user_id`;

CREATE TABLE `zh_sub_project_filing_permission` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT 'UUID',
  `pid` int(11) unsigned NOT NULL COMMENT '项目ID',
  `spid` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '子项目ID',
  `uid` int(11) unsigned NOT NULL COMMENT '项目用户ID',
  `filing_type` int(11) unsigned NOT NULL COMMENT '资料类别类型',
  `can_upload` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '上传/引用权限',
  `can_edit_file` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '编辑文件权限',
  `can_edit_dir` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '编辑目录权限',
  `can_lock_file` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '锁定文件权限',
  `create_uid` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '创建人ID',
  `update_uid` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '最后修改人ID',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sp_user_filing_type` (`spid`, `uid`, `filing_type`),
  KEY `idx_sp_user` (`spid`, `uid`),
  KEY `idx_sp_filing_type` (`spid`, `filing_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='子项目资料类别用户操作权限';

ALTER TABLE `zh_sub_project_filing_permission`
ADD COLUMN `filing_id` varchar(36) COLLATE utf8_unicode_ci NULL DEFAULT NULL
COMMENT '具体资料目录ID，NULL表示旧filing_type权限'
AFTER `filing_type`,
DROP INDEX `uk_sp_user_filing_type`,
ADD UNIQUE KEY `uk_sp_user_filing_node`
(`spid`, `uid`, `filing_id`),
ADD KEY `idx_sp_filing_id`
(`spid`, `filing_id`);

ALTER TABLE `zh_file`
ADD COLUMN `is_locked` tinyint(1) UNSIGNED NOT NULL DEFAULT 0
COMMENT '文件锁定状态：0未锁定，1已锁定'
AFTER `rela_info`;

CREATE TABLE `zh_project_screen` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `pid` int(11) UNSIGNED NOT NULL COMMENT '项目ID(zh_project.id)',
  `template_code` varchar(50) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT '代码内注册的大屏模板编码',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '大屏名称',
  `is_default` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否默认大屏',
  `config_json` json NULL COMMENT '大屏个性配置',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_project_screen_template` (`pid`, `template_code`),
  KEY `idx_project_screen_list` (`pid`, `create_time`)
) ENGINE=InnoDB COMMENT='项目大屏配置';

-- 台账分解附件关联纵横图纸：执行一次，drawing_access 仅保存只读访问凭证。
ALTER TABLE `zh_ledger_attachment`
  ADD COLUMN `is_drawing` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否为图纸附件：0否，1是',
  ADD COLUMN `drawing_file_id` varchar(36) DEFAULT NULL COMMENT '纵横图纸文件ID',
  ADD COLUMN `drawing_access` text NULL COMMENT '纵横图纸只读访问凭证';

ALTER TABLE `zh_advance_audit`
ADD COLUMN `audit_type` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '审批类型(1个人，2会签，3或签)' AFTER `audit_id`,
ADD COLUMN `audit_order` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '审批顺序' AFTER `audit_type`;

ALTER TABLE `calculation`.`zh_budget_final`
MODIFY COLUMN `tz_qc_qty` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '决算-变更令数量' AFTER `grow_dgn_qty`,
MODIFY COLUMN `tz_qc_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '决算-变更令金额' AFTER `tz_qc_qty`,
ADD COLUMN `un_tz_qc_qty` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '未批复变更令数量' AFTER `tz_qc_tp`,
ADD COLUMN `un_tz_qc_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '未批复变更令金额' AFTER `un_tz_qc_qty`;

-- 项目大屏：记录不展示的标段，与决策大屏的排除记录独立。
CREATE TABLE `zh_project_screen_tender` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL COMMENT '总项目 ID',
  `spid` varchar(36) NOT NULL COMMENT '具体项目 ID',
  `tid` int(11) NOT NULL COMMENT '不展示的标段 ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_project_screen_tender_pid_tid` (`pid`, `tid`),
  KEY `idx_project_screen_tender_pid_spid` (`pid`, `spid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目大屏排除标段';

------------------------------------
-- 表数据
------------------------------------

UPDATE `zh_material_exponent` SET `effective_price` = `m_price` WHERE `effective_price` IS NULL;
UPDATE `zh_material_exponent_history` SET `effective_price` = `m_price` WHERE `effective_price` IS NULL;
UPDATE `zh_material_exponent_shard` SET `effective_price` = `m_price` WHERE `effective_price` IS NULL;


-- 1. 安全巡检主表
ALTER TABLE `zh_safe_inspection`
  ADD COLUMN `flow_version` tinyint unsigned NOT NULL DEFAULT 0
    COMMENT '流程版本：0旧流程，1上报整改批复流程',
  ADD COLUMN `need_rectification` tinyint unsigned NOT NULL DEFAULT 1
    COMMENT '是否整改：0否，跳过整改和批复；1是',
  ADD COLUMN `flow_stage` tinyint unsigned NOT NULL DEFAULT 0
    COMMENT '当前阶段：0旧流程，1上报，2整改，3批复；完成状态由status判断',
  ADD COLUMN `stage_round` int unsigned NOT NULL DEFAULT 1
    COMMENT '整改批复轮次：首次为1，重新整改时递增',
  ADD INDEX `idx_safe_inspection_stage`
    (`tid`, `flow_version`, `flow_stage`, `status`);

-- 2. 安全巡检审批记录表
ALTER TABLE `zh_safe_inspection_audit`
  ADD COLUMN `flow_stage` tinyint unsigned NOT NULL DEFAULT 0
    COMMENT '记录所属阶段：0旧流程，1上报，2整改，3批复',
  ADD COLUMN `stage_round` int unsigned NOT NULL DEFAULT 1
    COMMENT '所属整改批复轮次，与主表stage_round对应',
  ADD COLUMN `rectification_item` varchar(1000) NOT NULL DEFAULT ''
    COMMENT '该整改人本轮填写的整改内容',
  ADD COLUMN `rectification_date` datetime NULL DEFAULT NULL
    COMMENT '该整改人本轮填写的整改日期',
  ADD INDEX `idx_safe_audit_stage`
    (`qiid`, `times`, `flow_stage`, `stage_round`, `is_old`, `status`),
  ADD INDEX `idx_safe_audit_todo`
    (`aid`, `status`, `qiid`, `times`);

-- 质量巡检：上报、多人整改、批复流程（以下两条 ALTER TABLE 各执行一次）
-- 历史单据默认 flow_version = 0，保留旧流程；新流程由业务代码启用。

-- 1. 质量巡检主表
ALTER TABLE `zh_quality_inspection`
  ADD COLUMN `flow_version` tinyint unsigned NOT NULL DEFAULT 0
    COMMENT '流程版本：0旧流程，1上报整改批复流程',
  ADD COLUMN `need_rectification` tinyint unsigned NOT NULL DEFAULT 1
    COMMENT '是否整改：0否，跳过整改和批复；1是',
  ADD COLUMN `flow_stage` tinyint unsigned NOT NULL DEFAULT 0
    COMMENT '当前阶段：0旧流程，1上报，2整改，3批复；完成状态由status判断',
  ADD COLUMN `stage_round` int unsigned NOT NULL DEFAULT 1
    COMMENT '整改批复轮次：首次为1，重新整改时递增',
  ADD INDEX `idx_quality_inspection_stage`
    (`tid`, `flow_version`, `flow_stage`, `status`);

-- 2. 质量巡检审批记录表（每名整改人独立保存本轮整改内容和日期）
ALTER TABLE `zh_quality_inspection_audit`
  ADD COLUMN `flow_stage` tinyint unsigned NOT NULL DEFAULT 0
    COMMENT '记录所属阶段：0旧流程，1上报，2整改，3批复',
  ADD COLUMN `stage_round` int unsigned NOT NULL DEFAULT 1
    COMMENT '所属整改批复轮次，与主表stage_round对应',
  ADD COLUMN `rectification_item` varchar(1000) NOT NULL DEFAULT ''
    COMMENT '该整改人本轮填写的整改内容',
  ADD COLUMN `rectification_date` datetime NULL DEFAULT NULL
    COMMENT '该整改人本轮填写的整改日期',
  ADD INDEX `idx_quality_audit_stage`
    (`qiid`, `times`, `flow_stage`, `stage_round`, `is_old`, `status`),
  ADD INDEX `idx_quality_audit_todo`
    (`aid`, `status`, `qiid`, `times`);

-- 安全、质量巡检附件阶段：flow_stage 字段已添加，本段不重复新增字段。
-- 历史未标记附件默认归入上报，保留已标记的整改、批复附件；本段可重复执行。

ALTER TABLE `zh_safe_inspection_attachment`
  MODIFY COLUMN `flow_stage` tinyint unsigned NOT NULL DEFAULT 1
    COMMENT '附件上传阶段：1上报（含历史附件），2整改，3批复';

ALTER TABLE `zh_quality_inspection_attachment`
  MODIFY COLUMN `flow_stage` tinyint unsigned NOT NULL DEFAULT 1
    COMMENT '附件上传阶段：1上报（含历史附件），2整改，3批复';

-- SAAS 工具：资料模板。以下结构变更仅首次执行；已更新的数据库不要重复执行。
ALTER TABLE `zh_filing_template_list`
  ADD COLUMN `is_init` tinyint unsigned NOT NULL DEFAULT 0
    COMMENT '资料目录初始化可用：0否，1是' AFTER `memo`,
  ADD COLUMN `sort_order` int NOT NULL DEFAULT 0
    COMMENT '模板排序，数字越大越靠前' AFTER `is_init`,
  ADD INDEX `idx_ft_init_sort` (`ft_type`, `is_init`, `sort_order`);

-- 仅首次迁移保留历史系统模板的初始化能力，之后不可重复启用已停用模板。
UPDATE `zh_filing_template_list` SET `is_init` = 1 WHERE `ft_type` = 0;

ALTER TABLE `zh_filing_template`
  ADD COLUMN `code` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci
    NOT NULL DEFAULT '' COMMENT '分类编号或序号' AFTER `tree_level`,
  ADD INDEX `idx_temp_parent_order` (`temp_id`, `tree_pid`, `tree_order`);

INSERT INTO `zh_permission`
  (`name`, `controller`, `action`, `pid`, `icon_class`, `create_time`, `isshow`)
SELECT '资料模板', 'filing-template', 'all', tool.`id`, '', 0, 1
FROM `zh_permission` AS tool
WHERE tool.`name` = '工具' AND tool.`pid` = 0
  AND NOT EXISTS (
    SELECT 1 FROM `zh_permission` AS existing
    WHERE existing.`controller` = 'filing-template'
  )
LIMIT 1;

-- 菜单直接归属“工具”，仅显示位置排在参考文件后、报表前。
UPDATE `zh_permission` AS menu
JOIN `zh_permission` AS tool ON tool.`name` = '工具' AND tool.`pid` = 0
SET menu.`pid` = tool.`id`, menu.`name` = '资料模板', menu.`action` = 'all', menu.`isshow` = 1
WHERE menu.`controller` = 'filing-template';

UPDATE `zh_permission` AS menu
JOIN `zh_permission` AS tool
  ON menu.`pid` = tool.`id` AND tool.`name` = '工具' AND tool.`pid` = 0
JOIN `zh_permission` AS ref
  ON ref.`pid` = tool.`id` AND ref.`controller` = 'fileReference'
SET menu.`create_time` = CASE menu.`controller`
  WHEN 'filing-template' THEN ref.`create_time` - 1
  WHEN 'report' THEN ref.`create_time` - 2
END
WHERE menu.`controller` IN ('filing-template', 'report');