import { addDays, dayDiffInclusive, normalizeEquipmentModelName, roundCurrency, type StatsResponse } from "@rental/shared";

import { query } from "../../db/pool.js";

type StatsBookingRow = {
  customer_name: string;
  start_date: string;
  end_date: string;
  day_rate: number | null;
  total_price: number | null;
  suggested_day_rate: number | null;
  item_name: string;
  item_short_name: string | null;
  category_id: string;
  category_name: string;
};

type UserClickRow = {
  user_id: string;
  user_name: string;
  click_date: string;
  click_count: number;
};

const currentDate = () => new Date().toISOString().slice(0, 10);

const startOfMonth = (date: string) => `${date.slice(0, 7)}-01`;

const endOfMonth = (date: string) => {
  const monthStart = startOfMonth(date);
  const nextMonthSeed = addDays(`${monthStart.slice(0, 4)}-${monthStart.slice(5, 7)}-28`, 4);
  const nextMonthStart = `${nextMonthSeed.slice(0, 7)}-01`;
  return addDays(nextMonthStart, -1);
};

const getBookingBaseRevenue = (
  booking: Pick<StatsBookingRow, "day_rate" | "end_date" | "start_date" | "suggested_day_rate" | "total_price">
) => {
  const days = dayDiffInclusive(booking.start_date, booking.end_date);

  if (booking.total_price !== null) {
    return booking.total_price;
  }

  const rate = booking.day_rate ?? booking.suggested_day_rate;

  if (rate !== null) {
    return rate * days;
  }

  return 0;
};

const getOverlapDays = (booking: Pick<StatsBookingRow, "end_date" | "start_date">, from: string, to: string) => {
  const overlapStart = booking.start_date > from ? booking.start_date : from;
  const overlapEnd = booking.end_date < to ? booking.end_date : to;

  if (overlapStart > overlapEnd) {
    return 0;
  }

  return dayDiffInclusive(overlapStart, overlapEnd);
};

const loadBookingRows = async (from?: string, to?: string) => {
  const whereClause =
    from && to
      ? `
        where daterange(b.start_date, b.end_date, '[]') && daterange($1::date, $2::date, '[]')
      `
      : "";
  const params = from && to ? [from, to] : [];

  const result = await query<StatsBookingRow>(
    `
      select
        b.customer_name,
        b.start_date::text,
        b.end_date::text,
        b.day_rate::double precision as day_rate,
        b.total_price::double precision as total_price,
        i.suggested_day_rate::double precision as suggested_day_rate,
        i.name as item_name,
        i.short_name as item_short_name,
        c.id as category_id,
        c.name as category_name
      from rental_bookings b
      join equipment_items i on i.id = b.equipment_item_id
      join equipment_categories c on c.id = i.category_id
      ${whereClause}
    `,
    params
  );

  return result.rows;
};

