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';
import { BaseSysDepartmentEntity } from '../../base/entity/sys/department';

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'];

/** 日表列与垃圾种类编码，首页 KPI/趋势口径以本表为准，不读种类表启用状态。 */
const WASTE_FIELDS: {
  code: string;
  weight: string;
  carbon: string;
  time: string;
  sales?: string;
  group?: 'recyclable' | 'bulk' | 'elec' | 'fabric';
}[] = [
  { code: 'SW60', weight: 'weightSw60', carbon: 'carbonSw60', time: 'timeSw60' },
  { code: 'SW61', weight: 'weightSw61', carbon: 'carbonSw61', time: 'timeSw61' },
  { code: 'SW62', weight: 'weightSw62', carbon: 'carbonSw62', time: 'timeSw62', sales: 'estimatedSalesSw62', group: 'recyclable' },
  { code: 'SW63', weight: 'weightSw63', carbon: 'carbonSw63', time: 'timeSw63', group: 'bulk' },
  { code: 'SW64', weight: 'weightSw64', carbon: 'carbonSw64', time: 'timeSw64' },
  ...['001', '002', '003', '004', '005', '006', '007'].map(idx => ({
    code: `SW62${idx}`,
    weight: `weightSw62${idx}`,
    carbon: `carbonSw62${idx}`,
    time: `timeSw62${idx}`,
    sales: `estimatedSalesSw62${idx}`,
    group: (idx === '006' ? 'elec' : idx === '004' ? 'fabric' : undefined) as 'elec' | 'fabric' | undefined,
  })),
];

