-- =====================================================
-- ERP 采购管理模块完善 - SQL 脚本
-- =====================================================

-- =====================================================
-- 00. 创建 BPM 采购审核分类（必须在采购订单前创建）
-- =====================================================
INSERT INTO bpm_category (id, name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
VALUES (100, '采购审核', 'purchase_approve', '采购订单审批流程分类', 0, 10, '1', NOW(), '1', NOW(), 0)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- =====================================================
-- 01. 采购入库表增加验收字段
-- =====================================================
ALTER TABLE `erp_purchase_in` ADD COLUMN IF NOT EXISTS `inspection_status` tinyint DEFAULT 0 COMMENT '验收状态（0:待验收 1:已验收 2:验收不合格）';
ALTER TABLE `erp_purchase_in` ADD COLUMN IF NOT EXISTS `inspection_time` datetime DEFAULT NULL COMMENT '验收时间';
ALTER TABLE `erp_purchase_in` ADD COLUMN IF NOT EXISTS `inspector_id` bigint DEFAULT NULL COMMENT '验收人员编号';
ALTER TABLE `erp_purchase_in` ADD COLUMN IF NOT EXISTS `inspection_result` varchar(500) DEFAULT NULL COMMENT '验收结果';

-- =====================================================
-- 02. 供应商表增加字段
-- =====================================================
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `credit_code` varchar(50) DEFAULT NULL COMMENT '统一社会信用代码';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `bank_name` varchar(100) DEFAULT NULL COMMENT '开户银行';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `bank_account` varchar(50) DEFAULT NULL COMMENT '银行账号';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `address` varchar(200) DEFAULT NULL COMMENT '地址';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `contact` varchar(50) DEFAULT NULL COMMENT '联系人';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `phone` varchar(20) DEFAULT NULL COMMENT '联系电话';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `email` varchar(100) DEFAULT NULL COMMENT '邮箱';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `tax_no` varchar(50) DEFAULT NULL COMMENT '税号';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `qualification` varchar(500) DEFAULT NULL COMMENT '资质信息';
ALTER TABLE `erp_supplier` ADD COLUMN IF NOT EXISTS `cooperation_time` date DEFAULT NULL COMMENT '合作开始时间';

-- =====================================================
-- 03. 采购订单表增加审批字段
-- =====================================================
ALTER TABLE `erp_purchase_order` ADD COLUMN IF NOT EXISTS `approve_time` datetime DEFAULT NULL COMMENT '审批时间';
ALTER TABLE `erp_purchase_order` ADD COLUMN IF NOT EXISTS `approver_id` bigint DEFAULT NULL COMMENT '审批人员编号';

-- =====================================================
-- 04. 初始化菜单数据
-- =====================================================

-- 采购管理目录
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES ('采购管理', '', 1, 10, 0, '/erp/purchase', 'ep:goods', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 获取采购管理菜单ID
SET @purchase_menu_id = LAST_INSERT_ID();

-- 供应商管理菜单
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES ('供应商管理', '', 2, 1, @purchase_menu_id, 'supplier', 'ep:office-building', 'erp/purchase/supplier/index', 'ErpSupplier', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
SET @supplier_menu_id = LAST_INSERT_ID();

-- 供应商按钮权限
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted) VALUES
('供应商查询', 'erp:supplier:query', 3, 1, @supplier_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('供应商新增', 'erp:supplier:create', 3, 2, @supplier_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('供应商修改', 'erp:supplier:update', 3, 3, @supplier_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('供应商删除', 'erp:supplier:delete', 3, 4, @supplier_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('供应商导出', 'erp:supplier:export', 3, 5, @supplier_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 采购订单菜单
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES ('采购订单', '', 2, 2, @purchase_menu_id, 'order', 'ep:document-checked', 'erp/purchase/order/index', 'ErpPurchaseOrder', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
SET @order_menu_id = LAST_INSERT_ID();

-- 采购订单按钮权限
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted) VALUES
('采购订单查询', 'erp:purchase-order:query', 3, 1, @order_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('采购订单新增', 'erp:purchase-order:create', 3, 2, @order_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('采购订单修改', 'erp:purchase-order:update', 3, 3, @order_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('采购订单删除', 'erp:purchase-order:delete', 3, 4, @order_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('采购订单审批', 'erp:purchase-order:update-status', 3, 5, @order_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('采购订单导出', 'erp:purchase-order:export', 3, 6, @order_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 采购入库菜单
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES ('采购入库', '', 2, 3, @purchase_menu_id, 'in', 'ep:box', 'erp/purchase/in/index', 'ErpPurchaseIn', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 采购退货菜单
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES ('采购退货', '', 2, 4, @purchase_menu_id, 'return', 'ep:back', 'erp/purchase/return/index', 'ErpPurchaseReturn', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 采购统计菜单
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES ('采购统计', '', 2, 5, @purchase_menu_id, 'statistics', 'ep:data-analysis', 'erp/purchase/statistics/index', 'ErpPurchaseStatistics', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
SET @statistics_menu_id = LAST_INSERT_ID();

-- 采购统计按钮权限
INSERT INTO system_menu (name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted) VALUES
('采购统计查询', 'erp:purchase-statistics:query', 3, 1, @statistics_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('供应商供货统计', 'erp:purchase-statistics:supplier', 3, 2, @statistics_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('采购价格分析', 'erp:purchase-statistics:price', 3, 3, @statistics_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
('未完成订单跟踪', 'erp:purchase-statistics:uncompleted', 3, 4, @statistics_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');