import { Inject, Provide } from '@midwayjs/core';
import { BaseService } from '@cool-midway/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository, SelectQueryBuilder } from 'typeorm';
import { Context } from '@midwayjs/koa';
import * as moment from 'moment';
import { ReportDailysiteEntity } from '../entity/dailysite';
import { ReportDailyVehicleEntity } from '../entity/dailyvehicle';
import { ReportDailySorterEntity } from '../entity/dailysorter';
import { BasicdataWastetypeEntity } from '../../basicdata/entity/wastetype';
import { BasicdataSorterEntity } from '../../basicdata/entity/sorter';
import { BasicdataVehicleEntity } from '../../basicdata/entity/vehicle';
import { BasicdataSiteEntity } from '../../basicdata/entity/site';
import { RecycleOrderEntity } from '../../shop/entity/recycleorder';
import { RecycleAppointmentEntity } from '../../shop/entity/recycleappointment';
import { RecycleTransactionEntity } from '../../shop/entity/transaction';
import { UserInfoEntity } from '../../user/entity/info';
import { PushVehicleGpsEntity } from '../../push/entity/vehiclegps';
import { BaseSysParamEntity } from '../../base/entity/sys/param';

const WASTE_COLORS: Record<string, string> = {
  SW62: '#16a34a',
  SW64: '#64748b',
  SW63: '#f59e0b',
  SW61: '#ef4444',
  SW60: '#06b6d4',
};

const ITEM_COLORS = ['#3b82f6', '#22c55e', '#f59e0b', '#a855f7', '#06b6d4', '#ef4444', '#94a3b8'];

type OrdersByType = {
  axis: string[];
  '现场回收': number[];
  '预约上门': number[];
  '大屏投递': number[];
  '活动回收': number[];
};

function n(v: any, d = 0) {
  const x = Number(v);
  return Number.isFinite(x) ? x : d;
}

function round(v: number, d = 2) {
  const p = 10 ** d;
  return Math.round(n(v) * p) / p;
}

@Provide()
export class ReportScreenService extends BaseService {
  @Inject()
  ctx: Context;

  @InjectEntityModel(ReportDailysiteEntity)
  dailySite: Repository<ReportDailysiteEntity>;

  @InjectEntityModel(ReportDailyVehicleEntity)
  dailyVehicle: Repository<ReportDailyVehicleEntity>;

  @InjectEntityModel(ReportDailySorterEntity)
  dailySorter: Repository<ReportDailySorterEntity>;

  @InjectEntityModel(BaseSysParamEntity)
  paramRepo: Repository<BaseSysParamEntity>;

  @InjectEntityModel(BasicdataWastetypeEntity)
  wasteTypeRepo: Repository<BasicdataWastetypeEntity>;

  @InjectEntityModel(BasicdataSorterEntity)
  sorterRepo: Repository<BasicdataSorterEntity>;

  @InjectEntityModel(BasicdataVehicleEntity)
  vehicleRepo: Repository<BasicdataVehicleEntity>;

  @InjectEntityModel(BasicdataSiteEntity)
  siteRepo: Repository<BasicdataSiteEntity>;

  @InjectEntityModel(RecycleOrderEntity)
  orderRepo: Repository<RecycleOrderEntity>;

  @InjectEntityModel(RecycleAppointmentEntity)
  appointmentRepo: Repository<RecycleAppointmentEntity>;

  @InjectEntityModel(RecycleTransactionEntity)
  transRepo: Repository<RecycleTransactionEntity>;

  @InjectEntityModel(UserInfoEntity)
  userRepo: Repository<UserInfoEntity>;

  @InjectEntityModel(PushVehicleGpsEntity)
  vehicleGpsRepo: Repository<PushVehicleGpsEntity>;

