import { Injectable } from '@nestjs/common';
import { ViharStatus } from '@prisma/client';
import { PrismaService } from '../../common/prisma/prisma.service';
import type { PeriodFilterT } from '@vihar/shared';

interface CityStats {
  period: { from: string; to: string };
  totals: {
    vihars: number;
    completed: number;
    cancelled: number;
    inProgress: number;
    autoClosed: number;
    totalKm: number;
    totalSadhuji: number;
    totalSadhviji: number;
    totalOther: number;
    activeVolunteers: number;
  };
  pending: {
    allocations: number;
    distanceApprovals: number;
    autoClosed: number;
  };
  topContributors: Array<{ userId: number; fullName: string; viharCount: number; totalKm: number }>;
  vihrsByLocality: Array<{ localityId: number | null; name: string; count: number }>;
  vihrsBySamuday: Array<{ samudayId: number | null; name: string; count: number }>;
  dailyTrend: Array<{ date: string; count: number }>;
}

interface PersonalStats {
  period: { from: string; to: string };
  lifetime: { vihars: number; totalKm: number };
  current: { vihars: number; totalKm: number };
  comparison: { previousPeriodVihars: number; pctChange: number | null };
  rank: { position: number; total: number } | null;
  recentVihars: Array<{ viharId: number; date: string; from: string; to: string; distanceKm: number }>;
}

@Injectable()
export class StatsService {
  constructor(private readonly prisma: PrismaService) {}

  async cityStats(cityId: number, period: PeriodFilterT): Promise<CityStats> {
    const { from, to } = this.periodRange(period);

    const [totals, completedVihars, activeVolunteers, pendingAllocations, pendingApprovals] =
      await Promise.all([
        this.prisma.vihar.groupBy({
          by: ['status'],
          where: { cityId, viharDate: { gte: from, lte: to } },
          _count: { _all: true },
        }),
        this.prisma.vihar.findMany({
          where: {
            cityId,
            viharDate: { gte: from, lte: to },
            status: { in: [ViharStatus.completed, ViharStatus.auto_closed] },
          },
          select: { actualDistanceKm: true, sadhujiCount: true, sadhvijiCount: true, otherCount: true },
        }),
        this.prisma.viharVolunteer.findMany({
          where: { isActive: true, vihar: { cityId, viharDate: { gte: from, lte: to } } },
          distinct: ['userId'],
          select: { userId: true },
        }),
        this.prisma.vihar.count({
          where: {
            cityId,
            status: ViharStatus.planned,
            viharDate: { gte: new Date() },
            allocations: { none: { isActive: true } },
          },
        }),
        this.prisma.viharVolunteer.count({
          where: {
            distanceApprovalStatus: 'pending_captain',
            vihar: { cityId },
          },
        }),
      ]);

    const counts = (s: ViharStatus) => totals.find((t) => t.status === s)?._count._all ?? 0;
    const totalKm = completedVihars.reduce((sum, v) => sum + Number(v.actualDistanceKm ?? 0), 0);

    const [top, byLocality, bySamuday, dailyTrend] = await Promise.all([
      this.topContributors(cityId, from, to),
      this.viharsByLocality(cityId, from, to),
      this.viharsBySamuday(cityId, from, to),
      this.dailyTrendQuery(cityId),
    ]);

    return {
      period: { from: from.toISOString(), to: to.toISOString() },
      totals: {
        vihars: totals.reduce((s, t) => s + t._count._all, 0),
        completed: counts(ViharStatus.completed),
        cancelled: counts(ViharStatus.cancelled),
        inProgress: counts(ViharStatus.in_progress),
        autoClosed: counts(ViharStatus.auto_closed),
        totalKm: Math.round(totalKm * 100) / 100,
        totalSadhuji: completedVihars.reduce((s, v) => s + v.sadhujiCount, 0),
        totalSadhviji: completedVihars.reduce((s, v) => s + v.sadhvijiCount, 0),
        totalOther: completedVihars.reduce((s, v) => s + v.otherCount, 0),
        activeVolunteers: activeVolunteers.length,
      },
      pending: {
        allocations: pendingAllocations,
        distanceApprovals: pendingApprovals,
        autoClosed: counts(ViharStatus.auto_closed),
      },
      topContributors: top,
      vihrsByLocality: byLocality,
      vihrsBySamuday: bySamuday,
      dailyTrend,
    };
  }

