import { Hono } from "hono";
import { sql } from "drizzle-orm";
import { useDrizzle } from "@/lib/db.js";
import { transaction, connector } from "@/db/schema.js";
import type { HonoEnv } from "@/types/hono.ts";

const app = new Hono<HonoEnv>();

/**
 * GET /api/stats/chart?range=30d|7d|24h
 * 返回时序图表数据，按日（30d/7d）或小时（24h）分组
 * 仅管理员可访问
 */
app.get("/", async (c) => {
  const currentUser = c.get("user");
  if (!currentUser || currentUser.role !== "admin") {
    return c.json({ error: "Forbidden" }, 403);
  }

  const range = c.req.query("range") ?? "7d";
  const db = useDrizzle();

  // 真实插口数量（connectorId >= 1），至少为 1 防止除零
  const [{ cnt }] = await db
    .select({ cnt: sql<number>`greatest(count(*)::int, 1)` })
    .from(connector)
    .where(sql`${connector.connectorId} >= 1`);

  if (range === "24h") {
    // 按小时分组，最近 24 小时
    const rows = await db.execute(sql`
      SELECT
        to_char(
          date_trunc('hour', generate_series),
          'YYYY-MM-DD"T"HH24:MI:SS"Z"'
        ) AS bucket,
        coalesce(
          (
            SELECT coalesce(sum(${transaction.stopMeterValue} - ${transaction.startMeterValue}), 0)::float / 1000
            FROM ${transaction}
            WHERE date_trunc('hour', ${transaction.stopTimestamp} AT TIME ZONE 'UTC') = date_trunc('hour', generate_series)
              AND ${transaction.stopTimestamp} IS NOT NULL
          ), 0
        ) AS energy_kwh,
        coalesce(
          (
            SELECT coalesce(sum(${transaction.chargeAmount}), 0)::float / 100
            FROM ${transaction}
            WHERE date_trunc('hour', ${transaction.stopTimestamp} AT TIME ZONE 'UTC') = date_trunc('hour', generate_series)
              AND ${transaction.stopTimestamp} IS NOT NULL
          ), 0
        ) AS revenue,
        coalesce(
          (
            SELECT count(*)::int
            FROM ${transaction}
            WHERE date_trunc('hour', ${transaction.stopTimestamp} AT TIME ZONE 'UTC') = date_trunc('hour', generate_series)
              AND ${transaction.stopTimestamp} IS NOT NULL
          ), 0
        ) AS tx_count,
        round(coalesce(
          (
            SELECT extract(epoch from sum(
              least(coalesce(t.stop_timestamp, now()), date_trunc('hour', generate_series) + interval '1 hour')
              - greatest(t.start_timestamp, date_trunc('hour', generate_series))
            )) / (${sql.raw(String(cnt))} * 3600.0) * 100
            FROM "transaction" t
            WHERE t.start_timestamp < date_trunc('hour', generate_series) + interval '1 hour'
              AND (t.stop_timestamp IS NULL OR t.stop_timestamp > date_trunc('hour', generate_series))
          ), 0
        )::numeric, 1) AS utilization_pct
      FROM generate_series(
        date_trunc('hour', now() AT TIME ZONE 'UTC') - interval '23 hours',
        date_trunc('hour', now() AT TIME ZONE 'UTC'),
        interval '1 hour'
      ) AS generate_series
      ORDER BY bucket ASC
    `);

    type Row24h = {
      bucket: string;
      energy_kwh: number;
      revenue: number;
      tx_count: number;
      utilization_pct: number;
    };
    return c.json(
      (rows.rows as Row24h[]).map((r) => ({
        bucket: r.bucket,
        energyKwh: Number(r.energy_kwh),
        revenue: Number(r.revenue),
        transactions: Number(r.tx_count),
        utilizationPct: Number(r.utilization_pct),
      })),
    );
  }

  // 按天分组，7d 或 30d
  const days = range === "30d" ? 29 : 6;

  const rows = await db.execute(sql`
    SELECT
      to_char(
        date_trunc('day', generate_series),
        'YYYY-MM-DD"T"HH24:MI:SS"Z"'
      ) AS bucket,
      coalesce(
        (
          SELECT coalesce(sum(${transaction.stopMeterValue} - ${transaction.startMeterValue}), 0)::float / 1000
          FROM ${transaction}
          WHERE date_trunc('day', ${transaction.stopTimestamp} AT TIME ZONE 'UTC') = date_trunc('day', generate_series)
            AND ${transaction.stopTimestamp} IS NOT NULL
        ), 0
      ) AS energy_kwh,
      coalesce(
        (
          SELECT coalesce(sum(${transaction.chargeAmount}), 0)::float / 100
          FROM ${transaction}
          WHERE date_trunc('day', ${transaction.stopTimestamp} AT TIME ZONE 'UTC') = date_trunc('day', generate_series)
            AND ${transaction.stopTimestamp} IS NOT NULL
        ), 0
      ) AS revenue,
      coalesce(
        (
          SELECT count(*)::int
          FROM ${transaction}
          WHERE date_trunc('day', ${transaction.stopTimestamp} AT TIME ZONE 'UTC') = date_trunc('day', generate_series)
            AND ${transaction.stopTimestamp} IS NOT NULL
        ), 0
      ) AS tx_count,
      round(coalesce(
        (
          SELECT extract(epoch from sum(
            least(coalesce(t.stop_timestamp, now()), date_trunc('day', generate_series) + interval '1 day')
            - greatest(t.start_timestamp, date_trunc('day', generate_series))
          )) / (${sql.raw(String(cnt))} * 86400.0) * 100
          FROM "transaction" t
          WHERE t.start_timestamp < date_trunc('day', generate_series) + interval '1 day'
            AND (t.stop_timestamp IS NULL OR t.stop_timestamp > date_trunc('day', generate_series))
        ), 0
      )::numeric, 1) AS utilization_pct
    FROM generate_series(
      date_trunc('day', now() AT TIME ZONE 'UTC') - interval '${sql.raw(String(days))} days',
      date_trunc('day', now() AT TIME ZONE 'UTC'),
      interval '1 day'
    ) AS generate_series
    ORDER BY bucket ASC
  `);

  type RowDay = {
    bucket: string;
    energy_kwh: number;
    revenue: number;
    tx_count: number;
    utilization_pct: number;
  };
  return c.json(
    (rows.rows as RowDay[]).map((r) => ({
      bucket: r.bucket,
      energyKwh: Number(r.energy_kwh),
      revenue: Number(r.revenue),
      transactions: Number(r.tx_count),
      utilizationPct: Number(r.utilization_pct),
    })),
  );
});

export default app;
