import { randomUUID } from "crypto";
import type { PoolClient } from "pg";

import {
  calculateBookingTotalPrice,
  bookingIntervalsOverlap,
  normalizeBookingOperationsPlan,
  normalizeBookingTime,
  roundCurrency,
  shiftBookingOperationsPlan,
  type BookingOperationsPlan,
  type RentalBooking
} from "@rental/shared";

import { pool, query } from "../../db/pool.js";
import { ConflictError, NotFoundError, ValidationError } from "../../utils/app-error.js";

type BookingRow = {
  id: string;
  equipment_item_id: string;
  relation_group_key: string;
  customer_name: string;
  order_number: string | null;
  project_number: string | null;
  project_name: string | null;
  notes: string | null;
  start_date: string;
  end_date: string;
  start_time: string | null;
  end_time: string | null;
  operations_plan: BookingOperationsPlan | null;
  day_rate: number | null;
  total_price: number | null;
  created_by: string | null;
  created_by_name: string | null;
  created_at: Date;
  updated_at: Date;
};

type BookingConflictRow = BookingRow & {
  item_name: string;
};

type BookableItemRow = {
  id: string;
  status: "active" | "service" | "retired";
  suggested_day_rate: number | null;
};

type BookingFilters = {
  itemId?: string;
  categoryId?: string;
  customerName?: string;
  from?: string;
  to?: string;
};

const normalizeTextValue = (value?: string | null) => {
  if (typeof value !== "string") {
    return null;
  }

  const trimmed = value.trim();
  return trimmed.length > 0 ? trimmed : null;
};

const normalizeComparableText = (value?: string | null) => normalizeTextValue(value)?.toLowerCase() ?? null;

const normalizePriceValue = (value?: number | null) => {
  if (typeof value !== "number" || !Number.isFinite(value)) {
    return null;
  }

  return roundCurrency(value);
};

const resolveBookingTotalPrice = (payload: {
  startDate: string;
  endDate: string;
  dayRate?: number | null;
  totalPrice?: number | null;
}) => {
  const normalizedTotalPrice = normalizePriceValue(payload.totalPrice);

  if (normalizedTotalPrice !== null) {
    return normalizedTotalPrice;
  }

  return calculateBookingTotalPrice(payload.startDate, payload.endDate, normalizePriceValue(payload.dayRate));
};

const distributeManualTotalPrice = (totalPrice: number, dayRates: Array<number | null>) => {
  if (dayRates.length === 0) {
    return [];
  }

  const positiveRates = dayRates.map((value) => (typeof value === "number" && value > 0 ? value : 0));
  const totalWeight = positiveRates.reduce((sum, value) => sum + value, 0);
  const weights = totalWeight > 0 ? positiveRates : dayRates.map(() => 1);
  const normalizedWeightSum = weights.reduce((sum, value) => sum + value, 0) || dayRates.length;
  const distributed: number[] = [];
  let assigned = 0;

  for (let index = 0; index < weights.length; index += 1) {
    if (index === weights.length - 1) {
      distributed.push(roundCurrency(totalPrice - assigned));
      continue;
    }

    const nextValue = roundCurrency((totalPrice * weights[index]!) / normalizedWeightSum);
    distributed.push(nextValue);
    assigned = roundCurrency(assigned + nextValue);
  }

  return distributed;
};

const padNumber = (value: number) => String(value).padStart(2, "0");

const getAutomaticOrderPrefix = (date = new Date()) =>
  `ZAM-${date.getFullYear()}${padNumber(date.getMonth() + 1)}${padNumber(date.getDate())}`;

const getAutomaticProjectPrefix = (date = new Date()) =>
  `PRJ-${date.getFullYear()}${padNumber(date.getMonth() + 1)}${padNumber(date.getDate())}`;

const generateAutomaticOrderNumber = async (client: PoolClient) => {
  const prefix = getAutomaticOrderPrefix();

  await client.query("select pg_advisory_xact_lock(hashtext($1), 1)", [prefix]);

  const result = await client.query<{ current_sequence: number }>(
    `
      select coalesce(max(right(order_number, 4)::integer), 0) as current_sequence
      from rental_bookings
      where order_number ~ ('^' || $1 || '-[0-9]{4}$')
    `,
    [prefix]
  );

  const nextSequence = (result.rows[0]?.current_sequence ?? 0) + 1;
  return `${prefix}-${String(nextSequence).padStart(4, "0")}`;
};

