-- 客户申报与项目业务升级脚本
-- 适用库：product-inventory-management-xmgl
-- 执行前必须先做全库备份；本脚本不删除现有表和数据。
-- 建议在测试库验证后再执行生产库。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `customer_submission` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_id` bigint NOT NULL COMMENT '客户ID',
  `report_year` int NOT NULL COMMENT '报告年度',
  `report_name` varchar(255) DEFAULT NULL COMMENT '材料名称',
  `material_files` text COMMENT '客户材料附件，逗号分隔',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '0待审核 1处理中 2已发布 3已退回',
  `result_text` varchar(2000) DEFAULT NULL COMMENT '发布结果说明',
  `result_files` text COMMENT '结果附件，逗号分隔',
  `review_remark` varchar(1000) DEFAULT NULL COMMENT '审核意见',
  `submit_user_id` bigint DEFAULT NULL COMMENT '提交人ID',
  `submit_user_name` varchar(100) DEFAULT NULL COMMENT '提交人名称',
  `publish_user_id` bigint DEFAULT NULL COMMENT '发布人ID',
  `publish_user_name` varchar(100) DEFAULT NULL COMMENT '发布人名称',
  `publish_time` datetime DEFAULT NULL COMMENT '发布时间',
  `tenant_id` bigint DEFAULT NULL COMMENT '租户ID',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  `is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT '删除标记',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customer_report_year` (`customer_id`,`report_year`,`is_deleted`),
  KEY `idx_submission_status` (`status`),
  KEY `idx_submission_user` (`submit_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户年度申报材料与结果';

DROP PROCEDURE IF EXISTS `add_column_if_missing`;
DELIMITER $$
CREATE PROCEDURE `add_column_if_missing`(
  IN p_table varchar(64), IN p_column varchar(64), IN p_definition varchar(1000)
)
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$$
DELIMITER ;

CALL add_column_if_missing('sales_ledger', 'business_approval_status', "tinyint NOT NULL DEFAULT 0 COMMENT '0草稿 1待审批 2已通过 3已退回'");
CALL add_column_if_missing('sales_ledger', 'business_approval_remark', "varchar(1000) DEFAULT NULL COMMENT '审批意见'");
CALL add_column_if_missing('sales_ledger', 'business_approved_by', "bigint DEFAULT NULL COMMENT '审批人ID'");
CALL add_column_if_missing('sales_ledger', 'business_approved_name', "varchar(100) DEFAULT NULL COMMENT '审批人名称'");
CALL add_column_if_missing('sales_ledger', 'business_approved_time', "datetime DEFAULT NULL COMMENT '审批时间'");
CALL add_column_if_missing('sales_ledger', 'business_submitted_time', "datetime DEFAULT NULL COMMENT '提交审批时间'");
CALL add_column_if_missing('sales_ledger', 'project_management_info_id', "bigint DEFAULT NULL COMMENT '审批通过后生成的项目ID'");
DROP PROCEDURE IF EXISTS `add_column_if_missing`;

-- 固定六阶段模板；重复执行不会重复创建。
INSERT INTO `project_management_plan` (`name`, `description`, `is_delete`, `create_time`, `update_time`, `create_user_name`)
SELECT '项目申报六阶段', '业务审批通过后自动生成：立项、团队分工、资料汇报、申报、申报结果、验收', 0, NOW(), NOW(), '升级脚本'
WHERE NOT EXISTS (
  SELECT 1 FROM `project_management_plan` WHERE `name` = '项目申报六阶段' AND `is_delete` = 0
);
SET @plan_id = (SELECT `id` FROM `project_management_plan` WHERE `name` = '项目申报六阶段' AND `is_delete` = 0 ORDER BY `id` LIMIT 1);

INSERT INTO `project_management_plan_node`
(`project_management_plan_id`, `sort`, `name`, `estimated_duration`, `work_content`, `is_delete`, `create_time`, `update_time`, `create_user_name`)
SELECT @plan_id, 1, '立项', 1, '建立项目并确认负责人、计划周期和基础资料', 0, NOW(), NOW(), '升级脚本'
WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='立项' AND `is_delete`=0);
INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
SELECT @plan_id,2,'团队分工',1,'确定项目成员与工作分工',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='团队分工' AND `is_delete`=0);
INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
SELECT @plan_id,3,'资料汇报',1,'收集、补充并汇总申报资料',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='资料汇报' AND `is_delete`=0);
INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
SELECT @plan_id,4,'申报',1,'完成正式申报并登记申报信息',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='申报' AND `is_delete`=0);
INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
SELECT @plan_id,5,'申报结果',1,'登记申报结果并形成客户发布材料',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='申报结果' AND `is_delete`=0);
INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
SELECT @plan_id,6,'验收',1,'完成交付、验收和项目归档',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='验收' AND `is_delete`=0);

