import { normalizeEquipmentModelName, type EquipmentItem } from "@rental/shared";

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

type ItemRow = {
  id: string;
  category_id: string;
  category_name: string;
  name: string;
  short_name: string | null;
  use_short_name_on_timeline: boolean;
  serial_number: string | null;
  asset_tag: string | null;
  suggested_day_rate: number | null;
  status: "active" | "service" | "retired";
  notes: string | null;
  is_visible_on_timeline: boolean;
  created_at: Date;
  updated_at: Date;
};

type ItemFilters = {
  categoryId?: string;
  status?: "all" | "active" | "service" | "retired";
  search?: string;
  includeInactive?: boolean;
};

type ItemNameRow = {
  id: string;
  name: string;
};

const mapItem = (row: ItemRow): EquipmentItem => ({
  id: row.id,
  categoryId: row.category_id,
  categoryName: row.category_name,
  name: row.name,
  shortName: row.short_name,
  useShortNameOnTimeline: row.use_short_name_on_timeline,
  serialNumber: row.serial_number,
  assetTag: row.asset_tag,
  suggestedDayRate: row.suggested_day_rate,
  status: row.status,
  notes: row.notes,
  isVisibleOnTimeline: row.is_visible_on_timeline,
  createdAt: row.created_at.toISOString(),
  updatedAt: row.updated_at.toISOString()
});

const getItemById = async (id: string) => {
  const result = await query<ItemRow>(
    `
      select
        i.id,
        i.category_id,
        c.name as category_name,
        i.name,
        i.short_name,
        i.use_short_name_on_timeline,
        i.serial_number,
        i.asset_tag,
        i.suggested_day_rate::double precision as suggested_day_rate,
        i.status,
        i.notes,
        i.is_visible_on_timeline,
        i.created_at,
        i.updated_at
      from equipment_items i
      join equipment_categories c on c.id = i.category_id
      where i.id = $1
    `,
    [id]
  );

  const item = result.rows[0];

  if (!item) {
    throw new NotFoundError("Equipment item not found");
  }

  return item;
};

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

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

  if (filters.status && filters.status !== "all") {
    params.push(filters.status);
    conditions.push(`i.status = $${params.length}::equipment_status`);
  } else if (!filters.includeInactive) {
    conditions.push(`i.status = 'active'`);
  }

  if (filters.search?.trim()) {
    params.push(`%${filters.search.trim().toLowerCase()}%`);
    conditions.push(`
      (
        lower(i.name) like $${params.length}
        or lower(coalesce(i.short_name, '')) like $${params.length}
        or lower(coalesce(i.serial_number, '')) like $${params.length}
        or lower(coalesce(i.asset_tag, '')) like $${params.length}
      )
    `);
  }

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

  const result = await query<ItemRow>(
    `
      select
        i.id,
        i.category_id,
        c.name as category_name,
        i.name,
        i.short_name,
        i.use_short_name_on_timeline,
        i.serial_number,
        i.asset_tag,
        i.suggested_day_rate::double precision as suggested_day_rate,
        i.status,
        i.notes,
        i.is_visible_on_timeline,
        i.created_at,
        i.updated_at
      from equipment_items i
      join equipment_categories c on c.id = i.category_id
      ${whereClause}
      order by
        case c.name
          when 'Laptopy' then 1
          when 'Tablety' then 2
          when 'Akcesoria' then 3
          else 99
        end,
        c.name asc,
        i.name asc
    `,
    params
  );

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

export const createItem = async (payload: {
  categoryId: string;
  name: string;
  shortName?: string | null;
  useShortNameOnTimeline: boolean;
  serialNumber?: string | null;
  assetTag?: string | null;
  suggestedDayRate: number | null;
  status: "active" | "service" | "retired";
  notes?: string | null;
  isVisibleOnTimeline: boolean;
}) => {
  const result = await query<ItemRow>(
    `
      insert into equipment_items (
        category_id,
        name,
        short_name,
        use_short_name_on_timeline,
        serial_number,
        asset_tag,
        suggested_day_rate,
        status,
        notes,
        is_visible_on_timeline
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
      returning
        id,
        category_id,
        (
          select name from equipment_categories where id = category_id
        ) as category_name,
        name,
        short_name,
        use_short_name_on_timeline,
        serial_number,
        asset_tag,
        suggested_day_rate::double precision as suggested_day_rate,
        status,
        notes,
        is_visible_on_timeline,
        created_at,
        updated_at
    `,
    [
      payload.categoryId,
      payload.name.trim(),
      payload.shortName?.trim() || null,
      payload.useShortNameOnTimeline,
      payload.serialNumber ?? null,
      payload.assetTag ?? null,
      payload.suggestedDayRate,
      payload.status,
      payload.notes ?? null,
      payload.isVisibleOnTimeline
    ]
  );

  const row = result.rows[0];

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

  return mapItem(row);
};

