-- 商户 H5：微信支付订单、轮播图、客户首次充值标记、关联扩展

CREATE TABLE IF NOT EXISTS `yw_wx_pay_order` (
  `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` tinyint DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `order_no` varchar(64) NOT NULL COMMENT '商户订单号',
  `customer_id` int NOT NULL COMMENT '付款商户',
  `order_type` tinyint NOT NULL COMMENT '0电表充值 1空调充值 2账单缴费',
  `biz_ref_id` int DEFAULT NULL COMMENT 'electrical_id/customer_id/bill_id',
  `biz_record_id` int DEFAULT NULL COMMENT 'yw_electrical_charge.id 或 yw_contract_revenue.id',
  `amount` decimal(12,2) NOT NULL,
  `status` tinyint DEFAULT 0 COMMENT '0待支付 1成功 2失败 3关闭',
  `wx_transaction_id` varchar(64) DEFAULT NULL,
  `pay_time` datetime DEFAULT NULL,
  `openid` varchar(128) DEFAULT NULL,
  `request_snapshot` text COMMENT '下单参数JSON',
  `status_info` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微信H5支付订单';

CREATE TABLE IF NOT EXISTS `yw_h5_banner` (
  `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` tinyint DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `image_url` varchar(500) NOT NULL,
  `link_url` varchar(500) DEFAULT NULL,
  `sortnum` int DEFAULT 0,
  `status` tinyint DEFAULT 0 COMMENT '0启用 1禁用',
  `scope` tinyint DEFAULT 1 COMMENT '1商户工作台',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='H5轮播图';

SET @db = DATABASE();

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'yw_customer' AND COLUMN_NAME = 'first_recharge_done') = 0,
  'ALTER TABLE `yw_customer` ADD COLUMN `first_recharge_done` tinyint DEFAULT 0 COMMENT ''是否已完成首次充值制'' AFTER `openid`',
  '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_customer_electrical' AND COLUMN_NAME = 'bind_source') = 0,
  'ALTER TABLE `yw_customer_electrical` ADD COLUMN `bind_source` tinyint DEFAULT 1 COMMENT ''0手动 1合同自动'' AFTER `electrical_id`, ADD COLUMN `contract_id` int DEFAULT NULL AFTER `bind_source`',
  '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 = 'wx_order_no') = 0,
  'ALTER TABLE `yw_electrical_charge` ADD COLUMN `wx_order_no` varchar(64) DEFAULT NULL COMMENT ''微信支付订单号'' AFTER `customer_id`',
  '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_contract_revenue' AND COLUMN_NAME = 'wx_order_no') = 0,
  'ALTER TABLE `yw_contract_revenue` ADD COLUMN `wx_order_no` varchar(64) DEFAULT NULL COMMENT ''微信支付订单号'' AFTER `bill_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;