  async myStats(userId: number, period: PeriodFilterT): Promise<PersonalStats> {
    const { from, to } = this.periodRange(period);
    const periodMs = to.getTime() - from.getTime();
    const previousFrom = new Date(from.getTime() - periodMs - 1);
    const previousTo = new Date(from.getTime() - 1);

    const [lifetime, inPeriodRows, previousCount, recentRows, rankRows] = await Promise.all([
      this.prisma.viharVolunteer.findMany({
        where: {
          userId,
          isActive: true,
          vihar: { status: { in: [ViharStatus.completed, ViharStatus.auto_closed] } },
        },
        include: { vihar: { select: { actualDistanceKm: true, viharDate: true } } },
      }),
      this.prisma.viharVolunteer.findMany({
        where: {
          userId,
          isActive: true,
          vihar: {
            status: { in: [ViharStatus.completed, ViharStatus.auto_closed] },
            viharDate: { gte: from, lte: to },
          },
        },
        include: { vihar: { select: { actualDistanceKm: true } } },
      }),
      this.prisma.viharVolunteer.count({
        where: {
          userId,
          isActive: true,
          vihar: {
            status: { in: [ViharStatus.completed, ViharStatus.auto_closed] },
            viharDate: { gte: previousFrom, lte: previousTo },
          },
        },
      }),
      this.prisma.viharVolunteer.findMany({
        where: {
          userId,
          isActive: true,
          vihar: { status: { in: [ViharStatus.completed, ViharStatus.auto_closed] } },
        },
        orderBy: { vihar: { viharDate: 'desc' } },
        take: 5,
        include: {
          vihar: {
            select: {
              viharId: true,
              viharDate: true,
              actualDistanceKm: true,
              departureLocation: { select: { name: true } },
              arrivalLocation: { select: { name: true } },
            },
          },
        },
      }),
      this.prisma.$queryRaw<Array<{ userId: number; totalKm: number }>>`
        SELECT u.user_id AS userId, COALESCE(SUM(v.actual_distance_km), 0) AS totalKm
        FROM user u
        LEFT JOIN vihar_volunteer vv ON vv.user_id = u.user_id AND vv.is_active = 1
        LEFT JOIN vihar v ON v.vihar_id = vv.vihar_id
          AND v.status IN ('completed','auto_closed')
        WHERE u.city_id = (SELECT city_id FROM user WHERE user_id = ${userId})
          AND u.is_volunteer = 1
          AND u.is_active = 1
        GROUP BY u.user_id
        ORDER BY totalKm DESC
      `,
    ]);

    const lifetimeKm = lifetime.reduce((s, a) => s + Number(a.vihar.actualDistanceKm ?? 0), 0);
    const periodKm = inPeriodRows.reduce((s, a) => s + Number(a.vihar.actualDistanceKm ?? 0), 0);

    const pctChange =
      previousCount === 0
        ? null
        : Math.round(((inPeriodRows.length - previousCount) / previousCount) * 100);

    const myIndex = rankRows.findIndex((r) => r.userId === userId);
    const rank = myIndex === -1 ? null : { position: myIndex + 1, total: rankRows.length };

    const recentVihars = recentRows.map((a) => ({
      viharId: a.vihar.viharId,
      date: a.vihar.viharDate.toISOString().slice(0, 10),
      from: a.vihar.departureLocation.name,
      to: a.vihar.arrivalLocation.name,
      distanceKm: Number(a.vihar.actualDistanceKm ?? 0),
    }));

    return {
      period: { from: from.toISOString(), to: to.toISOString() },
      lifetime: { vihars: lifetime.length, totalKm: Math.round(lifetimeKm * 100) / 100 },
      current: { vihars: inPeriodRows.length, totalKm: Math.round(periodKm * 100) / 100 },
      comparison: { previousPeriodVihars: previousCount, pctChange },
      rank,
      recentVihars,
    };
  }

  async leaderboard(
    cityId: number,
    period: PeriodFilterT,
    metric: 'km' | 'count' | 'streak' = 'km',
  ): Promise<Array<{ userId: number; fullName: string; viharCount: number; totalKm: number }>> {
    const { from, to } = this.periodRange(period);
    type Row = { userId: number; fullName: string; viharCount: bigint; totalKm: number };
    // streak not implemented in Phase 1 — fall back to count
    const rows = metric === 'km'
      ? await this.prisma.$queryRaw<Row[]>`
          SELECT u.user_id AS userId, u.full_name AS fullName,
            COUNT(DISTINCT vv.vihar_id) AS viharCount,
            COALESCE(SUM(v.actual_distance_km), 0) AS totalKm
          FROM user u
          JOIN vihar_volunteer vv ON vv.user_id = u.user_id AND vv.is_active = 1
          JOIN vihar v ON v.vihar_id = vv.vihar_id
          WHERE u.city_id = ${cityId}
            AND v.status IN ('completed', 'auto_closed')
            AND v.vihar_date BETWEEN ${from} AND ${to}
          GROUP BY u.user_id, u.full_name
          ORDER BY totalKm DESC
          LIMIT 10`
      : await this.prisma.$queryRaw<Row[]>`
          SELECT u.user_id AS userId, u.full_name AS fullName,
            COUNT(DISTINCT vv.vihar_id) AS viharCount,
            COALESCE(SUM(v.actual_distance_km), 0) AS totalKm
          FROM user u
          JOIN vihar_volunteer vv ON vv.user_id = u.user_id AND vv.is_active = 1
          JOIN vihar v ON v.vihar_id = vv.vihar_id
          WHERE u.city_id = ${cityId}
            AND v.status IN ('completed', 'auto_closed')
            AND v.vihar_date BETWEEN ${from} AND ${to}
          GROUP BY u.user_id, u.full_name
          ORDER BY viharCount DESC
          LIMIT 10`;

    return rows.map((r) => ({
      userId: r.userId,
      fullName: r.fullName,
      viharCount: Number(r.viharCount),
      totalKm: Math.round(Number(r.totalKm) * 100) / 100,
    }));
  }

