-- 商户充值模块：客户 GS 配置、电表/空调关联、充值记录扩展

CREATE TABLE IF NOT EXISTS `yw_customer_gs` (
  `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,
  `customer_id` int NOT NULL COMMENT '关联 yw_customer.id',
  `platform_gs_id` int DEFAULT NULL COMMENT '智精灵 addGs 返回的公司 ID',
  `is_pwr` tinyint DEFAULT 1 COMMENT '计费开关 0关 1开',
  `is_rest_stop` tinyint DEFAULT 0 COMMENT '18:00-09:00 不停机 0否 1是',
  `gs_bz` varchar(500) DEFAULT NULL COMMENT '备注',
  `stop_money` decimal(12,2) DEFAULT 0 COMMENT '欠费额度（元）',
  `left_money` decimal(12,4) DEFAULT NULL COMMENT '剩余金额，同步自 getGs',
  `sync_date` datetime DEFAULT NULL COMMENT '余额同步时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customer_id` (`customer_id`),
  KEY `idx_platform_gs_id` (`platform_gs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户空调 GS 配置';

CREATE TABLE IF NOT EXISTS `yw_customer_electrical` (
  `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,
  `customer_id` int NOT NULL COMMENT '关联 yw_customer.id',
  `electrical_id` int NOT NULL COMMENT '关联 yw_electrical.id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customer_electrical` (`customer_id`, `electrical_id`),
  KEY `idx_electrical_id` (`electrical_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户关联电表';

CREATE TABLE IF NOT EXISTS `yw_customer_conditioner` (
  `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,
  `customer_id` int NOT NULL COMMENT '关联 yw_customer.id',
  `conditioner_id` int NOT NULL COMMENT '关联 yw_conditioner.id',
  `dev_ratio` int DEFAULT 100 COMMENT '电费占比%',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customer_conditioner` (`customer_id`, `conditioner_id`),
  KEY `idx_conditioner_id` (`conditioner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户关联空调内机';

-- yw_electrical_charge 扩展列（若已存在则跳过）
SET @db := DATABASE();

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'yw_electrical_charge' AND COLUMN_NAME = 'balance_after') = 0,
  'ALTER TABLE `yw_electrical_charge` ADD COLUMN `balance_after` decimal(12,4) DEFAULT NULL COMMENT ''充值后余额（元）'' AFTER `banlance`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'yw_electrical_charge' AND COLUMN_NAME = 'device_info') = 0,
  'ALTER TABLE `yw_electrical_charge` ADD COLUMN `device_info` varchar(500) DEFAULT NULL COMMENT ''设备展示信息'' AFTER `balance_after`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;