  async query(range: string) {
    const unavailable: string[] = [];
    const dates = this.getRange(range || 'month');
    const today = moment().format('YYYY-MM-DD');
    const yearStart = moment().startOf('year').format('YYYY-MM-DD');
    const yearEnd = moment().format('YYYY-MM-DD');

    const deptIds = this.deptIds();
    if (deptIds && deptIds.length === 0) {
      unavailable.push('当前账号没有部门数据权限');
    }

    const safe = async <T>(label: string, fn: () => Promise<T>, fallback: T): Promise<T> => {
      try {
        return await fn();
      } catch (e: any) {
        unavailable.push(`${label}：${e?.message || '查询失败'}`);
        return fallback;
      }
    };

    const emptySum = {};
    const [period, todayRow, totalRow, yearRow] = await Promise.all([
      safe('区间站点日汇总', () => this.sumSite(dates.start, dates.end), emptySum),
      safe('今日站点日汇总', () => this.sumSite(today, today), emptySum),
      safe('累计站点日汇总', () => this.sumSite('1970-01-01', today), emptySum),
      safe('年度站点日汇总', () => this.sumSite(yearStart, yearEnd), emptySum),
    ]);

    const wasteNames = await safe('垃圾种类名称', () => this.wasteNameMap(), new Map<string, string>());
    const wasteMix = this.buildWasteMix(period, wasteNames);
    const recycleItems = this.buildRecycleItems(period, wasteNames);
    const { axis, trend } = await safe(
      '回收趋势',
      () => this.buildTrend(range || 'month', dates),
      { axis: [], trend: { weight: [], value: [], carbon: [] } }
    );
    const yearCarbon = await safe('年度碳减排', () => this.buildYearCarbon(), Array.from({ length: 12 }, () => 0));
    const ranks = await safe(
      '班级/专业/院系排行',
      () => this.buildOrgRanks(dates.startTime, dates.endTime, unavailable),
      { classRank: [], majorRank: [], collegeRank: [] }
    );
    const users = await safe(
      '师生参与与碳积分',
      () => this.buildUserPoints(range || 'month', dates, axis, unavailable),
      {
        totalParticipants: 0,
        newParticipants: 0,
        participateRate: 0,
        greenActions: 0,
        issued: 0,
        consumed: 0,
        transfer: 0,
        donate: 0,
        mall: 0,
        balance: 0,
        participateTrend: axis.map(() => 0),
        deliverTrend: axis.map(() => 0),
      }
    );
    const ops = await safe('运营监控', () => this.buildOperation(today, dates, unavailable), {
      sorterOnline: 0,
      sorterTotal: 0,
      vehicleOnline: 0,
      vehicleTotal: 0,
      siteOnline: 0,
      siteTotal: 0,
      todayOrders: 0,
      todayAppointments: 0,
      pendingAppointments: 0,
      alerts: 0,
      healthAlerts: 0,
      ordersByType: {
        axis: [],
        '现场回收': [],
        '预约上门': [],
        '大屏投递': [],
        '活动回收': [],
      } as OrdersByType,
      appointmentFlow: [],
      sorterStatus: [],
      vehicles: [],
      sites: [],
    });

    const weightKg = n(period.weightTotal);
    const recyclableKg = n(period.weightSw62);
    const carbonKg = n(period.carbonTotal);
    const totalCarbonKg = n(totalRow.carbonTotal);

    return {
      range: range || 'month',
      axis,
      kpis: {
        todayWeightKg: round(n(todayRow.weightTotal), 1),
        periodWeightTon: round(weightKg / 1000, 2),
        resourceRate: weightKg > 0 ? round((recyclableKg / weightKg) * 100, 1) : 0,
        periodValue: round(n(period.estimatedSalesSw62), 0),
        periodCarbonTon: round(carbonKg / 1000, 2),
        totalWeightTon: round(n(totalRow.weightTotal) / 1000, 1),
        totalParticipants: users.totalParticipants,
        totalPoints: users.balance,
        recyclableTon: round(recyclableKg / 1000, 2),
        bulkTon: round(n(period.weightSw63) / 1000, 2),
        elecTon: round(n(period.weightSw62006) / 1000, 2),
        fabricTon: round(n(period.weightSw62004) / 1000, 2),
        yearCarbonTon: round(n(yearRow.carbonTotal) / 1000, 1),
        monthCarbonTon: round(carbonKg / 1000, 2),
        totalCarbonTon: round(totalCarbonKg / 1000, 1),
        greenActions: users.greenActions,
        newParticipants: users.newParticipants,
        participateRate: users.participateRate,
        pointsIssued: users.issued,
        pointsConsumed: users.consumed,
        pointsBalance: users.balance,
        sorterOnline: ops.sorterOnline,
        sorterTotal: ops.sorterTotal,
        vehicleOnline: ops.vehicleOnline,
        vehicleTotal: ops.vehicleTotal,
        siteOnline: ops.siteOnline,
        siteTotal: ops.siteTotal,
        todayOrders: ops.todayOrders,
        todayAppointments: ops.todayAppointments,
        pendingAppointments: ops.pendingAppointments,
        alerts: ops.alerts,
        healthAlerts: ops.healthAlerts,
      },
      wasteMix,
      recycleItems,
      trend,
      yearCarbon,
      deptRank: ranks.collegeRank,
      classRank: ranks.classRank,
      majorRank: ranks.majorRank,
      pointFlow: {
        issued: users.issued,
        transfer: users.transfer,
        donate: users.donate,
        mall: users.mall,
        balance: users.balance,
      },
      participateTrend: users.participateTrend,
      deliverTrend: users.deliverTrend,
      ordersByType: ops.ordersByType,
      appointmentFlow: ops.appointmentFlow,
      sorterStatus: ops.sorterStatus,
      vehicles: ops.vehicles,
      sites: ops.sites,
      unavailable,
    };
  }

