-- 物料总库存汇总表（按 material_id 对应 mes_material）
-- 明细仍以 mes_material_kc_log 为准，本表维护每种物料当前总数量

CREATE TABLE IF NOT EXISTS `mes_material_kc_total` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `material_id` varchar(64) NOT NULL COMMENT '物料ID（mes_material.id）',
  `total_nums` bigint NOT NULL DEFAULT 0 COMMENT '总库存数量',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '状态',
  `create_by` varchar(64) NOT NULL COMMENT '创建者',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(64) NOT NULL COMMENT '更新者',
  `update_date` datetime NOT NULL COMMENT '更新时间',
  `remarks` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mes_material_kc_total_mid` (`material_id`),
  KEY `idx_mes_material_kc_total_nums` (`total_nums`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物料总库存（按物料汇总）';

-- 用现有明细库存初始化（重复执行前先清空）
DELETE FROM `mes_material_kc_total`;

INSERT INTO `mes_material_kc_total` (
  `id`, `material_id`, `total_nums`, `status`,
  `create_by`, `create_date`, `update_by`, `update_date`, `remarks`
)
SELECT
  CONCAT('kc_total_', a.material_id) AS id,
  a.material_id,
  COALESCE(SUM(a.nums), 0) AS total_nums,
  '0',
  'system',
  NOW(),
  'system',
  NOW(),
  '由 mes_material_kc_log 初始化'
FROM `mes_material_kc_log` a
WHERE a.material_id IS NOT NULL AND a.material_id <> ''
GROUP BY a.material_id;

-- 菜单：来料管理 (parent menu_code: 1667359431003865088)

DELETE FROM `js_sys_menu` WHERE `menu_code` IN ('mesMaterialKcTotal', 'mesMaterialKcTotalEdit');

INSERT INTO `js_sys_menu` (
  `menu_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`, `tree_leaf`, `tree_level`,
  `tree_names`, `menu_name`, `menu_type`, `menu_href`, `menu_target`, `menu_icon`, `menu_color`,
  `menu_title`, `permission`, `weight`, `is_show`, `sys_code`, `module_codes`, `component`, `params`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`
) VALUES (
  'mesMaterialKcTotal', '1667359431003865088', '0,1667359431003865088,',
  210, '0000009200,0000000210,', '1', 1,
  '来料管理/物料总库存', '物料总库存', '1', '/mes/mesMaterialKcTotal/list', '',
  'fa-cubes', '', '', 'mes:mesMaterialKcTotal:view', 50, '1', 'default', 'core', NULL, NULL,
  '0', 'system', NOW(), 'system', NOW(), '按物料汇总库存；明细见来料库存记录'
);

INSERT INTO `js_sys_menu` (
  `menu_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`, `tree_leaf`, `tree_level`,
  `tree_names`, `menu_name`, `menu_type`, `menu_href`, `menu_target`, `menu_icon`, `menu_color`,
  `menu_title`, `permission`, `weight`, `is_show`, `sys_code`, `module_codes`, `component`, `params`,
  `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`
) VALUES (
  'mesMaterialKcTotalEdit', 'mesMaterialKcTotal', '0,1667359431003865088,mesMaterialKcTotal,',
  30, '0000009200,0000000210,0000000030,', '1', 2,
  '来料管理/物料总库存/编辑', '编辑', '2', '', '',
  '', '', '', 'mes:mesMaterialKcTotal:edit', 40, '0', 'default', 'core', NULL, NULL,
  '0', 'system', NOW(), 'system', NOW(), '含按明细重算'
);