import { Injectable } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { PrismaService } from '../../common/prisma.service';

@Injectable()
export class DashboardService {
  constructor(
    private prisma: PrismaService,
    @InjectDataSource() private dataSource: DataSource,
  ) {}

  async getBackofficeStats() {
    const [totalBookings, totalPackageBookings, totalTickets, totalPassengers, blockedSeatsCount, revenueRows, packageRevenueRows] =
      await Promise.all([
        this.prisma.booking.count({ where: { status: { in: ['CONFIRMED', 'BOARDED'] } } }),
        this.prisma.booking.count({ where: { status: { in: ['CONFIRMED', 'BOARDED'] }, packageId: { not: null } } }),
        this.prisma.ticket.count(),
        this.prisma.passenger.count(),
        this.prisma.seat.count({ where: { status: 'BLOCKED' } }),
        this.prisma.$queryRaw<{ currency: string; total: bigint }[]>`
          SELECT
            COALESCE("displayCurrency"::text, "currency"::text) AS currency,
            SUM(COALESCE("displayTotalMinor", "totalMinor")) AS total
          FROM passenger."Booking"
          WHERE status IN ('CONFIRMED', 'BOARDED')
            AND "packageId" IS NULL
            AND id IN (SELECT "bookingId" FROM passenger."PaymentIntent" WHERE status = 'SUCCEEDED')
          GROUP BY COALESCE("displayCurrency"::text, "currency"::text)
        `,
        this.prisma.$queryRaw<{ currency: string; total: bigint }[]>`
          SELECT
            COALESCE("displayCurrency"::text, "currency"::text) AS currency,
            SUM(COALESCE("displayTotalMinor", "totalMinor")) AS total
          FROM passenger."Booking"
          WHERE status IN ('CONFIRMED', 'BOARDED')
            AND "packageId" IS NOT NULL
            AND id IN (SELECT "bookingId" FROM passenger."PaymentIntent" WHERE status = 'SUCCEEDED')
          GROUP BY COALESCE("displayCurrency"::text, "currency"::text)
        `,
      ]);

    const totalPackageTickets = await this.prisma.ticket.count({
      where: { booking: { packageId: { not: null } } },
    });

    const toMap = (rows: { currency: string; total: bigint }[]) =>
      Object.entries(
        rows.reduce((m, r) => { m[r.currency] = Number(r.total); return m; }, {} as Record<string, number>),
      ).map(([currency, totalMinor]) => ({ currency, totalMinor }));

    return {
      totalBookings,
      totalPackageBookings,
      totalNormalBookings: totalBookings - totalPackageBookings,
      totalTickets,
      totalPackageTickets,
      totalNormalTickets: totalTickets - totalPackageTickets,
      totalPassengers,
      blockedSeatsCount,
      revenueByCurrency: toMap(revenueRows),
      packageRevenueByCurrency: toMap(packageRevenueRows),
    };
  }

  async getHomeDashboard(passengerId: string) {
    const now = new Date();
    const [passenger, upcomingBooking, wallet, promos, weatherAlerts, stationSignals, savedRoutes] = await Promise.all([
      this.prisma.passenger.findUnique({ where: { id: passengerId }, include: { loyalty: true } }),
      this.prisma.booking.findFirst({
        where: { passengerId, status: 'CONFIRMED', schedule: { departureAt: { gte: now } } },
        include: {
          schedule: { include: { originStation: true, destinationStation: true, train: true, liveStatus: true } },
          seats: { include: { seat: { include: { coach: true } } }, take: 1 },
          tickets: { take: 1 },
        },
        orderBy: { createdAt: 'asc' },
      }),
      this.prisma.walletAccount.findUnique({ where: { passengerId } }),
      this.prisma.promotion.count({ where: { active: true, validUntil: { gte: now } } }),
      this.prisma.weatherAlert.findMany({ where: { validUntil: { gte: now } }, take: 3 }),
      this.prisma.stationCrowdSignal.findMany({ include: { station: true }, take: 5 }),
      this.prisma.savedRoute.findMany({ where: { passengerId }, orderBy: { tripCount: 'desc' }, take: 5 }),
    ]);

    const hour = now.getHours();
    const greetingKey = hour < 12 ? 'MORNING' : hour < 17 ? 'AFTERNOON' : 'EVENING';

    let firstName = '';
    if (passenger?.iamUserId) {
      const iamRows = await this.dataSource.query<{ name: { en?: string; am?: string } | null }[]>(
        `SELECT name FROM iam.users WHERE id = $1 LIMIT 1`,
        [passenger.iamUserId],
      );
      const name = iamRows[0]?.name;
      firstName = (name?.en ?? name?.am ?? '').split(' ')[0];
    }
    const seat = (upcomingBooking as any)?.seats?.[0];

    return {
      user: { firstName, greetingKey },
      upcomingTicket: upcomingBooking ? {
        ticketId: (upcomingBooking as any).tickets?.[0]?.id, bookingRef: upcomingBooking.bookingRef,
        from: (upcomingBooking as any).schedule.originStation.name, to: (upcomingBooking as any).schedule.destinationStation.name,
        trainName: (upcomingBooking as any).schedule.train.name, coachLabel: seat?.seat.coach.number, seatLabel: seat?.seat.seatNumber,
        departureAt: (upcomingBooking as any).schedule.departureAt,
        punctualityLabel: ((upcomingBooking as any).schedule.liveStatus?.delayMinutes ?? 0) > 0 ? 'DELAYED' : 'ON_TIME',
      } : null,
      wallet: wallet ? { balanceMinor: wallet.balanceMinor, currency: wallet.currency } : null,
      activePromotionsCount: promos,
      weatherAlerts: weatherAlerts.map((w) => ({ id: w.id, title: w.title, message: w.message, severity: w.severity })),
      stationSignals: stationSignals.map((s) => ({ stationId: s.stationId, stationName: s.station.name, level: s.level, statusLabel: s.statusLabel })),
      savedRoutes: savedRoutes.map((r) => ({ id: r.id, fromName: r.fromName, toName: r.toName, tripCount: r.tripCount })),
    };
  }
}