  private deptIds(): number[] | null {
    const admin = this.ctx.admin;
    if (!admin || admin.username === 'admin') return null;
    return Array.isArray(admin.departmentIds) ? admin.departmentIds.map(Number) : [];
  }

  private applyDept(qb: SelectQueryBuilder<any>, alias: string) {
    const ids = this.deptIds();
    if (ids === null) return qb;
    if (!ids.length) {
      qb.andWhere('1 = 0');
      return qb;
    }
    qb.andWhere(`${alias}.departmentId IN (:...deptIds)`, { deptIds: ids });
    return qb;
  }

  /** 编制人数：base_sys_param.keyName = student.count.{departmentId} */
  private async studentCount(unavailable: string[]) {
    const ids = this.deptIds();
    if (ids !== null && ids.length === 0) return 0;
    const keys =
      ids === null
        ? (
            await this.paramRepo
              .createQueryBuilder('p')
              .select('p.keyName', 'keyName')
              .addSelect('p.data', 'data')
              .where("p.keyName LIKE :prefix", { prefix: 'student.count.%' })
              .getRawMany()
          ).map(r => ({ key: r.keyName, data: r.data }))
        : await Promise.all(
            ids.map(async id => {
              const row = await this.paramRepo.findOneBy({ keyName: `student.count.${id}` });
              return { key: `student.count.${id}`, data: row?.data };
            })
          );

    const missing = keys.filter(e => e.data === undefined || e.data === null || e.data === '');
    const total = keys.reduce((s, e) => s + n(e.data), 0);
    if (!keys.length || missing.length === keys.length) {
      unavailable.push('未配置编制人数参数 student.count.{departmentId}，参与率暂为 0');
      return 0;
    }
    if (missing.length) {
      unavailable.push(`缺少编制人数参数：${missing.map(e => e.key).join('、')}`);
    }
    return total;
  }

  private getRange(range: string) {
    const now = moment();
    let startM = now.clone().startOf('month');
    if (range === 'today') startM = now.clone().startOf('day');
    else if (range === 'week') startM = now.clone().startOf('isoWeek');
    else if (range === 'quarter') startM = now.clone().startOf('quarter' as any);
    else if (range === 'year') startM = now.clone().startOf('year');

    return {
      start: startM.format('YYYY-MM-DD'),
      end: now.format('YYYY-MM-DD'),
      startTime: startM.format('YYYY-MM-DD HH:mm:ss'),
      endTime: now.clone().endOf('day').format('YYYY-MM-DD HH:mm:ss'),
    };
  }

