import type { EquipmentCategory } from "@rental/shared";

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

type CategoryRow = {
  id: string;
  name: string;
  item_count: number;
  active_item_count: number;
  service_item_count: number;
  retired_item_count: number;
  created_at: Date;
  updated_at: Date;
};

const mapCategory = (row: CategoryRow): EquipmentCategory => ({
  id: row.id,
  name: row.name,
  itemCount: Number(row.item_count),
  activeItemCount: Number(row.active_item_count),
  serviceItemCount: Number(row.service_item_count),
  retiredItemCount: Number(row.retired_item_count),
  createdAt: row.created_at.toISOString(),
  updatedAt: row.updated_at.toISOString()
});

export const listCategories = async () => {
  const result = await query<CategoryRow>(`
    select
      c.id,
      c.name,
      count(i.id)::int as item_count,
      count(i.id) filter (where i.status = 'active')::int as active_item_count,
      count(i.id) filter (where i.status = 'service')::int as service_item_count,
      count(i.id) filter (where i.status = 'retired')::int as retired_item_count,
      c.created_at,
      c.updated_at
    from equipment_categories c
    left join equipment_items i on i.category_id = c.id
    group by c.id
    order by
      case c.name
        when 'Laptopy' then 1
        when 'Tablety' then 2
        when 'Akcesoria' then 3
        else 99
      end,
      c.name asc
  `);

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

export const createCategory = async (name: string) => {
  const result = await query<CategoryRow>(
    `
      insert into equipment_categories (name)
      values ($1)
      returning
        id,
        name,
        0::int as item_count,
        0::int as active_item_count,
        0::int as service_item_count,
        0::int as retired_item_count,
        created_at,
        updated_at
    `,
    [name.trim()]
  );

  const row = result.rows[0];

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

  return mapCategory(row);
};

export const updateCategory = async (id: string, name: string) => {
  const result = await query<CategoryRow>(
    `
      update equipment_categories
      set name = $2, updated_at = now()
      where id = $1
      returning id, name, created_at, updated_at,
        (
          select count(*)::int
          from equipment_items
          where category_id = equipment_categories.id
        ) as item_count,
        (
          select count(*)::int
          from equipment_items
          where category_id = equipment_categories.id
            and status = 'active'
        ) as active_item_count,
        (
          select count(*)::int
          from equipment_items
          where category_id = equipment_categories.id
            and status = 'service'
        ) as service_item_count,
        (
          select count(*)::int
          from equipment_items
          where category_id = equipment_categories.id
            and status = 'retired'
        ) as retired_item_count
    `,
    [id, name.trim()]
  );

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

  const row = result.rows[0];

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

  return mapCategory(row);
};

export const deleteCategory = async (id: string) => {
  const itemCount = await query<{ count: number }>("select count(*)::int as count from equipment_items where category_id = $1", [id]);

  if ((itemCount.rows[0]?.count ?? 0) > 0) {
    throw new ConflictError("Cannot delete category with assigned equipment items");
  }

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

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