export const updateItem = async (
  id: string,
  payload: Partial<{
    categoryId: string;
    name: string;
    shortName: string | null;
    useShortNameOnTimeline: boolean;
    serialNumber: string | null;
    assetTag: string | null;
    suggestedDayRate: number | null;
    status: "active" | "service" | "retired";
    notes: string | null;
    isVisibleOnTimeline: boolean;
  }>
) => {
  const current = await getItemById(id);

  const nextCategoryId = payload.categoryId ?? current.category_id;
  const nextName = payload.name?.trim() ?? current.name;
  const nextShortName = payload.shortName === undefined ? current.short_name : payload.shortName?.trim() || null;
  const nextUseShortNameOnTimeline =
    payload.useShortNameOnTimeline === undefined ? current.use_short_name_on_timeline : payload.useShortNameOnTimeline;
  const nextSuggestedDayRate = payload.suggestedDayRate === undefined ? current.suggested_day_rate : payload.suggestedDayRate;
  const client = await pool.connect();
  let row: ItemRow | undefined;

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

    const updateResult = await client.query<ItemRow>(
      `
        update equipment_items
        set
          category_id = $2,
          name = $3,
          short_name = $4,
          use_short_name_on_timeline = $5,
          serial_number = $6,
          asset_tag = $7,
          suggested_day_rate = $8,
          status = $9,
          notes = $10,
          is_visible_on_timeline = $11,
          updated_at = now()
        where id = $1
        returning
          id,
          category_id,
          (
            select name from equipment_categories where id = category_id
          ) as category_name,
          name,
          short_name,
          use_short_name_on_timeline,
          serial_number,
          asset_tag,
          suggested_day_rate::double precision as suggested_day_rate,
          status,
          notes,
          is_visible_on_timeline,
          created_at,
          updated_at
      `,
      [
        id,
        nextCategoryId,
        nextName,
        nextShortName,
        nextUseShortNameOnTimeline,
        payload.serialNumber === undefined ? current.serial_number : payload.serialNumber,
        payload.assetTag === undefined ? current.asset_tag : payload.assetTag,
        nextSuggestedDayRate,
        payload.status ?? current.status,
        payload.notes === undefined ? current.notes : payload.notes,
        payload.isVisibleOnTimeline ?? current.is_visible_on_timeline
      ]
    );

    row = updateResult.rows[0];

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

    if (
      payload.suggestedDayRate !== undefined ||
      payload.shortName !== undefined ||
      payload.useShortNameOnTimeline !== undefined
    ) {
      const siblingResult = await client.query<ItemNameRow>(
        `
          select id, name
          from equipment_items
          where category_id = $1
        `,
        [row.category_id]
      );
      const modelKey = normalizeEquipmentModelName(row.name).toLowerCase();
      const relatedItemIds = siblingResult.rows
        .filter((candidate) => normalizeEquipmentModelName(candidate.name).toLowerCase() === modelKey)
        .map((candidate) => candidate.id);

      if (relatedItemIds.length > 1) {
        await client.query(
          `
            update equipment_items
            set
              suggested_day_rate = $2,
              short_name = $3,
              use_short_name_on_timeline = $4,
              updated_at = now()
            where id = any($1::uuid[])
          `,
          [relatedItemIds, nextSuggestedDayRate, nextShortName, nextUseShortNameOnTimeline]
        );

        const refreshedResult = await client.query<ItemRow>(
          `
            select
              i.id,
              i.category_id,
              c.name as category_name,
              i.name,
              i.short_name,
              i.use_short_name_on_timeline,
              i.serial_number,
              i.asset_tag,
              i.suggested_day_rate::double precision as suggested_day_rate,
              i.status,
              i.notes,
              i.is_visible_on_timeline,
              i.created_at,
              i.updated_at
            from equipment_items i
            join equipment_categories c on c.id = i.category_id
            where i.id = $1
          `,
          [id]
        );

        row = refreshedResult.rows[0] ?? row;
      }
    }

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

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

  return mapItem(row);
};

export const deleteItem = async (id: string) => {
  const bookingCount = await query<{ count: number }>("select count(*)::int as count from rental_bookings where equipment_item_id = $1", [id]);

  if ((bookingCount.rows[0]?.count ?? 0) > 0) {
    throw new ConflictError("Nie mogę usunąć urządzenia z historią wynajmu. Spróbuj po prostu wyłączyć urządzenie.");
  }

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

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