  private async sumSite(start: string, end: string) {
    const qb = this.dailySite
      .createQueryBuilder('a')
      .select('SUM(a.weightTotal)', 'weightTotal')
      .addSelect('SUM(a.weightSw60)', 'weightSw60')
      .addSelect('SUM(a.weightSw61)', 'weightSw61')
      .addSelect('SUM(a.weightSw62)', 'weightSw62')
      .addSelect('SUM(a.weightSw63)', 'weightSw63')
      .addSelect('SUM(a.weightSw64)', 'weightSw64')
      .addSelect('SUM(a.timeTotal)', 'timeTotal')
      .addSelect('SUM(a.timeSw60)', 'timeSw60')
      .addSelect('SUM(a.timeSw61)', 'timeSw61')
      .addSelect('SUM(a.timeSw62)', 'timeSw62')
      .addSelect('SUM(a.timeSw63)', 'timeSw63')
      .addSelect('SUM(a.timeSw64)', 'timeSw64')
      .addSelect('SUM(a.carbonTotal)', 'carbonTotal')
      .addSelect('SUM(a.estimatedSalesSw62)', 'estimatedSalesSw62');

    for (const i of ['001', '002', '003', '004', '005', '006', '007']) {
      qb.addSelect(`SUM(a.weightSw62${i})`, `weightSw62${i}`);
      qb.addSelect(`SUM(a.timeSw62${i})`, `timeSw62${i}`);
      qb.addSelect(`SUM(a.carbonSw62${i})`, `carbonSw62${i}`);
      qb.addSelect(`SUM(a.estimatedSalesSw62${i})`, `estimatedSalesSw62${i}`);
    }

    qb.where('a.date BETWEEN :start AND :end', { start, end });
    this.applyDept(qb, 'a');
    return (await qb.getRawOne()) || {};
  }

  private async wasteNameMap() {
    const list = await this.wasteTypeRepo.find({ select: ['code', 'name'] });
    const map = new Map<string, string>();
    list.forEach(e => map.set(String(e.code).toUpperCase(), e.name));
    return map;
  }

  private buildWasteMix(row: any, names: Map<string, string>) {
    const items = [
      { code: 'SW62', key: 'Sw62' },
      { code: 'SW64', key: 'Sw64' },
      { code: 'SW63', key: 'Sw63' },
      { code: 'SW61', key: 'Sw61' },
      { code: 'SW60', key: 'Sw60' },
    ];
    return items.map(e => ({
      name: names.get(e.code) || this.defaultWasteName(e.code),
      code: e.code,
      weight: round(n(row[`weight${e.key}`]), 1),
      value: e.code === 'SW62' ? round(n(row.estimatedSalesSw62), 0) : 0,
      count: round(n(row[`time${e.key}`]), 0),
      color: WASTE_COLORS[e.code],
    }));
  }

  private defaultWasteName(code: string) {
    return (
      {
        SW60: '湿垃圾',
        SW61: '有害垃圾',
        SW62: '可回收物',
        SW63: '大件垃圾',
        SW64: '其他垃圾',
      }[code] || code
    );
  }

  private buildRecycleItems(row: any, names: Map<string, string>) {
    const codes = [
      '900-001-S62',
      '900-002-S62',
      '900-003-S62',
      '900-004-S62',
      '900-005-S62',
      '900-006-S62',
      '900-007-S62',
    ];
    const fallback = ['废纸', '塑料', '金属', '织物', '玻璃', '电子产品', '其他可回收'];
    return codes.map((code, i) => {
      const idx = String(i + 1).padStart(3, '0');
      return {
        name: names.get(code.toUpperCase()) || names.get(code) || fallback[i],
        code,
        weight: round(n(row[`weightSw62${idx}`]), 1),
        value: round(n(row[`estimatedSalesSw62${idx}`]), 0),
        carbon: round(n(row[`carbonSw62${idx}`]), 1),
        color: ITEM_COLORS[i],
      };
    });
  }

  private async buildTrend(range: string, dates: { start: string; end: string; startTime: string; endTime: string }) {
    const trendStart =
      range === 'today' ? moment().subtract(6, 'day').format('YYYY-MM-DD') : dates.start;
    const trendEnd = dates.end;

    const qb = this.dailySite
      .createQueryBuilder('a')
      .select('a.date', 'date')
      .addSelect('SUM(a.weightTotal)', 'weight')
      .addSelect('SUM(a.estimatedSalesSw62)', 'value')
      .addSelect('SUM(a.carbonTotal)', 'carbon')
      .where('a.date BETWEEN :start AND :end', { start: trendStart, end: trendEnd })
      .groupBy('a.date')
      .orderBy('a.date', 'ASC');
    this.applyDept(qb, 'a');
    const rows = await qb.getRawMany();
    const map = new Map(
      rows.map(r => [
        moment(r.date).format('YYYY-MM-DD'),
        { weight: n(r.weight), value: n(r.value), carbon: n(r.carbon) },
      ])
    );

    const axis: string[] = [];
    const weight: number[] = [];
    const value: number[] = [];
    const carbon: number[] = [];

    if (range === 'year') {
      for (let m = 1; m <= 12; m++) {
        axis.push(`${m}月`);
        const prefix = `${moment().year()}-${String(m).padStart(2, '0')}`;
        let w = 0,
          v = 0,
          c = 0;
        map.forEach((item, key) => {
          if (key.startsWith(prefix)) {
            w += item.weight;
            v += item.value;
            c += item.carbon;
          }
        });
        weight.push(round(w, 1));
        value.push(round(v, 0));
        carbon.push(round(c, 1));
      }
    } else {
      const cursor = moment(trendStart);
      const end = moment(trendEnd);
      while (cursor.isSameOrBefore(end, 'day')) {
        const key = cursor.format('YYYY-MM-DD');
        axis.push(cursor.format('MM-DD'));
        const item = map.get(key) || { weight: 0, value: 0, carbon: 0 };
        weight.push(round(item.weight, 1));
        value.push(round(item.value, 0));
        carbon.push(round(item.carbon, 1));
        cursor.add(1, 'day');
      }
    }

    return { axis, trend: { weight, value, carbon } };
  }

