/**
 * tickets.repository.ts
 *
 * Camada de acesso a dados do módulo de tickets.
 * É a ÚNICA camada que importa e usa o Drizzle diretamente.
 *
 * Recebe `db` como parâmetro em vez de importar o singleton,
 * o que facilita testes unitários com banco em memória.
 */

import { eq, and, count, desc, type SQL } from 'drizzle-orm';
import type { Database } from '@/db/connection.js';
import { tickets, users, categories, type NewTicket } from '@/db/schema.js';
import { alias } from 'drizzle-orm/sqlite-core';
import type { ListTicketsQuery } from './tickets.schema.js';

// ─── Criação ──────────────────────────────────────────────────────────────────

export async function createTicket(
  db: Database,
  data: NewTicket,
) {
  const [ticket] = await db
    .insert(tickets)
    .values({
      ...data,
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    })
    .returning();

  return ticket;
}

// ─── Listagem com filtros e paginação ─────────────────────────────────────────

export async function findManyTickets(
  db: Database,
  filters: ListTicketsQuery,
) {
  const { status, priority, assigneeId, creatorId, categoryId, page, limit } = filters;
  const offset = (page - 1) * limit;

  // Constrói array de condições dinamicamente
  // Apenas condições com valor definido são incluídas no WHERE
  const conditions: SQL[] = [];
  if (status)     conditions.push(eq(tickets.status, status));
  if (priority)   conditions.push(eq(tickets.priority, priority));
  if (assigneeId) conditions.push(eq(tickets.assigneeId, assigneeId));
  if (creatorId)  conditions.push(eq(tickets.creatorId, creatorId));
  if (categoryId) conditions.push(eq(tickets.categoryId, categoryId));

  const where = conditions.length > 0 ? and(...conditions) : undefined;

  const creatorAlias = alias(users, 'creator');
  const assigneeAlias = alias(users, 'assignee');

  const rows = await db
    .select({
      id: tickets.id,
      title: tickets.title,
      description: tickets.description,
      status: tickets.status,
      priority: tickets.priority,
      creatorId: tickets.creatorId,
      assigneeId: tickets.assigneeId,
      categoryId: tickets.categoryId,
      dueAt: tickets.dueAt,
      resolvedAt: tickets.resolvedAt,
      createdAt: tickets.createdAt,
      updatedAt: tickets.updatedAt,
      creatorName: creatorAlias.name,
      assigneeName: assigneeAlias.name,
      categoryName: categories.name,
    })
    .from(tickets)
    .leftJoin(creatorAlias, eq(tickets.creatorId, creatorAlias.id))
    .leftJoin(assigneeAlias, eq(tickets.assigneeId, assigneeAlias.id))
    .leftJoin(categories, eq(tickets.categoryId, categories.id))
    .where(where)
    .orderBy(desc(tickets.createdAt)) // Mais recentes primeiro
    .limit(limit)
    .offset(offset);

  return rows;
}

// ─── Contagem (para meta de paginação) ───────────────────────────────────────

export async function countTickets(
  db: Database,
  filters: Omit<ListTicketsQuery, 'page' | 'limit'>,
) {
  const { status, priority, assigneeId, creatorId, categoryId } = filters;

  const conditions: SQL[] = [];
  if (status)     conditions.push(eq(tickets.status, status));
  if (priority)   conditions.push(eq(tickets.priority, priority));
  if (assigneeId) conditions.push(eq(tickets.assigneeId, assigneeId));
  if (creatorId)  conditions.push(eq(tickets.creatorId, creatorId));
  if (categoryId) conditions.push(eq(tickets.categoryId, categoryId));

  const where = conditions.length > 0 ? and(...conditions) : undefined;

  const [result] = await db
    .select({ total: count() })
    .from(tickets)
    .where(where);

  return result?.total ?? 0;
}

// ─── Busca por ID ─────────────────────────────────────────────────────────────

export async function findTicketById(db: Database, id: number) {
  const creatorAlias = alias(users, 'creator');
  const assigneeAlias = alias(users, 'assignee');

  const [ticket] = await db
    .select({
      id: tickets.id,
      title: tickets.title,
      description: tickets.description,
      status: tickets.status,
      priority: tickets.priority,
      creatorId: tickets.creatorId,
      assigneeId: tickets.assigneeId,
      categoryId: tickets.categoryId,
      dueAt: tickets.dueAt,
      resolvedAt: tickets.resolvedAt,
      createdAt: tickets.createdAt,
      updatedAt: tickets.updatedAt,
      creatorName: creatorAlias.name,
      assigneeName: assigneeAlias.name,
      categoryName: categories.name,
    })
    .from(tickets)
    .leftJoin(creatorAlias, eq(tickets.creatorId, creatorAlias.id))
    .leftJoin(assigneeAlias, eq(tickets.assigneeId, assigneeAlias.id))
    .leftJoin(categories, eq(tickets.categoryId, categories.id))
    .where(eq(tickets.id, id))
    .limit(1);

  return ticket ?? null;
}