-- 设备区域（车间/位置）父子递归结构
CREATE TABLE IF NOT EXISTS `device_area` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `name` varchar(255) NOT NULL COMMENT '区域/车间/位置名称',
    `parent_id` bigint NULL COMMENT '父级区域ID，根节点为NULL',
    `ancestors` varchar(1000) NULL COMMENT '祖级路径，如 1,2,3',
    `sort` int NULL COMMENT '排序',
    `dept_id` bigint NULL COMMENT '部门ID',
    `tenant_id` bigint NULL COMMENT '租户ID',
    `create_user` int NULL COMMENT '创建人',
    `create_time` datetime NULL COMMENT '创建时间',
    `update_user` int NULL COMMENT '更新人',
    `update_time` datetime NULL COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备区域表（车间/位置）';

-- 设备台账关联区域（幂等：已存在列时跳过）
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'device_ledger' AND COLUMN_NAME = 'area_id');
SET @ddl = IF(@col_exists = 0,
    'ALTER TABLE `device_ledger` ADD COLUMN `area_id` bigint NULL COMMENT ''设备区域ID'' AFTER `dept_id`',
    'SELECT 1');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;