  private async buildYearCarbon() {
    const start = moment().startOf('year').format('YYYY-MM-DD');
    const end = moment().endOf('year').format('YYYY-MM-DD');
    const qb = this.dailySite
      .createQueryBuilder('a')
      .select('MONTH(a.date)', 'm')
      .addSelect('SUM(a.carbonTotal)', 'carbon')
      .where('a.date BETWEEN :start AND :end', { start, end })
      .groupBy('MONTH(a.date)');
    this.applyDept(qb, 'a');
    const rows = await qb.getRawMany();
    const map = new Map(rows.map(r => [Number(r.m), n(r.carbon)]));
    return Array.from({ length: 12 }, (_, i) => round((map.get(i + 1) || 0) / 1000, 3));
  }

  private async buildOrgRanks(startTime: string, endTime: string, unavailable: string[]) {
    const empty = { classRank: [], majorRank: [], collegeRank: [] };
    try {
      const joinOrder = () => {
        const qb = this.orderRepo
          .createQueryBuilder('o')
          .innerJoin(UserInfoEntity, 'u', 'o.campusUserId = u.unionid')
          .where('o.status = :status', { status: '已完成' })
          .andWhere('o.finishTime BETWEEN :start AND :end', { start: startTime, end: endTime });
        this.applyDept(qb, 'o');
        return qb;
      };

      const metrics = (qb: any) =>
        qb
          .addSelect('COUNT(DISTINCT o.campusUserId)', 'people')
          .addSelect('SUM(o.totalWeight)', 'weight')
          .addSelect('SUM(o.totalCarbonPoint)', 'points');

      const classQb = metrics(
        joinOrder()
          .select('u.className', 'className')
          .addSelect('u.collegeName', 'collegeName')
          .andWhere("u.className IS NOT NULL AND u.className <> ''")
          .groupBy('u.classId')
          .addGroupBy('u.className')
          .addGroupBy('u.collegeName')
          .orderBy('people', 'DESC')
          .limit(10)
      );

      const majorQb = metrics(
        joinOrder()
          .select('u.majorName', 'name')
          .andWhere("u.majorName IS NOT NULL AND u.majorName <> ''")
          .groupBy('u.majorId')
          .addGroupBy('u.majorName')
          .orderBy('people', 'DESC')
          .limit(10)
      );

      const collegeQb = metrics(
        joinOrder()
          .select('u.collegeName', 'name')
          .andWhere("u.collegeName IS NOT NULL AND u.collegeName <> ''")
          .groupBy('u.collegeName')
          .orderBy('people', 'DESC')
          .limit(10)
      );

      const [classRows, majorRows, collegeRows] = await Promise.all([
        classQb.getRawMany(),
        majorQb.getRawMany(),
        collegeQb.getRawMany(),
      ]);

      if (!classRows.length) {
        unavailable.push('班级排行暂无数据（className 为空或订单未关联用户）');
      }
      if (!majorRows.length) {
        unavailable.push('专业排行暂无数据（majorName 为空或订单未关联用户）');
      }
      if (!collegeRows.length) {
        unavailable.push('院系排行暂无数据（collegeName 为空或订单未关联用户）');
      }

      const toRank = (name: string, r: any, org?: string) => {
        const people = n(r.people) || 1;
        const weight = n(r.weight);
        const points = n(r.points);
        return {
          name,
          org,
          weight: round(weight, 1),
          people: n(r.people),
          rate: 0,
          points: round(points, 0),
          carbon: round(weight, 1),
          size: n(r.people),
          weightPerCapita: round(weight / people, 2),
          pointPerCapita: round(points / people, 1),
        };
      };

      return {
        classRank: classRows.map(r => toRank(r.className, r, r.collegeName)),
        majorRank: majorRows.map(r => toRank(r.name, r)),
        collegeRank: collegeRows.map(r => toRank(r.name, r)),
      };
    } catch (e) {
      unavailable.push('师生组织排行查询失败，请检查回收订单与校园用户表');
      return empty;
    }
  }