export const getStats = async (input: { from?: string; to?: string }): Promise<StatsResponse> => {
  const today = currentDate();
  const from = input.from && input.from.length === 10 ? input.from : startOfMonth(today);
  const to = input.to && input.to.length === 10 ? input.to : endOfMonth(today);

  const [selectedBookings, lifetimeBookings, userClicksResult] = await Promise.all([
    loadBookingRows(from, to),
    loadBookingRows(),
    query<UserClickRow>(
      `
        select
          clicks.user_id,
          users.name as user_name,
          clicks.click_date::text,
          clicks.click_count
        from user_click_daily clicks
        join users on users.id = clicks.user_id
        where clicks.click_date between $1::date and $2::date
        order by clicks.click_date desc, users.name asc
      `,
      [from, to]
    )
  ]);

  const lifetimeRevenueByProduct = new Map<string, number>();
  const lifetimeRevenueByCategory = new Map<string, number>();

  for (const booking of lifetimeBookings) {
    const normalizedProductName = normalizeEquipmentModelName(booking.item_name);
    const productKey = `${booking.category_id}::${normalizedProductName.toLowerCase()}`;
    const revenue = getBookingBaseRevenue(booking);

    lifetimeRevenueByProduct.set(productKey, roundCurrency((lifetimeRevenueByProduct.get(productKey) ?? 0) + revenue));
    lifetimeRevenueByCategory.set(
      booking.category_id,
      roundCurrency((lifetimeRevenueByCategory.get(booking.category_id) ?? 0) + revenue)
    );
  }

  const revenueByProduct = new Map<string, StatsResponse["revenueByProduct"][number]>();
  const revenueByCategory = new Map<string, StatsResponse["revenueByCategory"][number]>();
  const revenueByCustomer = new Map<string, StatsResponse["revenueByCustomer"][number]>();
  let totalRevenue = 0;

  for (const booking of selectedBookings) {
    const normalizedProductName = normalizeEquipmentModelName(booking.item_name);
    const productKey = `${booking.category_id}::${normalizedProductName.toLowerCase()}`;
    const customerKey = booking.customer_name.trim().toLowerCase();
    const totalDays = dayDiffInclusive(booking.start_date, booking.end_date);
    const overlapDays = getOverlapDays(booking, from, to);
    const revenue = totalDays > 0 ? (getBookingBaseRevenue(booking) / totalDays) * overlapDays : 0;

    totalRevenue += revenue;

    const currentProduct =
      revenueByProduct.get(productKey) ??
      {
        categoryId: booking.category_id,
        categoryName: booking.category_name,
        productKey,
        productName: normalizedProductName,
        productShortName: booking.item_short_name,
        bookingCount: 0,
        revenue: 0,
        lifetimeRevenue: lifetimeRevenueByProduct.get(productKey) ?? 0
      };
    currentProduct.bookingCount += 1;
    currentProduct.revenue = roundCurrency(currentProduct.revenue + revenue);
    currentProduct.productShortName = currentProduct.productShortName ?? booking.item_short_name;
    revenueByProduct.set(productKey, currentProduct);

    const currentCategory =
      revenueByCategory.get(booking.category_id) ??
      {
        categoryId: booking.category_id,
        categoryName: booking.category_name,
        bookingCount: 0,
        revenue: 0,
        lifetimeRevenue: lifetimeRevenueByCategory.get(booking.category_id) ?? 0
      };
    currentCategory.bookingCount += 1;
    currentCategory.revenue = roundCurrency(currentCategory.revenue + revenue);
    revenueByCategory.set(booking.category_id, currentCategory);

    const currentCustomer =
      revenueByCustomer.get(customerKey) ??
      {
        customerName: booking.customer_name,
        bookingCount: 0,
        revenue: 0
      };
    currentCustomer.bookingCount += 1;
    currentCustomer.revenue = roundCurrency(currentCustomer.revenue + revenue);
    revenueByCustomer.set(customerKey, currentCustomer);
  }

  return {
    from,
    to,
    totalRevenue: roundCurrency(totalRevenue),
    revenueByProduct: Array.from(revenueByProduct.values()).sort(
      (left, right) => right.revenue - left.revenue || left.productName.localeCompare(right.productName, "pl")
    ),
    revenueByCategory: Array.from(revenueByCategory.values()).sort(
      (left, right) => right.revenue - left.revenue || left.categoryName.localeCompare(right.categoryName, "pl")
    ),
    revenueByCustomer: Array.from(revenueByCustomer.values()).sort(
      (left, right) => right.revenue - left.revenue || left.customerName.localeCompare(right.customerName, "pl")
    ),
    userClicks: userClicksResult.rows.map((row) => ({
      userId: row.user_id,
      userName: row.user_name,
      clickDate: row.click_date,
      clickCount: row.click_count
    }))
  };
};

export const recordUserClicks = async (userId: string, count: number) => {
  await query(
    `
      insert into user_click_daily (user_id, click_date, click_count)
      values ($1, current_date, $2)
      on conflict (user_id, click_date)
      do update
      set
        click_count = user_click_daily.click_count + excluded.click_count,
        updated_at = now()
    `,
    [userId, count]
  );
};