const generateAutomaticProjectNumber = async (client: PoolClient) => {
  const prefix = getAutomaticProjectPrefix();

  await client.query("select pg_advisory_xact_lock(hashtext($1), 2)", [prefix]);

  const result = await client.query<{ current_sequence: number }>(
    `
      select coalesce(max(right(project_number, 4)::integer), 0) as current_sequence
      from rental_bookings
      where project_number ~ ('^' || $1 || '-[0-9]{4}$')
    `,
    [prefix]
  );

  const nextSequence = (result.rows[0]?.current_sequence ?? 0) + 1;
  return `${prefix}-${String(nextSequence).padStart(4, "0")}`;
};

const generateRelationGroupKey = () => randomUUID();

const mapBooking = (row: BookingRow): RentalBooking => ({
  id: row.id,
  equipmentItemId: row.equipment_item_id,
  relationGroupKey: row.relation_group_key,
  customerName: row.customer_name,
  orderNumber: row.order_number,
  projectNumber: row.project_number,
  projectName: row.project_name,
  notes: row.notes,
  startDate: row.start_date,
  endDate: row.end_date,
  startTime: row.start_time,
  endTime: row.end_time,
  operationsPlan: normalizeBookingOperationsPlan(row.operations_plan, {
    startDate: row.start_date,
    endDate: row.end_date,
    startTime: row.start_time,
    endTime: row.end_time
  }),
  dayRate: row.day_rate,
  totalPrice: row.total_price,
  createdBy: row.created_by,
  createdByName: row.created_by_name,
  createdAt: row.created_at.toISOString(),
  updatedAt: row.updated_at.toISOString()
});

const getBookingRowById = async (id: string) => {
  const result = await query<BookingRow>(
    `
      select
        b.id,
        b.equipment_item_id,
        b.relation_group_key,
        b.customer_name,
        b.order_number,
        b.project_number,
        b.project_name,
        b.notes,
        b.start_date::text,
        b.end_date::text,
        to_char(b.start_time, 'HH24:MI') as start_time,
        to_char(b.end_time, 'HH24:MI') as end_time,
        b.operations_plan,
        b.day_rate::double precision as day_rate,
        b.total_price::double precision as total_price,
        b.created_by,
        u.name as created_by_name,
        b.created_at,
        b.updated_at
      from rental_bookings b
      left join users u on u.id = b.created_by
      where b.id = $1
    `,
    [id]
  );

  const booking = result.rows[0];

  if (!booking) {
    throw new NotFoundError("Booking not found");
  }

  return booking;
};

const getBookableItemsForBooking = async (equipmentItemIds: string[]) => {
  const result = await query<BookableItemRow>(
    `
      select id, status, suggested_day_rate::double precision as suggested_day_rate
      from equipment_items
      where id = any($1::uuid[])
    `,
    [equipmentItemIds]
  );

  if (result.rowCount !== equipmentItemIds.length) {
    throw new ValidationError("At least one selected equipment item does not exist");
  }

  const inactive = result.rows.filter((row) => row.status !== "active");

  if (inactive.length > 0) {
    throw new ConflictError("Only active equipment items can be booked");
  }

  return result.rows;
};

const isExactDuplicateBooking = (
  row: Pick<BookingRow, "equipment_item_id" | "customer_name" | "start_date" | "end_date" | "start_time" | "end_time">,
  payload: {
    equipmentItemId: string;
    customerName: string;
    startDate: string;
    endDate: string;
    startTime?: string | null;
    endTime?: string | null;
  }
) =>
  row.equipment_item_id === payload.equipmentItemId &&
  normalizeComparableText(row.customer_name) === normalizeComparableText(payload.customerName) &&
  row.start_date === payload.startDate &&
  row.end_date === payload.endDate &&
  (row.start_time ?? null) === (payload.startTime ?? null) &&
  (row.end_time ?? null) === (payload.endTime ?? null);