  private async buildUserPoints(
    range: string,
    dates: { start: string; end: string; startTime: string; endTime: string },
    axis: string[],
    unavailable: string[]
  ) {
    const result = {
      totalParticipants: 0,
      newParticipants: 0,
      participateRate: 0,
      greenActions: 0,
      issued: 0,
      consumed: 0,
      transfer: 0,
      donate: 0,
      mall: 0,
      balance: 0,
      participateTrend: [] as number[],
      deliverTrend: [] as number[],
    };

    try {
      const partQb = this.orderRepo
        .createQueryBuilder('o')
        .select('COUNT(DISTINCT o.campusUserId)', 'c')
        .where('o.status = :status', { status: '已完成' });
      this.applyDept(partQb, 'o');
      result.totalParticipants = n((await partQb.getRawOne())?.c);

      const newQb = partQb.clone().andWhere('o.finishTime BETWEEN :start AND :end', {
        start: dates.startTime,
        end: dates.endTime,
      });
      result.newParticipants = n((await newQb.getRawOne())?.c);

      const userTotal = await this.studentCount(unavailable);
      result.participateRate = userTotal > 0 ? round((result.totalParticipants / userTotal) * 100, 1) : 0;

      const actQb = this.orderRepo
        .createQueryBuilder('o')
        .select('COUNT(o.id)', 'c')
        .where('o.status = :status', { status: '已完成' });
      this.applyDept(actQb, 'o');
      result.greenActions = n((await actQb.getRawOne())?.c);

      const transQb = this.transRepo
        .createQueryBuilder('t')
        .select('t.sourceType', 'sourceType')
        .addSelect('t.type', 'type')
        .addSelect('SUM(t.amount)', 'amount')
        .where('t.createTime BETWEEN :start AND :end', { start: dates.startTime, end: dates.endTime })
        .groupBy('t.sourceType')
        .addGroupBy('t.type');
      this.applyDept(transQb, 't');
      const transRows = await transQb.getRawMany();
      transRows.forEach(r => {
        const amount = n(r.amount);
        if (r.sourceType === '回收' || r.sourceType === '签到') result.issued += amount;
        if (r.sourceType === '商城') result.mall += amount;
        if (r.sourceType === '赠送') result.transfer += amount;
        if (r.sourceType === '捐赠') result.donate += amount;
        if (r.type === '支出') result.consumed += amount;
      });

      const balQb = this.userRepo.createQueryBuilder('u').select('SUM(u.carbonBalance)', 'b');
      this.applyDept(balQb, 'u');
      result.balance = round(n((await balQb.getRawOne())?.b), 0);

      const trendStart =
        range === 'today'
          ? moment().subtract(6, 'day').startOf('day').format('YYYY-MM-DD HH:mm:ss')
          : dates.startTime;
      const trendQb = this.orderRepo
        .createQueryBuilder('o')
        .select(range === 'year' ? 'MONTH(o.finishTime)' : 'DATE(o.finishTime)', 'd')
        .addSelect('COUNT(DISTINCT o.campusUserId)', 'people')
        .addSelect('COUNT(o.id)', 'times')
        .where('o.status = :status', { status: '已完成' })
        .andWhere('o.finishTime BETWEEN :start AND :end', {
          start: trendStart,
          end: dates.endTime,
        })
        .groupBy(range === 'year' ? 'MONTH(o.finishTime)' : 'DATE(o.finishTime)')
        .orderBy('d', 'ASC');
      this.applyDept(trendQb, 'o');
      const trendRows = await trendQb.getRawMany();
      const peopleMap = new Map<string, number>();
      const timesMap = new Map<string, number>();
      trendRows.forEach(r => {
        const key = range === 'year' ? `${Number(r.d)}月` : moment(r.d).format('MM-DD');
        peopleMap.set(key, n(r.people));
        timesMap.set(key, n(r.times));
      });
      result.participateTrend = axis.map(k => peopleMap.get(k) || 0);
      result.deliverTrend = axis.map(k => timesMap.get(k) || 0);
    } catch (e) {
      unavailable.push('师生参与 / 碳积分无法从用户、订单、流水表汇总');
    }

    return result;
  }

