feat: upload of the projectHEADmaster

Signed-off-by: zwlucas <lucas.fariamo08@gmail.com>

1 files changed, 145 insertions, 0 deletions

diff --git a/api/src/modules/tickets/tickets.repository.ts b/api/src/modules/tickets/tickets.repository.ts
new file mode 100644
index 0000000..0c34abe
--- /dev/null
+++ b/api/src/modules/tickets/tickets.repository.ts
@@ -0,0 +1,145 @@
+/**
+ * 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;
+}