-- ============================================================
-- 生产核算（成品部门 / 白盒部门）数据库脚本
-- 目标库：product-inventory-management-new-pro（按实际环境替换）
-- 说明：
--   第 1 部分：结构变更（补列 + 新建配置表）—— 必须执行，幂等可重复执行
--   第 2 部分：演示数据 —— 可选，仅用于本地/演示环境，正式环境请勿执行或按需调整
-- 成品/白盒 的归属：在【工序类型配置】里直接把某个工序指定为 成品 或 白盒
--   （1=成品部门，2=白盒部门）
-- ============================================================
SET NAMES utf8mb4;

/* ============================================================
 * 第 1 部分：结构变更（必须执行）
 * ============================================================ */

-- 1.1 补齐 production_product_main 缺失列
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='report_type');
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `report_type` int NULL COMMENT ''报工结果类型：0合格，1轻微返工，2严重返工，3报废'' AFTER `status`', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='audit_status');
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `audit_status` int NULL DEFAULT 1 COMMENT ''审核状态：0待审核，1审核通过，2审核不通过'' AFTER `report_type`', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='audit_remark');
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `audit_remark` varchar(255) NULL COMMENT ''审核意见'' AFTER `audit_status`', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='production_product_main' AND COLUMN_NAME='repair_work_hour');
SET @s := IF(@c=0, 'ALTER TABLE `production_product_main` ADD COLUMN `repair_work_hour` decimal(10,2) NULL DEFAULT 0 COMMENT ''补修理工时'' AFTER `work_hour`', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- 1.2 补齐 product 缺失列 standard_quantity
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='product' AND COLUMN_NAME='standard_quantity');
SET @s := IF(@c=0, 'ALTER TABLE `product` ADD COLUMN `standard_quantity` decimal(18,4) NULL COMMENT ''标准数量'' AFTER `product_name`', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- 1.3 已存在报工数据默认：审核通过、合格
UPDATE `production_product_main` SET `report_type` = 0 WHERE `report_type` IS NULL;
UPDATE `production_product_main` SET `audit_status` = 1 WHERE `audit_status` IS NULL;

-- 1.4 新建核算配置表
CREATE TABLE IF NOT EXISTS `production_costing_process` (
    `id`          bigint       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `process_id`  bigint       NULL COMMENT '工序ID(technology_operation.id)',
    `dept_type`   int          NULL COMMENT '类型：1成品部门，2白盒部门',
    `remark`      varchar(255) NULL COMMENT '备注',
    `create_user` bigint       NULL COMMENT '创建人',
    `create_time` datetime     NULL COMMENT '创建时间',
    `update_user` bigint       NULL COMMENT '修改人',
    `update_time` datetime     NULL COMMENT '修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_process` (`process_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-工序类型配置';

CREATE TABLE IF NOT EXISTS `production_costing_standard` (
    `id`                bigint        NOT NULL AUTO_INCREMENT COMMENT '主键',
    `dept_type`         int           NULL COMMENT '类型：1成品，2白盒',
    `process_id`        bigint        NULL COMMENT '工序ID',
    `product_model_id`  bigint        NULL COMMENT '型号ID，白盒必填',
    `standard_quantity` decimal(18,4) NULL COMMENT '标准数量',
    `unit`              varchar(50)   NULL COMMENT '单位',
    `remark`            varchar(255)  NULL COMMENT '备注',
    `create_user`       bigint        NULL COMMENT '创建人',
    `create_time`       datetime      NULL COMMENT '创建时间',
    `update_user`       bigint        NULL COMMENT '修改人',
    `update_time`       datetime      NULL COMMENT '修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_std` (`dept_type`, `process_id`, `product_model_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-标准数量配置';

CREATE TABLE IF NOT EXISTS `production_costing_factor` (
    `id`            bigint        NOT NULL AUTO_INCREMENT COMMENT '主键',
    `process_id`    bigint        NULL COMMENT '工序ID',
    `staff_id`      bigint        NULL COMMENT '员工ID',
    `staff_name`    varchar(255)  NULL COMMENT '员工姓名',
    `violin_factor` decimal(18,4) NULL DEFAULT 1 COMMENT '小提琴折算率(基准)',
    `viola_factor`  decimal(18,4) NULL COMMENT '大提琴折算率',
    `bass_factor`   decimal(18,4) NULL COMMENT '贝斯折算率',
    `remark`        varchar(255)  NULL COMMENT '备注',
    `create_user`   bigint        NULL COMMENT '创建人',
    `create_time`   datetime      NULL COMMENT '创建时间',
    `update_user`   bigint        NULL COMMENT '修改人',
    `update_time`   datetime      NULL COMMENT '修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_factor` (`process_id`, `staff_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-折算率配置(工序+员工)';

CREATE TABLE IF NOT EXISTS `production_costing_workday` (
    `id`          bigint       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `work_month`  varchar(7)   NULL COMMENT '年月 yyyy-MM',
    `work_days`   int          NULL COMMENT '当月工作日天数',
    `remark`      varchar(255) NULL COMMENT '备注',
    `create_user` bigint       NULL COMMENT '创建人',
    `create_time` datetime     NULL COMMENT '创建时间',
    `update_user` bigint       NULL COMMENT '修改人',
    `update_time` datetime     NULL COMMENT '修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_work_month` (`work_month`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '生产核算-月度工作日配置';

/* ============================================================
 * 第 2 部分：演示数据（可选，仅本地/演示环境）
 * ============================================================ */

-- 2.1 工序类型配置（成品：冷工艺27/硫化28；白盒：切割2/收线25）
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
SELECT 27, 1, '冷工艺-成品', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=27);
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
SELECT 28, 1, '硫化-成品', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=28);
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
SELECT 2, 2, '切割-白盒', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=2);
INSERT INTO `production_costing_process` (`process_id`, `dept_type`, `remark`, `create_time`)
SELECT 25, 2, '收线-白盒', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_process` WHERE `process_id`=25);

-- 2.2 标准数量配置（成品：按工序；白盒：按工序+型号）
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
SELECT 1, 27, NULL, 10, 'm', '冷工艺标准数量', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=1 AND `process_id`=27 AND `product_model_id` IS NULL);
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
SELECT 1, 28, NULL, 20, 'm', '硫化标准数量', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=1 AND `process_id`=28 AND `product_model_id` IS NULL);
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
SELECT 2, 25, 251, 5, 'kg/km', '收线-铝合金线', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=2 AND `process_id`=25 AND `product_model_id`=251);
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
SELECT 2, 25, 256, 2, 'm', '收线-铝合金绞线', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=2 AND `process_id`=25 AND `product_model_id`=256);
INSERT INTO `production_costing_standard` (`dept_type`, `process_id`, `product_model_id`, `standard_quantity`, `unit`, `remark`, `create_time`)
SELECT 2, 2, 246, 4, 'kg/km', '切割-铝合金线', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_standard` WHERE `dept_type`=2 AND `process_id`=2 AND `product_model_id`=246);

-- 2.3 折算率配置（工序+员工）
INSERT INTO `production_costing_factor` (`process_id`, `staff_id`, `staff_name`, `violin_factor`, `viola_factor`, `bass_factor`, `remark`, `create_time`)
SELECT 27, 1, '管理员账号', 1, 3, 6, '冷工艺-折算率', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_factor` WHERE `process_id`=27 AND `staff_id`=1);
INSERT INTO `production_costing_factor` (`process_id`, `staff_id`, `staff_name`, `violin_factor`, `viola_factor`, `bass_factor`, `remark`, `create_time`)
SELECT 28, 1, '管理员账号', 1, 2, 4, '硫化-折算率', NOW() WHERE NOT EXISTS (SELECT 1 FROM `production_costing_factor` WHERE `process_id`=28 AND `staff_id`=1);

-- 2.4 乐器演示数据（小提琴/大提琴/贝斯）+ 报工链
INSERT INTO `product` (`id`, `parent_id`, `product_name`, `create_user`, `dept_id`)
SELECT 510, 276, '小提琴', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product` WHERE `id` = 510);
INSERT INTO `product` (`id`, `parent_id`, `product_name`, `create_user`, `dept_id`)
SELECT 511, 276, '大提琴', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product` WHERE `id` = 511);
INSERT INTO `product` (`id`, `parent_id`, `product_name`, `create_user`, `dept_id`)
SELECT 512, 276, '贝斯', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product` WHERE `id` = 512);