const findConflicts = async (equipmentItemIds: string[], startDate: string, endDate: string, excludeBookingId?: string) => {
  const params: unknown[] = [equipmentItemIds, startDate, endDate];
  const conditions = [
    "b.equipment_item_id = any($1::uuid[])",
    "b.start_date <= $3::date",
    "b.end_date >= $2::date"
  ];

  if (excludeBookingId) {
    params.push(excludeBookingId);
    conditions.push(`b.id <> $${params.length}`);
  }

  const result = await query<BookingConflictRow>(
    `
      select
        b.id,
        b.equipment_item_id,
        b.relation_group_key,
        b.customer_name,
        b.order_number,
        b.project_number,
        b.project_name,
        b.notes,
        b.start_date::text,
        b.end_date::text,
        to_char(b.start_time, 'HH24:MI') as start_time,
        to_char(b.end_time, 'HH24:MI') as end_time,
        b.operations_plan,
        b.day_rate::double precision as day_rate,
        b.total_price::double precision as total_price,
        b.created_by,
        u.name as created_by_name,
        b.created_at,
        b.updated_at,
        i.name as item_name
      from rental_bookings b
      join equipment_items i on i.id = b.equipment_item_id
      left join users u on u.id = b.created_by
      where ${conditions.join(" and ")}
    `,
    params
  );

  return result.rows;
};

export const listBookings = async (filters: BookingFilters) => {
  const params: unknown[] = [];
  const conditions: string[] = [];

  if (filters.itemId) {
    params.push(filters.itemId);
    conditions.push(`b.equipment_item_id = $${params.length}`);
  }

  if (filters.categoryId) {
    params.push(filters.categoryId);
    conditions.push(`i.category_id = $${params.length}`);
  }

  if (filters.customerName?.trim()) {
    params.push(`%${filters.customerName.trim().toLowerCase()}%`);
    conditions.push(`lower(b.customer_name) like $${params.length}`);
  }

  if (filters.from) {
    params.push(filters.from);
    conditions.push(`b.end_date >= $${params.length}::date`);
  }

  if (filters.to) {
    params.push(filters.to);
    conditions.push(`b.start_date <= $${params.length}::date`);
  }

  const whereClause = conditions.length ? `where ${conditions.join(" and ")}` : "";

  const result = await query<BookingRow>(
    `
      select
        b.id,
        b.equipment_item_id,
        b.relation_group_key,
        b.customer_name,
        b.order_number,
        b.project_number,
        b.project_name,
        b.notes,
        b.start_date::text,
        b.end_date::text,
        to_char(b.start_time, 'HH24:MI') as start_time,
        to_char(b.end_time, 'HH24:MI') as end_time,
        b.operations_plan,
        b.day_rate::double precision as day_rate,
        b.total_price::double precision as total_price,
        b.created_by,
        u.name as created_by_name,
        b.created_at,
        b.updated_at
      from rental_bookings b
      join equipment_items i on i.id = b.equipment_item_id
      left join users u on u.id = b.created_by
      ${whereClause}
      order by b.start_date asc, b.customer_name asc
    `,
    params
  );

  return result.rows.map(mapBooking);
};

export const getBookingById = async (id: string) => mapBooking(await getBookingRowById(id));