  // ---- Private helpers ----------------------------------------------------

  private async topContributors(cityId: number, from: Date, to: Date) {
    const rows = await this.prisma.$queryRaw<
      Array<{ userId: number; fullName: string; viharCount: bigint; totalKm: number }>
    >`
      SELECT u.user_id AS userId, u.full_name AS fullName,
        COUNT(DISTINCT vv.vihar_id) AS viharCount,
        COALESCE(SUM(v.actual_distance_km), 0) AS totalKm
      FROM user u
      JOIN vihar_volunteer vv ON vv.user_id = u.user_id AND vv.is_active = 1
      JOIN vihar v ON v.vihar_id = vv.vihar_id
      WHERE u.city_id = ${cityId}
        AND v.status IN ('completed', 'auto_closed')
        AND v.vihar_date BETWEEN ${from} AND ${to}
      GROUP BY u.user_id, u.full_name
      ORDER BY totalKm DESC, viharCount DESC
      LIMIT 10
    `;
    return rows.map((r) => ({
      userId: r.userId,
      fullName: r.fullName,
      viharCount: Number(r.viharCount),
      totalKm: Math.round(Number(r.totalKm) * 100) / 100,
    }));
  }

  private async viharsByLocality(cityId: number, from: Date, to: Date) {
    const [byLocality, localities] = await Promise.all([
      this.prisma.vihar.groupBy({
        by: ['departureLocalityId'],
        where: { cityId, viharDate: { gte: from, lte: to } },
        _count: { _all: true },
      }),
      this.prisma.locality.findMany({
        where: { cityId },
        select: { localityId: true, name: true },
      }),
    ]);
    const lookup = new Map(localities.map((l) => [l.localityId, l.name]));
    return byLocality.map((g) => ({
      localityId: g.departureLocalityId,
      name: g.departureLocalityId ? (lookup.get(g.departureLocalityId) ?? 'Unknown') : 'Unassigned',
      count: g._count._all,
    }));
  }

  private async viharsBySamuday(cityId: number, from: Date, to: Date) {
    const [bySamuday, samudayList] = await Promise.all([
      this.prisma.vihar.groupBy({
        by: ['samudayId'],
        where: { cityId, viharDate: { gte: from, lte: to } },
        _count: { _all: true },
      }),
      this.prisma.samuday.findMany({
        select: { samudayId: true, name: true },
      }),
    ]);
    const lookup = new Map(samudayList.map((s) => [s.samudayId, s.name]));
    return bySamuday.map((g) => ({
      samudayId: g.samudayId,
      name: g.samudayId ? (lookup.get(g.samudayId) ?? 'Unknown') : 'Unassigned',
      count: g._count._all,
    }));
  }

  private async dailyTrendQuery(cityId: number) {
    const rows = await this.prisma.$queryRaw<Array<{ date: Date; count: bigint }>>`
      SELECT vihar_date AS date, COUNT(*) AS count
      FROM vihar
      WHERE city_id = ${cityId}
        AND vihar_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
      GROUP BY vihar_date
      ORDER BY vihar_date ASC
    `;
    return rows.map((r) => ({ date: r.date.toISOString().slice(0, 10), count: Number(r.count) }));
  }

  private periodRange(period: PeriodFilterT): { from: Date; to: Date } {
    const now = new Date();
    const today = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));
    const endOfToday = new Date(today);
    endOfToday.setUTCHours(23, 59, 59, 999);

    if (period === 'today') return { from: today, to: endOfToday };
    if (period === 'this_week') {
      const day = today.getUTCDay();
      const monday = new Date(today);
      monday.setUTCDate(today.getUTCDate() - ((day + 6) % 7));
      return { from: monday, to: endOfToday };
    }
    if (period === 'this_month') {
      const first = new Date(Date.UTC(today.getUTCFullYear(), today.getUTCMonth(), 1));
      return { from: first, to: endOfToday };
    }
    if (period === 'this_year') {
      const first = new Date(Date.UTC(today.getUTCFullYear(), 0, 1));
      return { from: first, to: endOfToday };
    }
    return { from: new Date('2000-01-01T00:00:00Z'), to: endOfToday };
  }
}