INSERT INTO `product_model` (`id`, `product_id`, `model`, `unit`, `create_user`, `dept_id`)
SELECT 5001, 510, 'VIOLIN-STD', '把', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product_model` WHERE `id` = 5001);
INSERT INTO `product_model` (`id`, `product_id`, `model`, `unit`, `create_user`, `dept_id`)
SELECT 5002, 511, 'CELLO-STD', '把', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product_model` WHERE `id` = 5002);
INSERT INTO `product_model` (`id`, `product_id`, `model`, `unit`, `create_user`, `dept_id`)
SELECT 5003, 512, 'BASS-STD', '把', 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `product_model` WHERE `id` = 5003);

INSERT INTO `production_order` (`id`, `product_model_id`, `technology_routing_id`, `nps_no`, `quantity`, `create_user`, `dept_id`)
SELECT 5000, 5001, 0, 'DEMO-ORDER-1', 100, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_order` WHERE `id` = 5000);
INSERT INTO `production_order_routing_operation` (`id`, `production_order_id`, `technology_routing_operation_id`, `technology_operation_id`, `operation_name`, `product_model_id`, `type`, `create_user`, `dept_id`)
SELECT 5000, 5000, 0, 27, '冷工艺', 5001, 1, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_order_routing_operation` WHERE `id` = 5000);
INSERT INTO `production_operation_task` (`id`, `production_order_routing_operation_id`, `technology_routing_operation_id`, `work_order_no`, `status`, `production_order_id`, `create_user`, `dept_id`)
SELECT 5000, 5000, 0, 'DEMO-WO-1', 4, 5000, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_operation_task` WHERE `id` = 5000);
INSERT INTO `production_product_main` (`id`, `product_no`, `user_id`, `user_name`, `production_operation_task_id`, `status`, `dept_id`, `create_time`, `create_user`, `report_type`, `audit_status`)
SELECT 5000, 'BG-DEMO-001', 1, '管理员账号', 5000, 4, 100, '2026-09-15 10:00:00', 1, 0, 1 WHERE NOT EXISTS (SELECT 1 FROM `production_product_main` WHERE `id` = 5000);
INSERT INTO `production_product_output` (`production_product_main_id`, `product_model_id`, `quantity`, `scrap_qty`, `create_user`, `dept_id`)
SELECT 5000, 5001, 20, 0, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_product_output` WHERE `production_product_main_id` = 5000 AND `product_model_id` = 5001);
INSERT INTO `production_product_output` (`production_product_main_id`, `product_model_id`, `quantity`, `scrap_qty`, `create_user`, `dept_id`)
SELECT 5000, 5002, 10, 0, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_product_output` WHERE `production_product_main_id` = 5000 AND `product_model_id` = 5002);
INSERT INTO `production_product_output` (`production_product_main_id`, `product_model_id`, `quantity`, `scrap_qty`, `create_user`, `dept_id`)
SELECT 5000, 5003, 5, 0, 1, 100 WHERE NOT EXISTS (SELECT 1 FROM `production_product_output` WHERE `production_product_main_id` = 5000 AND `product_model_id` = 5003);

SELECT '生产核算数据库脚本执行完成' AS result_message;