export const createBookings = async (payload: {
  equipmentItemIds: string[];
  customerName: string;
  orderNumber?: string | null;
  projectNumber?: string | null;
  projectName?: string | null;
  notes?: string | null;
  startDate: string;
  endDate: string;
  startTime?: string | null;
  endTime?: string | null;
  operationsPlan?: BookingOperationsPlan | null;
  dayRate?: number | null;
  totalPrice?: number | null;
  useSuggestedItemRates?: boolean;
  allowConflict?: boolean;
  createdBy: string | null;
}) => {
  const bookableItems = await getBookableItemsForBooking(payload.equipmentItemIds);

  const normalizedStartTime = normalizeBookingTime(payload.startTime);
  const normalizedEndTime = normalizeBookingTime(payload.endTime);
  const conflicts = (await findConflicts(payload.equipmentItemIds, payload.startDate, payload.endDate)).filter((row) =>
    bookingIntervalsOverlap(
      {
        startDate: row.start_date,
        endDate: row.end_date,
        startTime: row.start_time,
        endTime: row.end_time
      },
      {
        startDate: payload.startDate,
        endDate: payload.endDate,
        startTime: normalizedStartTime,
        endTime: normalizedEndTime
      }
    )
  );

  const exactDuplicates = conflicts.filter((row) =>
    isExactDuplicateBooking(row, {
      equipmentItemId: row.equipment_item_id,
      customerName: payload.customerName,
      startDate: payload.startDate,
      endDate: payload.endDate,
      startTime: normalizedStartTime,
      endTime: normalizedEndTime
    })
  );

  if (exactDuplicates.length > 0) {
    throw new ConflictError("An identical booking already exists for this equipment item and time range", {
      conflicts: exactDuplicates.map((row) => ({
        id: row.id,
        equipmentItemId: row.equipment_item_id,
        itemName: row.item_name,
        customerName: row.customer_name,
        startDate: row.start_date,
        endDate: row.end_date,
        startTime: row.start_time,
        endTime: row.end_time
      }))
    });
  }

  if (conflicts.length > 0 && !payload.allowConflict) {
    throw new ConflictError("Selected range overlaps with an existing booking", {
      conflicts: conflicts.map((row) => ({
        id: row.id,
        equipmentItemId: row.equipment_item_id,
        itemName: row.item_name,
        customerName: row.customer_name,
        startDate: row.start_date,
        endDate: row.end_date,
        startTime: row.start_time,
        endTime: row.end_time
      }))
    });
  }

  const resolvedOrderNumber = normalizeTextValue(payload.orderNumber);
  const resolvedProjectNumber = normalizeTextValue(payload.projectNumber);
  const normalizedDayRate = normalizePriceValue(payload.dayRate);
  const normalizedManualTotalPrice = normalizePriceValue(payload.totalPrice);
  const perItemDayRates =
    normalizedDayRate !== null
      ? payload.equipmentItemIds.map(() => normalizedDayRate)
      : payload.useSuggestedItemRates
        ? payload.equipmentItemIds.map(
            (equipmentItemId) => normalizePriceValue(bookableItems.find((item) => item.id === equipmentItemId)?.suggested_day_rate)
          )
        : payload.equipmentItemIds.map(() => null);
  const perItemTotalPrices =
    normalizedManualTotalPrice !== null
      ? payload.equipmentItemIds.length === 1
        ? [normalizedManualTotalPrice]
        : distributeManualTotalPrice(normalizedManualTotalPrice, perItemDayRates)
      : perItemDayRates.map((itemDayRate) =>
          resolveBookingTotalPrice({
            startDate: payload.startDate,
            endDate: payload.endDate,
            dayRate: itemDayRate,
            totalPrice: null
          })
        );
  const created = [];
  const client = await pool.connect();

  try {
    await client.query("begin");

    const finalOrderNumber = resolvedOrderNumber ?? (await generateAutomaticOrderNumber(client));
    const finalProjectNumber = resolvedProjectNumber ?? (await generateAutomaticProjectNumber(client));
    const finalRelationGroupKey = generateRelationGroupKey();

    for (const [index, equipmentItemId] of payload.equipmentItemIds.entries()) {
      const itemDayRate = perItemDayRates[index] ?? null;
      const itemTotalPrice = perItemTotalPrices[index] ?? null;
      const result = await client.query<BookingRow>(
        `
          insert into rental_bookings (
            equipment_item_id,
            relation_group_key,
            customer_name,
            order_number,
            project_number,
            project_name,
            notes,
            start_date,
            end_date,
            start_time,
            end_time,
            operations_plan,
            day_rate,
            total_price,
            created_by
          )
          values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
          returning
            id,
            equipment_item_id,
            relation_group_key,
            customer_name,
            order_number,
            project_number,
            project_name,
            notes,
            start_date::text,
            end_date::text,
            to_char(start_time, 'HH24:MI') as start_time,
            to_char(end_time, 'HH24:MI') as end_time,
            operations_plan,
            day_rate::double precision as day_rate,
            total_price::double precision as total_price,
            created_by,
            (
              select name from users where id = created_by
            ) as created_by_name,
            created_at,
            updated_at
        `,
        [
          equipmentItemId,
          finalRelationGroupKey,
          payload.customerName.trim(),
          finalOrderNumber,
          finalProjectNumber,
          normalizeTextValue(payload.projectName),
          normalizeTextValue(payload.notes),
          payload.startDate,
          payload.endDate,
          normalizedStartTime,
          normalizedEndTime,
          JSON.stringify(
            normalizeBookingOperationsPlan(payload.operationsPlan, {
              startDate: payload.startDate,
              endDate: payload.endDate,
              startTime: normalizedStartTime,
              endTime: normalizedEndTime
            })
          ),
          itemDayRate,
          itemTotalPrice,
          payload.createdBy
        ]
      );

      const row = result.rows[0];

      if (!row) {
        throw new Error("Booking insert did not return a row");
      }

      created.push(mapBooking(row));
    }

    await client.query("commit");
  } catch (error) {
    await client.query("rollback");
    throw error;
  } finally {
    client.release();
  }

  return created;
};

