-- 空调多联机模块：网关/电表/计费/操作记录/用量 + 内机表扩展

CREATE TABLE IF NOT EXISTS `yw_conditioner_gateway` (
  `id` int NOT NULL AUTO_INCREMENT,
  `creator` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `editor` int DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `isdeleted` int DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `platform_wg_id` int DEFAULT NULL COMMENT '平台网关ID',
  `wg_mac` varchar(64) NOT NULL COMMENT '网关MAC',
  `wg_bz` varchar(500) DEFAULT NULL COMMENT '备注',
  `online_status` varchar(20) DEFAULT NULL COMMENT '在线/离线',
  `last_sync_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_wg_mac` (`wg_mac`),
  KEY `idx_online_status` (`online_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智精灵网关镜像';

CREATE TABLE IF NOT EXISTS `yw_conditioner_gateway_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `creator` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `editor` int DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `isdeleted` int DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `gateway_id` int DEFAULT NULL,
  `wg_mac` varchar(64) DEFAULT NULL,
  `old_status` varchar(20) DEFAULT NULL,
  `new_status` varchar(20) DEFAULT NULL,
  `log_time` datetime DEFAULT NULL,
  `source` varchar(32) DEFAULT NULL COMMENT 'manual/schedule',
  PRIMARY KEY (`id`),
  KEY `idx_gateway_id` (`gateway_id`),
  KEY `idx_wg_mac` (`wg_mac`),
  KEY `idx_log_time` (`log_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网关上下线记录';

CREATE TABLE IF NOT EXISTS `yw_conditioner_meter` (
  `id` int NOT NULL AUTO_INCREMENT,
  `creator` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `editor` int DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `isdeleted` int DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `platform_db_id` int DEFAULT NULL,
  `db_name` varchar(128) DEFAULT NULL,
  `db_adr` varchar(64) DEFAULT NULL,
  `wg_mac` varchar(64) DEFAULT NULL,
  `wg_id` int DEFAULT NULL,
  `xy_id` int DEFAULT NULL,
  `xy_name` varchar(128) DEFAULT NULL,
  `dlj_mac` varchar(64) DEFAULT NULL COMMENT '多联机MAC',
  `btl` int DEFAULT NULL COMMENT '波特率',
  `jy` int DEFAULT NULL COMMENT '是否校验 0无校验 1校验',
  `db_bb` int DEFAULT NULL COMMENT '变比',
  `standby_share` varchar(32) DEFAULT NULL COMMENT '待机分摊',
  `outdoor_loop` int DEFAULT NULL COMMENT '外机回路号',
  `power_kw` decimal(12,4) DEFAULT NULL,
  `total_dl` decimal(12,4) DEFAULT NULL,
  `db_data` text,
  `db_uptime` varchar(32) DEFAULT NULL,
  `last_sync_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_platform_db_id` (`platform_db_id`),
  KEY `idx_wg_mac` (`wg_mac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智精灵电表镜像';

CREATE TABLE IF NOT EXISTS `yw_conditioner_billing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `creator` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `editor` int DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `isdeleted` int DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `platform_dev_id` int DEFAULT NULL,
  `dev_name` varchar(128) DEFAULT NULL,
  `wg_mac` varchar(64) DEFAULT NULL,
  `kw_type` int DEFAULT NULL COMMENT '0时长1能耗2电表',
  `fan_arg` decimal(12,4) DEFAULT NULL,
  `fan_kw` decimal(12,4) DEFAULT NULL,
  `hig_kw` decimal(12,4) DEFAULT NULL,
  `mid_kw` decimal(12,4) DEFAULT NULL,
  `low_kw` decimal(12,4) DEFAULT NULL,
  `kt_dj` decimal(12,4) DEFAULT NULL,
  `last_sync_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_platform_dev_id` (`platform_dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智精灵计费系数镜像';

CREATE TABLE IF NOT EXISTS `yw_conditioner_actions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `creator` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `editor` int DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `isdeleted` int DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `conditioner_id` int DEFAULT NULL,
  `platform_dev_id` int DEFAULT NULL,
  `dev_name` varchar(128) DEFAULT NULL,
  `wg_mac` varchar(64) DEFAULT NULL,
  `action_type` int NOT NULL COMMENT '1开关2模式3风速4温度5锁定6查电量7查功率',
  `action_content` varchar(500) DEFAULT NULL,
  `result_status` int DEFAULT 1 COMMENT '0失败1成功',
  `result_msg` varchar(500) DEFAULT NULL,
  `source` varchar(64) DEFAULT NULL COMMENT '操作来源',
  `request_body` text,
  `response_body` text,
  PRIMARY KEY (`id`),
  KEY `idx_conditioner_id` (`conditioner_id`),
  KEY `idx_platform_dev_id` (`platform_dev_id`),
  KEY `idx_create_date` (`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空调设备控制记录';

CREATE TABLE IF NOT EXISTS `yw_conditioner_usage` (
  `id` int NOT NULL AUTO_INCREMENT,
  `creator` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `editor` int DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `isdeleted` int DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `platform_dev_id` int NOT NULL,
  `dev_name` varchar(128) DEFAULT NULL,
  `usage_date` date NOT NULL,
  `sum_time` decimal(12,2) DEFAULT NULL,
  `sum_dl` decimal(12,4) DEFAULT NULL,
  `sum_df` decimal(12,4) DEFAULT NULL,
  `gs_id` int DEFAULT NULL,
  `sync_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_dev_date_gs` (`platform_dev_id`,`usage_date`,`gs_id`),
  KEY `idx_usage_date` (`usage_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备日用量镜像';

-- 扩展 yw_conditioner（首次执行；列已存在时会报错可忽略）
ALTER TABLE `yw_conditioner` ADD COLUMN `platform_dev_id` int DEFAULT NULL COMMENT '平台设备ID';
ALTER TABLE `yw_conditioner` ADD COLUMN `wg_id` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `wg_mac` varchar(64) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `wg_qid` varchar(64) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `pid` varchar(16) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `online` varchar(20) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `pwr` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `mode` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `fan` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `fan_set` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `temp` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `temp_set` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `kt_lock` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `stop_logo` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `uptime` varchar(32) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `floor_id` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `floor_name` varchar(64) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `room_id` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `room_name` varchar(64) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `dev_type_id` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `dev_type_name` varchar(64) DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_pwr` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_mode` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_fan` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_min_temp` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `lock_max_temp` int DEFAULT NULL;
ALTER TABLE `yw_conditioner` ADD COLUMN `last_sync_date` datetime DEFAULT NULL;