/** 五大类；子类已滚入 SW62，总量不再把 SW62001–007 加一遍。 */
const MAIN_WASTE_FIELDS = WASTE_FIELDS.filter(f => /^SW6[0-4]$/.test(f.code));

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.wasteNames(),
      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 periodStat = this.statKpi(period);
    const todayStat = this.statKpi(todayRow);
    const totalStat = this.statKpi(totalRow);
    const yearStat = this.statKpi(yearRow);

    return {
      range: range || 'month',
      axis,
      kpis: {
        todayWeightKg: round(todayStat.weight, 1),
        periodWeightTon: round(periodStat.weight / 1000, 2),
        resourceRate: periodStat.weight > 0 ? round((periodStat.recyclable / periodStat.weight) * 100, 1) : 0,
        periodValue: round(periodStat.sales, 0),
        periodCarbonTon: round(periodStat.carbon / 1000, 2),
        totalWeightTon: round(totalStat.weight / 1000, 1),
        totalParticipants: users.totalParticipants,
        totalPoints: users.balance,
        recyclableTon: round(periodStat.recyclable / 1000, 2),
        bulkTon: round(periodStat.bulk / 1000, 2),
        elecTon: round(periodStat.elec / 1000, 2),
        fabricTon: round(periodStat.fabric / 1000, 2),
        yearCarbonTon: round(yearStat.carbon / 1000, 1),
        monthCarbonTon: round(periodStat.carbon / 1000, 2),
        totalCarbonTon: round(totalStat.carbon / 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,
    };
  }

  /**
   * 分类下钻：不传 departmentId 按部门汇总该类指标；传入则下钻该部门下各站点。
   * metric: weight | value | count | carbon
   */
  async drill(range: string, code: string, metric: string, departmentId?: number) {
    const dates = this.getRange(range || 'month');
    const rawCode = String(code || '').toUpperCase();
    const field = this.resolveDrillField(rawCode, metric);
    const names = await this.wasteNames();
    const categoryName = names.get(rawCode) || this.defaultWasteName(rawCode);
    const deptId =
      departmentId == null || departmentId === ('' as any)
        ? undefined
        : Number(departmentId);
    const level = Number.isFinite(deptId as number) ? 'site' : 'department';

    if (!field) {
      return {
        level,
        code: rawCode,
        categoryName,
        departmentId: Number.isFinite(deptId as number) ? deptId : null,
        departmentName: null,
        items: [],
      };
    }

    const expr = `SUM(IFNULL(a.${field},0))`;
    const qb = this.dailySite.createQueryBuilder('a');

    let departmentName: string | null = null;
    if (level === 'site') {
      qb.select('a.siteId', 'id')
        .addSelect('a.siteName', 'name')
        .addSelect(expr, 'metricValue')
        .where('a.date BETWEEN :start AND :end', { start: dates.start, end: dates.end })
        .andWhere('a.departmentId = :deptId', { deptId })
        .groupBy('a.siteId')
        .addGroupBy('a.siteName');
      const dept = await this.dailySite.manager
        .createQueryBuilder(BaseSysDepartmentEntity, 'd')
        .select('d.name', 'name')
        .where('d.id = :id', { id: deptId })
        .getRawOne();
      departmentName = dept?.name || `部门${deptId}`;
    } else {
      qb.select('a.departmentId', 'id')
        .addSelect(`IFNULL(d.name, CONCAT('部门', a.departmentId))`, 'name')
        .addSelect(expr, 'metricValue')
        .leftJoin(BaseSysDepartmentEntity, 'd', 'd.id = a.departmentId')
        .where('a.date BETWEEN :start AND :end', { start: dates.start, end: dates.end })
        .groupBy('a.departmentId')
        .addGroupBy('d.name');
    }

    this.applyDept(qb, 'a');
    const rows = await qb.getRawMany();
    const absCarbon = metric === 'carbon';
    const decimals = metric === 'value' || metric === 'count' ? 0 : 1;
    const items = rows
      .map(r => {
        const raw = n(r.metricValue);
        const value = round(absCarbon ? Math.abs(raw) : raw, decimals);
        return {
          id: n(r.id),
          name: String(r.name || (level === 'site' ? `站点${r.id}` : `部门${r.id}`)),
          value,
        };
      })
      .filter(e => e.value !== 0)
      .sort((a, b) => b.value - a.value);

    const total = items.reduce((s, e) => s + e.value, 0);
    return {
      level,
      code: rawCode,
      categoryName,
      departmentId: Number.isFinite(deptId as number) ? deptId : null,
      departmentName,
      items: items.map(e => ({
        ...e,
        percent: total ? round((e.value / total) * 100, 1) : 0,
      })),
    };
  }

  private resolveDrillField(code: string, metric: string): string | null {
    const f = WASTE_FIELDS.find(e => e.code === code);
    if (!f) return null;
    if (metric === 'weight') return f.weight;
    if (metric === 'carbon') return f.carbon;
    if (metric === 'count') return f.time;
    if (metric === 'value') return f.sales || null;
    return null;
  }

  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.carbonSw60)', 'carbonSw60')
      .addSelect('SUM(a.carbonSw61)', 'carbonSw61')
      .addSelect('SUM(a.carbonSw62)', 'carbonSw62')
      .addSelect('SUM(a.carbonSw63)', 'carbonSw63')
      .addSelect('SUM(a.carbonSw64)', 'carbonSw64')
      .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()) || {};
  }

  /** 种类名称仅作展示，不参与 KPI 过滤。 */
  private async wasteNames() {
    const list = await this.wasteTypeRepo.find({
      select: ['code', 'name'],
    });
    const names = new Map<string, string>();
    list.forEach(e => {
      names.set(String(e.code).toUpperCase(), e.name);
    });
    return names;
  }

  /** 按 WASTE_FIELDS 汇总；总量用五大类，分组指标用 group 字段。 */
  private statKpi(row: any) {
    const sum = (
      fields: typeof WASTE_FIELDS,
      pick: (f: (typeof WASTE_FIELDS)[number]) => string | undefined
    ) => fields.reduce((s, f) => s + n(row?.[pick(f)]), 0);
    const ofGroup = (group: 'recyclable' | 'bulk' | 'elec' | 'fabric') =>
      WASTE_FIELDS.filter(f => f.group === group).reduce((s, f) => s + n(row?.[f.weight]), 0);
    return {
      weight: sum(MAIN_WASTE_FIELDS, f => f.weight),
      carbon: sum(MAIN_WASTE_FIELDS, f => f.carbon),
      sales: sum(MAIN_WASTE_FIELDS, f => f.sales),
      recyclable: ofGroup('recyclable'),
      bulk: ofGroup('bulk'),
      elec: ofGroup('elec'),
      fabric: ofGroup('fabric'),
    };
  }

  private kpiExpr(pick: (f: (typeof WASTE_FIELDS)[number]) => string | undefined) {
    const cols = MAIN_WASTE_FIELDS.map(pick).filter((col): col is string => !!col);
    if (!cols.length) return '0';
    return cols.map(col => `IFNULL(a.${col},0)`).join(' + ');
  }

  /** 首页分类全量返回，不按种类启用状态裁剪；0 值由前端图表隐藏。 */
  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
    );
  }

  /** 可回收 7 子类全量返回；碳值保留符号（减排可为负），图表侧取绝对值展示。 */
  private buildRecycleItems(row: any, names: Map<string, string>) {
    const fallback = ['废纸', '塑料', '金属', '织物', '玻璃', '电子产品', '其他可回收'];
    return ['001', '002', '003', '004', '005', '006', '007']
      .map((idx, i) => ({ idx, i, code: `SW62${idx}` }))
      .map(e => ({
        name: names.get(e.code) || fallback[e.i],
        code: e.code,
        weight: round(n(row[`weightSw62${e.idx}`]), 1),
        value: round(n(row[`estimatedSalesSw62${e.idx}`]), 0),
        carbon: round(n(row[`carbonSw62${e.idx}`]), 1),
        color: ITEM_COLORS[e.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(${this.kpiExpr(f => f.weight)})`, 'weight')
      .addSelect(`SUM(${this.kpiExpr(f => f.sales)})`, 'value')
      .addSelect(`SUM(${this.kpiExpr(f => f.carbon)})`, '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(${this.kpiExpr(f => f.carbon)})`, '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: '已完成' })
        .andWhere('o.finishTime BETWEEN :start AND :end', {
          start: dates.startTime,
          end: dates.endTime,
        });
      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,
    };
  }
}