export const updateBooking = async (
  id: string,
  payload: {
    equipmentItemId: string;
    customerName: string;
    orderNumber?: string | null;
    projectNumber?: string | null;
    projectName?: string | null;
    notes?: string | null;
    startDate: string;
    endDate: string;
    startTime?: string | null;
    endTime?: string | null;
    operationsPlan?: BookingOperationsPlan | null;
    dayRate?: number | null;
    totalPrice?: number | null;
    allowConflict?: boolean;
  }
) => {
  const currentBooking = await getBookingRowById(id);
  await getBookableItemsForBooking([payload.equipmentItemId]);

  if (currentBooking.project_number) {
    const currentScopeRows = await listProjectScopeRows(currentBooking);
    const duplicateInProject = currentScopeRows.find(
      (row) => row.id !== currentBooking.id && row.equipment_item_id === payload.equipmentItemId
    );

    if (duplicateInProject) {
      throw new ConflictError("This equipment item is already assigned inside the same project task");
    }
  }

  const normalizedStartTime = normalizeBookingTime(payload.startTime);
  const normalizedEndTime = normalizeBookingTime(payload.endTime);
  const conflicts = (await findConflicts([payload.equipmentItemId], payload.startDate, payload.endDate, id)).filter((row) =>
    bookingIntervalsOverlap(
      {
        startDate: row.start_date,
        endDate: row.end_date,
        startTime: row.start_time,
        endTime: row.end_time
      },
      {
        startDate: payload.startDate,
        endDate: payload.endDate,
        startTime: normalizedStartTime,
        endTime: normalizedEndTime
      }
    )
  );

  const exactDuplicates = conflicts.filter((row) =>
    isExactDuplicateBooking(row, {
      equipmentItemId: payload.equipmentItemId,
      customerName: payload.customerName,
      startDate: payload.startDate,
      endDate: payload.endDate,
      startTime: normalizedStartTime,
      endTime: normalizedEndTime
    })
  );

  if (exactDuplicates.length > 0) {
    throw new ConflictError("An identical booking already exists for this equipment item and time range", {
      conflicts: exactDuplicates.map((row) => ({
        id: row.id,
        equipmentItemId: row.equipment_item_id,
        itemName: row.item_name,
        customerName: row.customer_name,
        startDate: row.start_date,
        endDate: row.end_date,
        startTime: row.start_time,
        endTime: row.end_time
      }))
    });
  }

  if (conflicts.length > 0 && !payload.allowConflict) {
    throw new ConflictError("Updated booking overlaps with an existing booking", {
      conflicts: conflicts.map((row) => ({
        id: row.id,
        equipmentItemId: row.equipment_item_id,
        itemName: row.item_name,
        customerName: row.customer_name,
        startDate: row.start_date,
        endDate: row.end_date,
        startTime: row.start_time,
        endTime: row.end_time
      }))
    });
  }

  const resolvedOrderNumber = normalizeTextValue(payload.orderNumber);
  const resolvedProjectNumber = normalizeTextValue(payload.projectNumber);
  const normalizedDayRate = normalizePriceValue(payload.dayRate);
  const resolvedOperationsPlan =
    payload.operationsPlan !== undefined
      ? normalizeBookingOperationsPlan(payload.operationsPlan, {
          startDate: payload.startDate,
          endDate: payload.endDate,
          startTime: normalizedStartTime,
          endTime: normalizedEndTime
        })
      : shiftBookingOperationsPlan(
          currentBooking.operations_plan,
          {
            startDate: currentBooking.start_date,
            endDate: currentBooking.end_date,
            startTime: currentBooking.start_time,
            endTime: currentBooking.end_time
          },
          {
            startDate: payload.startDate,
            endDate: payload.endDate,
            startTime: normalizedStartTime,
            endTime: normalizedEndTime
          }
        ) ??
        normalizeBookingOperationsPlan(null, {
          startDate: payload.startDate,
          endDate: payload.endDate,
          startTime: normalizedStartTime,
          endTime: normalizedEndTime
        });
  const resolvedTotalPrice = resolveBookingTotalPrice({
    startDate: payload.startDate,
    endDate: payload.endDate,
    dayRate: normalizedDayRate,
    totalPrice: payload.totalPrice
  });
  const client = await pool.connect();

  try {
    await client.query("begin");

    const finalOrderNumber = resolvedOrderNumber ?? (await generateAutomaticOrderNumber(client));
    const finalProjectNumber = resolvedProjectNumber ?? currentBooking.project_number ?? (await generateAutomaticProjectNumber(client));

    const result = await client.query<BookingRow>(
      `
        update rental_bookings
        set
          equipment_item_id = $2,
          customer_name = $3,
          order_number = $4,
          project_number = $5,
          project_name = $6,
          notes = $7,
          start_date = $8,
          end_date = $9,
          start_time = $10,
          end_time = $11,
          operations_plan = $12,
          day_rate = $13,
          total_price = $14,
          updated_at = now()
        where id = $1
        returning
          id,
          equipment_item_id,
          relation_group_key,
          customer_name,
          order_number,
          project_number,
          project_name,
          notes,
          start_date::text,
          end_date::text,
          to_char(start_time, 'HH24:MI') as start_time,
          to_char(end_time, 'HH24:MI') as end_time,
          operations_plan,
          day_rate::double precision as day_rate,
          total_price::double precision as total_price,
          created_by,
          (
            select name from users where id = created_by
          ) as created_by_name,
          created_at,
          updated_at
      `,
      [
        id,
        payload.equipmentItemId,
        payload.customerName.trim(),
        finalOrderNumber,
        finalProjectNumber,
        normalizeTextValue(payload.projectName),
        normalizeTextValue(payload.notes),
        payload.startDate,
        payload.endDate,
        normalizedStartTime,
        normalizedEndTime,
        JSON.stringify(resolvedOperationsPlan),
        normalizedDayRate,
        resolvedTotalPrice
      ]
    );

    const row = result.rows[0];

    if (!row) {
      throw new Error("Booking update did not return a row");
    }

    await client.query("commit");
    return mapBooking(row);
  } catch (error) {
    await client.query("rollback");
    throw error;
  } finally {
    client.release();
  }
};

