-- ============================================================================
-- 大屏 /large-screen/* 慢查询优化 SQL 脚本
-- 背景: /large-screen/summary 在 MyCat 分表下按 site_id 过滤会广播扫描 50 张
--       t_charging_order 物理表(无索引、无时间边界、跨分片 join),导致超时。
-- 方案: 新建站点级预聚合表 t_large_screen_site_stat(全局表),由后端定时任务
--       按"月/日/全局"切片汇总写入,大屏接口改为读该表;本脚本为 50 张分表
--       补充 create_time 索引,供聚合任务按月/按日切片范围扫描使用。
--
-- 执行方式: 在 MySQL 192.168.110.74 上直接执行(不要走 MyCat)。
-- 幂等: 可重复执行,不会报错。
-- ============================================================================

-- 1. 预聚合表(全局表,不分区)
CREATE TABLE IF NOT EXISTS `charging_pile_order`.`t_large_screen_site_stat` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stat_type` char(1) NOT NULL COMMENT '统计粒度:D=日 M=月 G=全局',
  `period` varchar(16) NOT NULL COMMENT 'D:yyyy-MM-dd / M:yyyy-MM / G:ALL',
  `site_id` int NOT NULL COMMENT '站点ID, G行固定为0',
  `order_count` bigint NOT NULL DEFAULT 0 COMMENT '订单数',
  `user_count` bigint NOT NULL DEFAULT 0 COMMENT '充电用户数(按站点统计, 跨站充电会重复计数)',
  `capacity` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '充电量(kWh)',
  `electric_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '电费',
  `service_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '服务费(含三方0.8折算)',
  `income` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '收入(电费+服务费)',
  `total_power` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '功率合计(仅D行使用)',
  `registered_user_count` bigint NOT NULL DEFAULT 0 COMMENT '注册用户数(仅G行使用)',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stat_period_site` (`stat_type`,`period`,`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大屏站点统计预聚合表';

-- 2. 给 50 张 t_charging_order 物理分表补 create_time 索引(幂等,已存在则跳过)
DROP PROCEDURE IF EXISTS `charging_pile_order`.`add_large_screen_create_time_index`;
DELIMITER $$
CREATE PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE tbl_name VARCHAR(64);
  DECLARE idx_exists INT;
  WHILE i < 50 DO
    SET tbl_name = CONCAT('t_charging_order_', i);
    SELECT COUNT(*) INTO idx_exists
    FROM information_schema.statistics
    WHERE table_schema = 'charging_pile_order'
      AND table_name = tbl_name
      AND index_name = 'idx_create_time';
    IF idx_exists = 0 THEN
      SET @ddl = CONCAT('ALTER TABLE `charging_pile_order`.`', tbl_name,
                        '` ADD KEY idx_create_time (create_time)');
      PREPARE stmt FROM @ddl;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END IF;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL `charging_pile_order`.`add_large_screen_create_time_index`();
DROP PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`;

-- 3. 索引结果核对(应返回 50 行)
SELECT table_name, index_name
FROM information_schema.statistics
WHERE table_schema = 'charging_pile_order'
  AND table_name LIKE 't_charging_order\_%'
  AND index_name = 'idx_create_time'
ORDER BY table_name;

-- 1.2 用户-站点对表(充电用户数精确去重)
-- 背景: 预聚合表的 user_count 按站点求和, 跨站充电用户会重复计数。
--       本表记录"每个站点出现过哪些用户"(所有订单口径, 与工作台 countAllUserData 一致),
--       接口对选中站点做 COUNT(DISTINCT app_user_id) 精确去重。
CREATE TABLE IF NOT EXISTS `charging_pile_order`.`t_large_screen_user_site` (
  `site_id` int NOT NULL COMMENT '站点ID',
  `app_user_id` bigint NOT NULL COMMENT '用户ID',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`site_id`,`app_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大屏充电用户-站点对(精确去重)';

-- ============================================================================
-- 上线后数据一致性对比(只读,可在线执行;对比新老口径差值,应≈0)
-- 老口径: 现接口 getLargeScreenSummary 的原始 SQL(挑少量站点对比,避免生产压力)
-- ============================================================================
-- SELECT COUNT(DISTINCT charging_order.id) AS validOrderCount,
--        COALESCE(ROUND(SUM(accounting.charging_capacity), 2), 0) AS totalChargingCapacity,
--        COALESCE(ROUND(SUM(accounting.period_electric_price), 2), 0) AS electricityFee,
--        COALESCE(ROUND(SUM(CASE WHEN charging_order.order_source IN (0,1)
--            THEN accounting.period_service_price ELSE accounting.period_service_price * 0.8 END), 2), 0) AS serviceFee
-- FROM charging_pile_order.t_charging_order charging_order
-- LEFT JOIN charging_pile_order.t_charging_order_accounting_strategy accounting
--        ON accounting.charging_order_id = charging_order.id
-- WHERE charging_order.del_flag = 0
--   AND charging_order.status = 5
--   AND charging_order.recharge_payment_status = 2
--   AND charging_order.site_id IN (101, 102);
--
-- SELECT SUM(order_count) AS validOrderCount,
--        SUM(capacity) AS totalChargingCapacity,
--        SUM(electric_fee) AS electricityFee,
--        SUM(service_fee) AS serviceFee
-- FROM charging_pile_order.t_large_screen_site_stat
-- WHERE stat_type = 'M' AND site_id IN (101, 102);