  private async buildOperation(today: string, _dates: any, unavailable: string[]) {
    const start15 = moment().subtract(15, 'minute').format('YYYY-MM-DD HH:mm:ss');
    const todayStart = moment().startOf('day').format('YYYY-MM-DD HH:mm:ss');
    const todayEnd = moment().endOf('day').format('YYYY-MM-DD HH:mm:ss');

    const sorterQb = this.sorterRepo.createQueryBuilder('a').select('COUNT(a.id)', 'c');
    this.applyDept(sorterQb, 'a');
    const sorterTotal = n((await sorterQb.getRawOne())?.c);

    const dsQb = this.dailySorter.createQueryBuilder('a').where('a.date = :today', { today });
    this.applyDept(dsQb, 'a');
    const dsRows = await dsQb.getMany();
    if (!dsRows.length) {
      unavailable.push('分拣员日表暂无当日数据，状态按离线统计');
    }

    let working = 0;
    let resting = 0;
    let abnormal = 0;
    let offlineFromTable = 0;
    let alerts = 0;
    let healthAlerts = 0;
    dsRows.forEach(r => {
      alerts += n(r.unhandledAlarmCount);
      healthAlerts += n(r.healthAbnormalCount);
      if (n(r.healthAbnormalCount) > 0 || n(r.unhandledAlarmCount) > 0) {
        abnormal += 1;
      } else if (r.checkInTime && !r.checkOutTime) {
        working += 1;
      } else if (r.checkInTime && r.checkOutTime) {
        resting += 1;
      } else {
        offlineFromTable += 1;
      }
    });
    const sorterOnline = working;
    const offline = offlineFromTable + Math.max(sorterTotal - dsRows.length, 0);
    const sorterStatus = [
      { name: '工作中', value: working, color: '#22c55e' },
      { name: '休息', value: resting, color: '#f59e0b' },
      { name: '异常', value: abnormal, color: '#ef4444' },
      { name: '离线', value: offline, color: '#94a3b8' },
    ];

    const vehicleQb = this.vehicleRepo.createQueryBuilder('a').select('COUNT(a.id)', 'c');
    this.applyDept(vehicleQb, 'a');
    const vehicleTotal = n((await vehicleQb.getRawOne())?.c);

    const vGpsQb = this.vehicleGpsRepo
      .createQueryBuilder('a')
      .select('COUNT(DISTINCT a.vehicleNo)', 'c')
      .where('a.gpsTime >= :start15', { start15 });
    this.applyDept(vGpsQb, 'a');
    const vehicleOnline = n((await vGpsQb.getRawOne())?.c);

    const siteQb = this.siteRepo
      .createQueryBuilder('a')
      .select('COUNT(a.id)', 'c')
      .where("a.siteType = '收集点'");
    this.applyDept(siteQb, 'a');
    const siteTotal = n((await siteQb.getRawOne())?.c);
    const siteOnlineQb = siteQb.clone().andWhere('a.status = :st', { st: 1 });
    const siteOnline = n((await siteOnlineQb.getRawOne())?.c);

    const orderQb = this.orderRepo
      .createQueryBuilder('o')
      .select('COUNT(o.id)', 'c')
      .where('o.createTime BETWEEN :start AND :end', { start: todayStart, end: todayEnd });
    this.applyDept(orderQb, 'o');
    const todayOrders = n((await orderQb.getRawOne())?.c);

    const apptQb = this.appointmentRepo
      .createQueryBuilder('a')
      .select('a.status', 'status')
      .addSelect('COUNT(a.id)', 'c')
      .where('a.appointmentDate = :today', { today })
      .groupBy('a.status');
    this.applyDept(apptQb, 'a');
    const apptRows = await apptQb.getRawMany();
    const apptMap = new Map(apptRows.map(r => [r.status, n(r.c)]));
    const todayAppointments = apptRows.reduce((s, r) => s + n(r.c), 0);
    const pendingAppointments =
      n(apptMap.get('新增')) +
      n(apptMap.get('待审核')) +
      n(apptMap.get('待接单')) +
      n(apptMap.get('已确认'));

    const appointmentFlow = [
      { name: '预约', value: n(apptMap.get('新增')) + n(apptMap.get('待审核')) },
      { name: '接单', value: n(apptMap.get('已确认')) + n(apptMap.get('已接单')) },
      { name: '完成', value: n(apptMap.get('已完成')) },
    ];

    const typeQb = this.orderRepo
      .createQueryBuilder('o')
      .select('DATE(o.createTime)', 'd')
      .addSelect('o.orderType', 'orderType')
      .addSelect('COUNT(o.id)', 'c')
      .where('o.createTime BETWEEN :start AND :end', {
        start: moment().subtract(6, 'day').startOf('day').format('YYYY-MM-DD HH:mm:ss'),
        end: todayEnd,
      })
      .groupBy('DATE(o.createTime)')
      .addGroupBy('o.orderType');
    this.applyDept(typeQb, 'o');
    const typeRows = await typeQb.getRawMany();
    const days = Array.from({ length: 7 }, (_, i) => moment().subtract(6 - i, 'day').format('YYYY-MM-DD'));
    const typeMap: Omit<OrdersByType, 'axis'> = {
      '现场回收': days.map(() => 0),
      '预约上门': days.map(() => 0),
      '大屏投递': days.map(() => 0),
      '活动回收': days.map(() => 0),
    };
    const typeAlias: Record<string, keyof Omit<OrdersByType, 'axis'>> = {
      '现场': '现场回收',
      '预约': '预约上门',
      '大屏': '大屏投递',
      '活动': '活动回收',
    };
    typeRows.forEach(r => {
      const key = typeAlias[r.orderType];
      const idx = days.indexOf(moment(r.d).format('YYYY-MM-DD'));
      if (idx >= 0 && key) typeMap[key][idx] = n(r.c);
    });

    const vQb = this.dailyVehicle
      .createQueryBuilder('a')
      .select('a.vehicleNo', 'vehicleNo')
      .addSelect('a.totalKm', 'km')
      .addSelect('a.minutesAbnormalIdle', 'idle')
      .addSelect('a.overspeedCount', 'overspeed')
      .where('a.date = :today', { today })
      .orderBy('a.totalKm', 'DESC')
      .limit(8);
    this.applyDept(vQb, 'a');
    const vRows = await vQb.getRawMany();
    const onlineSet = new Set(
      (
        await this.vehicleGpsRepo
          .createQueryBuilder('a')
          .select('DISTINCT a.vehicleNo', 'vehicleNo')
          .where('a.gpsTime >= :start15', { start15 })
          .getRawMany()
      ).map(r => r.vehicleNo)
    );
    const vehicles = vRows.map(r => ({
      no: r.vehicleNo,
      km: round(n(r.km), 1),
      idle: n(r.idle),
      overspeed: n(r.overspeed),
      status: onlineSet.has(r.vehicleNo) ? '在线' : '离线',
    }));

    const sQb = this.dailySite
      .createQueryBuilder('a')
      .select('a.siteName', 'name')
      .addSelect('SUM(a.weightTotal)', 'weight')
      .where('a.date = :today', { today })
      .groupBy('a.siteName')
      .orderBy('weight', 'DESC')
      .limit(8);
    this.applyDept(sQb, 'a');
    const sRows = await sQb.getRawMany();
    const sites = sRows.map(r => ({
      name: r.name,
      weight: round(n(r.weight), 1),
      online: true,
    }));

    return {
      sorterOnline,
      sorterTotal,
      vehicleOnline,
      vehicleTotal,
      siteOnline,
      siteTotal,
      todayOrders,
      todayAppointments,
      pendingAppointments,
      alerts,
      healthAlerts,
      ordersByType: {
        axis: days.map(d => d.slice(5)),
        ...typeMap,
      } as OrdersByType,
      appointmentFlow,
      sorterStatus,
      vehicles,
      sites,
    };
  }
}