const listProjectGroupRows = async (projectNumber: string) => {
  const result = await query<BookingRow>(
    `
      select
        b.id,
        b.equipment_item_id,
        b.relation_group_key,
        b.customer_name,
        b.order_number,
        b.project_number,
        b.project_name,
        b.notes,
        b.start_date::text,
        b.end_date::text,
        to_char(b.start_time, 'HH24:MI') as start_time,
        to_char(b.end_time, 'HH24:MI') as end_time,
        b.operations_plan,
        b.day_rate::double precision as day_rate,
        b.total_price::double precision as total_price,
        b.created_by,
        u.name as created_by_name,
        b.created_at,
        b.updated_at
      from rental_bookings b
      left join users u on u.id = b.created_by
      where b.project_number = $1
      order by b.created_at asc, b.id asc
    `,
    [projectNumber]
  );

  return result.rows;
};

const bookingMatchesProjectScope = (
  candidate: Pick<BookingRow, "project_number" | "relation_group_key" | "start_date" | "end_date" | "start_time" | "end_time">,
  anchor: Pick<BookingRow, "project_number" | "relation_group_key" | "start_date" | "end_date" | "start_time" | "end_time">
) =>
  Boolean(anchor.relation_group_key || anchor.project_number) &&
  (candidate.relation_group_key === anchor.relation_group_key ||
    (candidate.project_number === anchor.project_number &&
      candidate.start_date === anchor.start_date &&
      candidate.end_date === anchor.end_date &&
      (candidate.start_time ?? null) === (anchor.start_time ?? null) &&
      (candidate.end_time ?? null) === (anchor.end_time ?? null))) &&
  candidate.start_date === anchor.start_date &&
  candidate.end_date === anchor.end_date &&
  (candidate.start_time ?? null) === (anchor.start_time ?? null) &&
  (candidate.end_time ?? null) === (anchor.end_time ?? null);

const listProjectScopeRows = async (anchorBooking: BookingRow) => {
  if (!anchorBooking.project_number) {
    return [anchorBooking];
  }

  const projectRows = await listProjectGroupRows(anchorBooking.project_number);
  const scopeRows = projectRows.filter((row) => bookingMatchesProjectScope(row, anchorBooking));
  return scopeRows.length > 0 ? scopeRows : [anchorBooking];
};