-- 客户门户菜单。
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
SELECT '客户服务',0,7,'customerPortal',NULL,'CustomerPortal',1,0,'M','0','0','customer:portal','#','admin',NOW(),'客户材料与结果门户'
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `path`='customerPortal' AND `parent_id`=0);
SET @portal_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `path`='customerPortal' AND `parent_id`=0 ORDER BY `menu_id` LIMIT 1);

INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
SELECT '我的申报',@portal_menu_id,1,'mySubmission','customerPortal/mySubmission/index','MySubmission',1,0,'C','0','0','customer:submission:mine','upload','admin',NOW(),'客户上传近三年材料并查看结果'
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id`=@portal_menu_id AND `path`='mySubmission');
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
SELECT '申报管理',@portal_menu_id,2,'submissionManagement','customerPortal/submissionManagement/index','SubmissionManagement',1,0,'C','0','0','customer:submission:manage','list','admin',NOW(),'内部人员审核并发布客户申报结果'
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id`=@portal_menu_id AND `path`='submissionManagement');
SET @my_submission_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `parent_id`=@portal_menu_id AND `path`='mySubmission' ORDER BY `menu_id` LIMIT 1);

-- 销售台账中的业务审批按钮权限。已有销售台账权限的角色自动获得“提交”，
-- “审批”不自动授予，由管理员在现有角色管理中分配给管理人员。
SET @sales_ledger_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `component`='salesManagement/salesLedger/index' ORDER BY `menu_id` LIMIT 1);
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
SELECT '提交业务审批',@sales_ledger_menu_id,20,'#',NULL,'',1,0,'F','0','0','business:approval:submit','#','admin',NOW(),'提交业务进入管理审批'
WHERE @sales_ledger_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms`='business:approval:submit');
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
SELECT '审核业务',@sales_ledger_menu_id,21,'#',NULL,'',1,0,'F','0','0','business:approval:review','#','admin',NOW(),'审批通过后自动生成项目'
WHERE @sales_ledger_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms`='business:approval:review');
SET @business_submit_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `perms`='business:approval:submit' ORDER BY `menu_id` LIMIT 1);
INSERT IGNORE INTO `sys_role_menu` (`role_id`,`menu_id`)
SELECT `role_id`, @business_submit_menu_id FROM `sys_role_menu`
WHERE `menu_id`=@sales_ledger_menu_id AND @business_submit_menu_id IS NOT NULL;

-- 客户角色只授予客户门户和“我的申报”，账号仍由管理员在现有用户管理中创建。
INSERT INTO `sys_role` (`role_name`,`role_key`,`role_sort`,`data_scope`,`menu_check_strictly`,`dept_check_strictly`,`status`,`del_flag`,`create_by`,`create_time`,`remark`)
SELECT '客户','customer',90,'5',1,1,'0','0','admin',NOW(),'外部客户账号；不开放自行注册'
WHERE NOT EXISTS (SELECT 1 FROM `sys_role` WHERE `role_key`='customer' AND `del_flag`='0');
SET @customer_role_id = (SELECT `role_id` FROM `sys_role` WHERE `role_key`='customer' AND `del_flag`='0' ORDER BY `role_id` LIMIT 1);
INSERT IGNORE INTO `sys_role_menu` (`role_id`,`menu_id`) VALUES (@customer_role_id,@portal_menu_id),(@customer_role_id,@my_submission_menu_id);

SET FOREIGN_KEY_CHECKS = 1;

-- 执行后检查
SELECT 'customer_submission' AS item, COUNT(*) AS count_value FROM `customer_submission`
UNION ALL SELECT 'six_stage_nodes', COUNT(*) FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `is_delete`=0
UNION ALL SELECT 'customer_portal_menus', COUNT(*) FROM `sys_menu` WHERE `menu_id`=@portal_menu_id OR `parent_id`=@portal_menu_id;