const listExplicitScopeRows = async (anchorBooking: BookingRow, scopeBookingIds?: string[]) => {
  const explicitScopeIds = Array.from(new Set((scopeBookingIds ?? []).filter(Boolean)));

  if (explicitScopeIds.length === 0) {
    return listProjectScopeRows(anchorBooking);
  }

  const projectRows = anchorBooking.project_number
    ? await listProjectGroupRows(anchorBooking.project_number)
    : [anchorBooking];
  const explicitScopeSet = new Set(explicitScopeIds);
  const explicitRows = projectRows.filter((row) => explicitScopeSet.has(row.id));

  if (explicitRows.some((row) => row.id === anchorBooking.id)) {
    return explicitRows;
  }

  return explicitRows.length > 0 ? [anchorBooking, ...explicitRows] : [anchorBooking];
};

export const detachBookingFromProject = async (id: string, scopeBookingIds?: string[]) => {
  const anchorBooking = await getBookingRowById(id);

  if (!anchorBooking.project_number) {
    return mapBooking(anchorBooking);
  }

  const currentGroupRows = await listExplicitScopeRows(anchorBooking, scopeBookingIds);

  if (currentGroupRows.length <= 1) {
    return mapBooking(anchorBooking);
  }

  const client = await pool.connect();

  try {
    await client.query("begin");

    for (const row of currentGroupRows) {
      await client.query(
        `
          update rental_bookings
          set
            relation_group_key = $2,
            updated_at = now()
          where id = $1
        `,
        [row.id, generateRelationGroupKey()]
      );
    }

    await client.query("commit");
    return mapBooking(await getBookingRowById(id));
  } catch (error) {
    await client.query("rollback");
    throw error;
  } finally {
    client.release();
  }
};

export const syncBookingProjectScope = async (
  id: string,
  payload: {
    equipmentItemIds: string[];
    scopeBookingIds?: string[];
    startDate?: string;
    endDate?: string;
    allowConflict?: boolean;
  }
) => {
  const anchorBooking = await getBookingRowById(id);
  const uniqueEquipmentItemIds = Array.from(new Set(payload.equipmentItemIds));
  const targetStartDate = payload.startDate ?? anchorBooking.start_date;
  const targetEndDate = payload.endDate ?? anchorBooking.end_date;
  const currentGroupRows = await listExplicitScopeRows(anchorBooking, payload.scopeBookingIds);
  const currentGroupIds = new Set(currentGroupRows.map((row) => row.id));

  await getBookableItemsForBooking(uniqueEquipmentItemIds);

  const conflicts = (await findConflicts(uniqueEquipmentItemIds, targetStartDate, targetEndDate)).filter(
    (row) =>
      !currentGroupIds.has(row.id) &&
      bookingIntervalsOverlap(
        {
          startDate: row.start_date,
          endDate: row.end_date,
          startTime: row.start_time,
          endTime: row.end_time
        },
        {
          startDate: targetStartDate,
          endDate: targetEndDate,
          startTime: anchorBooking.start_time,
          endTime: anchorBooking.end_time
        }
      )
  );

  if (conflicts.length > 0 && !payload.allowConflict) {
    throw new ConflictError("Selected range overlaps with an existing booking", {
      conflicts: conflicts.map((row) => ({
        id: row.id,
        equipmentItemId: row.equipment_item_id,
        itemName: row.item_name,
        customerName: row.customer_name,
        startDate: row.start_date,
        endDate: row.end_date,
        startTime: row.start_time,
        endTime: row.end_time
      }))
    });
  }

  const client = await pool.connect();
  let finalProjectNumber: string | null = anchorBooking.project_number;
  let finalRelationGroupKey: string = anchorBooking.relation_group_key || generateRelationGroupKey();
  const resolvedScopeOperationsPlan = shiftBookingOperationsPlan(
    anchorBooking.operations_plan,
    {
      startDate: anchorBooking.start_date,
      endDate: anchorBooking.end_date,
      startTime: anchorBooking.start_time,
      endTime: anchorBooking.end_time
    },
    {
      startDate: targetStartDate,
      endDate: targetEndDate,
      startTime: anchorBooking.start_time,
      endTime: anchorBooking.end_time
    }
  ) ??
    normalizeBookingOperationsPlan(null, {
      startDate: targetStartDate,
      endDate: targetEndDate,
      startTime: anchorBooking.start_time,
      endTime: anchorBooking.end_time
    });

  try {
    await client.query("begin");

    finalProjectNumber = anchorBooking.project_number ?? (await generateAutomaticProjectNumber(client));
    finalRelationGroupKey = anchorBooking.relation_group_key || generateRelationGroupKey();
    const prioritizedCurrentGroupRows = [
      ...currentGroupRows.filter((row) => row.id === anchorBooking.id),
      ...currentGroupRows.filter((row) => row.id !== anchorBooking.id)
    ];
    const existingByItemId = new Map<string, BookingRow>();
    const duplicateRowIds: string[] = [];

    for (const row of prioritizedCurrentGroupRows) {
      if (existingByItemId.has(row.equipment_item_id)) {
        duplicateRowIds.push(row.id);
        continue;
      }

      existingByItemId.set(row.equipment_item_id, row);
    }

    for (const equipmentItemId of uniqueEquipmentItemIds) {
      const existingRow = existingByItemId.get(equipmentItemId);

      if (existingRow) {
        await client.query(
          `
            update rental_bookings
            set
              project_number = $2,
              relation_group_key = $3,
              customer_name = $4,
              order_number = $5,
              project_name = $6,
              notes = $7,
            start_date = $8,
            end_date = $9,
            start_time = $10,
            end_time = $11,
              operations_plan = $12,
              day_rate = $13,
              total_price = $14,
              updated_at = now()
            where id = $1
          `,
          [
            existingRow.id,
            finalProjectNumber,
            finalRelationGroupKey,
            anchorBooking.customer_name,
            anchorBooking.order_number,
            anchorBooking.project_name,
            anchorBooking.notes,
            targetStartDate,
            targetEndDate,
            anchorBooking.start_time,
            anchorBooking.end_time,
            JSON.stringify(resolvedScopeOperationsPlan),
            anchorBooking.day_rate,
            anchorBooking.total_price
          ]
        );
        continue;
      }

      await client.query(
        `
          insert into rental_bookings (
            equipment_item_id,
            relation_group_key,
            customer_name,
            order_number,
            project_number,
            project_name,
            notes,
            start_date,
            end_date,
            start_time,
            end_time,
            operations_plan,
            day_rate,
            total_price,
            created_by
          )
          values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
        `,
        [
          equipmentItemId,
          finalRelationGroupKey,
          anchorBooking.customer_name,
          anchorBooking.order_number,
          finalProjectNumber,
          anchorBooking.project_name,
          anchorBooking.notes,
          targetStartDate,
          targetEndDate,
          anchorBooking.start_time,
          anchorBooking.end_time,
          JSON.stringify(resolvedScopeOperationsPlan),
          anchorBooking.day_rate,
          anchorBooking.total_price,
          anchorBooking.created_by
        ]
      );
    }

    const removedIds = Array.from(
      new Set([
        ...duplicateRowIds,
        ...currentGroupRows
          .filter((row) => !uniqueEquipmentItemIds.includes(row.equipment_item_id))
          .map((row) => row.id)
      ])
    );

    if (removedIds.length > 0) {
      await client.query("delete from rental_bookings where id = any($1::uuid[])", [removedIds]);
    }

    await client.query("commit");
  } catch (error) {
    await client.query("rollback");
    throw error;
  } finally {
    client.release();
  }

  if (!finalProjectNumber) {
    return [];
  }

  const refreshedProjectRows = finalProjectNumber ? await listProjectGroupRows(finalProjectNumber) : [];
  const refreshedRows = refreshedProjectRows.filter(
    (row) =>
      row.start_date === targetStartDate &&
      row.end_date === targetEndDate &&
      (row.start_time ?? null) === (anchorBooking.start_time ?? null) &&
      (row.end_time ?? null) === (anchorBooking.end_time ?? null) &&
      uniqueEquipmentItemIds.includes(row.equipment_item_id)
  );

  return (refreshedRows.length > 0 ? refreshedRows : [anchorBooking]).map(mapBooking);
};

export const deleteBooking = async (id: string) => {
  const result = await query<{ id: string }>("delete from rental_bookings where id = $1 returning id", [id]);

  if (!result.rowCount) {
    throw new NotFoundError("Booking not found");
  }
};

export const deleteBookingScope = async (id: string) => {
  const anchorBooking = await getBookingRowById(id);
  const scopeRows = await listProjectScopeRows(anchorBooking);
  const scopeIds = Array.from(new Set(scopeRows.map((row) => row.id)));
  const result = await query<{ id: string }>("delete from rental_bookings where id = any($1::uuid[]) returning id", [scopeIds]);

  if (!result.rowCount) {
    throw new NotFoundError("Booking not found");
  }

  return result.rowCount;
};