diff options
Diffstat (limited to 'api/src/db')
| -rw-r--r-- | api/src/db/connection.ts | 37 | ||||
| -rw-r--r-- | api/src/db/migrate.ts | 56 | ||||
| -rw-r--r-- | api/src/db/migrations/0000_huge_luke_cage.sql | 61 | ||||
| -rw-r--r-- | api/src/db/migrations/0001_ambitious_emma_frost.sql | 6 | ||||
| -rw-r--r-- | api/src/db/migrations/meta/0000_snapshot.json | 446 | ||||
| -rw-r--r-- | api/src/db/migrations/meta/0001_snapshot.json | 486 | ||||
| -rw-r--r-- | api/src/db/migrations/meta/_journal.json | 20 | ||||
| -rw-r--r-- | api/src/db/schema.ts | 240 | ||||
| -rw-r--r-- | api/src/db/seed.ts | 60 |
9 files changed, 1412 insertions, 0 deletions
diff --git a/api/src/db/connection.ts b/api/src/db/connection.ts new file mode 100644 index 0000000..2982336 --- /dev/null +++ b/api/src/db/connection.ts @@ -0,0 +1,37 @@ +/** + * connection.ts + * + * Singleton de conexão com o banco de dados. + * + * Exporta uma única instância do Drizzle ORM configurada com: + * - better-sqlite3 como driver síncrono + * - WAL mode para concorrência + * - Foreign keys habilitadas + * - Schema tipado para autocompletar queries + * + * Uso em qualquer módulo da aplicação: + * import { db } from '@/db/connection' + */ + +import Database from 'better-sqlite3'; +import { drizzle } from 'drizzle-orm/better-sqlite3'; +import * as schema from './schema.js'; + +const DATABASE_URL = process.env['DATABASE_URL'] ?? 'file:./helpdesk.db'; +const dbPath = DATABASE_URL.replace(/^file:/, ''); + +// ─── Criação do cliente SQLite ──────────────────────────────────────────────── +const sqlite = new Database(dbPath); + +// WAL mode: leituras não bloqueiam escritas (essencial para servidor web) +sqlite.pragma('journal_mode = WAL'); + +// Ativa integridade referencial (desativada por padrão no SQLite) +sqlite.pragma('foreign_keys = ON'); + +// ─── Instância Drizzle com schema completo ──────────────────────────────────── +// Passar o schema habilita db.query.tickets.findMany() com joins automáticos +export const db = drizzle(sqlite, { schema }); + +// Exporta o tipo para uso em funções que recebem `db` como parâmetro +export type Database = typeof db; diff --git a/api/src/db/migrate.ts b/api/src/db/migrate.ts new file mode 100644 index 0000000..7964ef6 --- /dev/null +++ b/api/src/db/migrate.ts @@ -0,0 +1,56 @@ +/** + * migrate.ts + * + * Script responsável por aplicar todas as migrations pendentes no banco SQLite. + * + * Pode ser executado de duas formas: + * 1. Manual (CLI): pnpm db:migrate + * 2. Programática: importado e chamado no boot do servidor (server.ts) + */ + +import Database from 'better-sqlite3'; +import { drizzle } from 'drizzle-orm/better-sqlite3'; +import { migrate } from 'drizzle-orm/better-sqlite3/migrator'; +import path from 'node:path'; +import { fileURLToPath } from 'node:url'; + +// Resolve o diretório correto em módulos ESM (não existe __dirname por padrão) +const __dirname = path.dirname(fileURLToPath(import.meta.url)); +const MIGRATIONS_DIR = path.join(__dirname, 'migrations'); +const DATABASE_URL = process.env['DATABASE_URL'] ?? 'file:./helpdesk.db'; + +// Drizzle espera o caminho sem o prefixo "file:" +const dbPath = DATABASE_URL.replace(/^file:/, ''); + +export async function runMigrations(): Promise<void> { + console.log('🗄️ Iniciando migrações do banco de dados...'); + console.log(` Banco : ${dbPath}`); + console.log(` Origem : ${MIGRATIONS_DIR}`); + + // better-sqlite3 opera de forma síncrona (sem Promises) + const sqlite = new Database(dbPath); + + // Otimizações de performance recomendadas para SQLite + sqlite.pragma('journal_mode = WAL'); // Write-Ahead Logging: leituras não bloqueiam escritas + sqlite.pragma('foreign_keys = ON'); // Ativa integridade referencial (desativada por padrão no SQLite) + + const db = drizzle(sqlite); + + // Aplica todas as migrations ainda não registradas na tabela __drizzle_migrations + migrate(db, { migrationsFolder: MIGRATIONS_DIR }); + + sqlite.close(); + console.log('✅ Migrações aplicadas com sucesso.'); +} + +// ─── Execução direta (CLI) ──────────────────────────────────────────────────── +// Roda apenas quando o arquivo é chamado diretamente (pnpm db:migrate) +// e não quando é importado como módulo pelo server.ts +const isMain = process.argv[1] === fileURLToPath(import.meta.url); + +if (isMain) { + runMigrations().catch((err) => { + console.error('❌ Falha ao aplicar migrações:', err); + process.exit(1); + }); +} diff --git a/api/src/db/migrations/0000_huge_luke_cage.sql b/api/src/db/migrations/0000_huge_luke_cage.sql new file mode 100644 index 0000000..1abb4d2 --- /dev/null +++ b/api/src/db/migrations/0000_huge_luke_cage.sql @@ -0,0 +1,61 @@ +CREATE TABLE `categories` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `name` text NOT NULL, + `slug` text NOT NULL, + `description` text, + `created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL, + `updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL +); +--> statement-breakpoint +CREATE TABLE `comments` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `content` text NOT NULL, + `is_internal` integer DEFAULT false NOT NULL, + `ticket_id` integer NOT NULL, + `author_id` integer NOT NULL, + `created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL, + `updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL, + FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON UPDATE no action ON DELETE cascade, + FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict +); +--> statement-breakpoint +CREATE TABLE `tickets` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `title` text NOT NULL, + `description` text NOT NULL, + `status` text DEFAULT 'open' NOT NULL, + `priority` text DEFAULT 'medium' NOT NULL, + `creator_id` integer NOT NULL, + `assignee_id` integer, + `category_id` integer, + `due_at` text, + `resolved_at` text, + `created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL, + `updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL, + FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict, + FOREIGN KEY (`assignee_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE set null, + FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null +); +--> statement-breakpoint +CREATE TABLE `users` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `name` text NOT NULL, + `email` text NOT NULL, + `password` text NOT NULL, + `role` text DEFAULT 'requester' NOT NULL, + `is_active` integer DEFAULT true NOT NULL, + `created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL, + `updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL +); +--> statement-breakpoint +CREATE UNIQUE INDEX `categories_slug_unique_idx` ON `categories` (`slug`);--> statement-breakpoint +CREATE INDEX `comments_ticket_id_idx` ON `comments` (`ticket_id`);--> statement-breakpoint +CREATE INDEX `comments_author_id_idx` ON `comments` (`author_id`);--> statement-breakpoint +CREATE INDEX `tickets_status_idx` ON `tickets` (`status`);--> statement-breakpoint +CREATE INDEX `tickets_priority_idx` ON `tickets` (`priority`);--> statement-breakpoint +CREATE INDEX `tickets_assignee_id_idx` ON `tickets` (`assignee_id`);--> statement-breakpoint +CREATE INDEX `tickets_creator_id_idx` ON `tickets` (`creator_id`);--> statement-breakpoint +CREATE INDEX `tickets_category_id_idx` ON `tickets` (`category_id`);--> statement-breakpoint +CREATE INDEX `tickets_status_assignee_idx` ON `tickets` (`status`,`assignee_id`);--> statement-breakpoint +CREATE UNIQUE INDEX `users_email_unique_idx` ON `users` (`email`);--> statement-breakpoint +CREATE INDEX `users_role_idx` ON `users` (`role`);
\ No newline at end of file diff --git a/api/src/db/migrations/0001_ambitious_emma_frost.sql b/api/src/db/migrations/0001_ambitious_emma_frost.sql new file mode 100644 index 0000000..c4256aa --- /dev/null +++ b/api/src/db/migrations/0001_ambitious_emma_frost.sql @@ -0,0 +1,6 @@ +CREATE TABLE `system_settings` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `allow_requester_chat` integer DEFAULT true NOT NULL, + `created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL, + `updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL +); diff --git a/api/src/db/migrations/meta/0000_snapshot.json b/api/src/db/migrations/meta/0000_snapshot.json new file mode 100644 index 0000000..8cdfb93 --- /dev/null +++ b/api/src/db/migrations/meta/0000_snapshot.json @@ -0,0 +1,446 @@ +{ + "version": "6", + "dialect": "sqlite", + "id": "6d22de90-25a5-4565-8b37-8d1d479c2dc6", + "prevId": "00000000-0000-0000-0000-000000000000", + "tables": { + "categories": { + "name": "categories", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "slug": { + "name": "slug", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "description": { + "name": "description", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "categories_slug_unique_idx": { + "name": "categories_slug_unique_idx", + "columns": [ + "slug" + ], + "isUnique": true + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "comments": { + "name": "comments", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "content": { + "name": "content", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "is_internal": { + "name": "is_internal", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "ticket_id": { + "name": "ticket_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "author_id": { + "name": "author_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "comments_ticket_id_idx": { + "name": "comments_ticket_id_idx", + "columns": [ + "ticket_id" + ], + "isUnique": false + }, + "comments_author_id_idx": { + "name": "comments_author_id_idx", + "columns": [ + "author_id" + ], + "isUnique": false + } + }, + "foreignKeys": { + "comments_ticket_id_tickets_id_fk": { + "name": "comments_ticket_id_tickets_id_fk", + "tableFrom": "comments", + "tableTo": "tickets", + "columnsFrom": [ + "ticket_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + }, + "comments_author_id_users_id_fk": { + "name": "comments_author_id_users_id_fk", + "tableFrom": "comments", + "tableTo": "users", + "columnsFrom": [ + "author_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "restrict", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "tickets": { + "name": "tickets", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "title": { + "name": "title", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "description": { + "name": "description", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "status": { + "name": "status", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "'open'" + }, + "priority": { + "name": "priority", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "'medium'" + }, + "creator_id": { + "name": "creator_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "assignee_id": { + "name": "assignee_id", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "category_id": { + "name": "category_id", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "due_at": { + "name": "due_at", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "resolved_at": { + "name": "resolved_at", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "tickets_status_idx": { + "name": "tickets_status_idx", + "columns": [ + "status" + ], + "isUnique": false + }, + "tickets_priority_idx": { + "name": "tickets_priority_idx", + "columns": [ + "priority" + ], + "isUnique": false + }, + "tickets_assignee_id_idx": { + "name": "tickets_assignee_id_idx", + "columns": [ + "assignee_id" + ], + "isUnique": false + }, + "tickets_creator_id_idx": { + "name": "tickets_creator_id_idx", + "columns": [ + "creator_id" + ], + "isUnique": false + }, + "tickets_category_id_idx": { + "name": "tickets_category_id_idx", + "columns": [ + "category_id" + ], + "isUnique": false + }, + "tickets_status_assignee_idx": { + "name": "tickets_status_assignee_idx", + "columns": [ + "status", + "assignee_id" + ], + "isUnique": false + } + }, + "foreignKeys": { + "tickets_creator_id_users_id_fk": { + "name": "tickets_creator_id_users_id_fk", + "tableFrom": "tickets", + "tableTo": "users", + "columnsFrom": [ + "creator_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "restrict", + "onUpdate": "no action" + }, + "tickets_assignee_id_users_id_fk": { + "name": "tickets_assignee_id_users_id_fk", + "tableFrom": "tickets", + "tableTo": "users", + "columnsFrom": [ + "assignee_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "set null", + "onUpdate": "no action" + }, + "tickets_category_id_categories_id_fk": { + "name": "tickets_category_id_categories_id_fk", + "tableFrom": "tickets", + "tableTo": "categories", + "columnsFrom": [ + "category_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "set null", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "users": { + "name": "users", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "email": { + "name": "email", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "password": { + "name": "password", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "role": { + "name": "role", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "'requester'" + }, + "is_active": { + "name": "is_active", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": true + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "users_email_unique_idx": { + "name": "users_email_unique_idx", + "columns": [ + "email" + ], + "isUnique": true + }, + "users_role_idx": { + "name": "users_role_idx", + "columns": [ + "role" + ], + "isUnique": false + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + } + }, + "enums": {}, + "_meta": { + "schemas": {}, + "tables": {}, + "columns": {} + }, + "internal": { + "indexes": {} + } +}
\ No newline at end of file diff --git a/api/src/db/migrations/meta/0001_snapshot.json b/api/src/db/migrations/meta/0001_snapshot.json new file mode 100644 index 0000000..9fd41f7 --- /dev/null +++ b/api/src/db/migrations/meta/0001_snapshot.json @@ -0,0 +1,486 @@ +{ + "version": "6", + "dialect": "sqlite", + "id": "e3832373-0ea8-46a1-8480-091761f1a94a", + "prevId": "6d22de90-25a5-4565-8b37-8d1d479c2dc6", + "tables": { + "categories": { + "name": "categories", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "slug": { + "name": "slug", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "description": { + "name": "description", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "categories_slug_unique_idx": { + "name": "categories_slug_unique_idx", + "columns": [ + "slug" + ], + "isUnique": true + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "comments": { + "name": "comments", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "content": { + "name": "content", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "is_internal": { + "name": "is_internal", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "ticket_id": { + "name": "ticket_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "author_id": { + "name": "author_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "comments_ticket_id_idx": { + "name": "comments_ticket_id_idx", + "columns": [ + "ticket_id" + ], + "isUnique": false + }, + "comments_author_id_idx": { + "name": "comments_author_id_idx", + "columns": [ + "author_id" + ], + "isUnique": false + } + }, + "foreignKeys": { + "comments_ticket_id_tickets_id_fk": { + "name": "comments_ticket_id_tickets_id_fk", + "tableFrom": "comments", + "tableTo": "tickets", + "columnsFrom": [ + "ticket_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + }, + "comments_author_id_users_id_fk": { + "name": "comments_author_id_users_id_fk", + "tableFrom": "comments", + "tableTo": "users", + "columnsFrom": [ + "author_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "restrict", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "system_settings": { + "name": "system_settings", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "allow_requester_chat": { + "name": "allow_requester_chat", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": true + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": {}, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "tickets": { + "name": "tickets", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "title": { + "name": "title", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "description": { + "name": "description", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "status": { + "name": "status", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "'open'" + }, + "priority": { + "name": "priority", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "'medium'" + }, + "creator_id": { + "name": "creator_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "assignee_id": { + "name": "assignee_id", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "category_id": { + "name": "category_id", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "due_at": { + "name": "due_at", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "resolved_at": { + "name": "resolved_at", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "tickets_status_idx": { + "name": "tickets_status_idx", + "columns": [ + "status" + ], + "isUnique": false + }, + "tickets_priority_idx": { + "name": "tickets_priority_idx", + "columns": [ + "priority" + ], + "isUnique": false + }, + "tickets_assignee_id_idx": { + "name": "tickets_assignee_id_idx", + "columns": [ + "assignee_id" + ], + "isUnique": false + }, + "tickets_creator_id_idx": { + "name": "tickets_creator_id_idx", + "columns": [ + "creator_id" + ], + "isUnique": false + }, + "tickets_category_id_idx": { + "name": "tickets_category_id_idx", + "columns": [ + "category_id" + ], + "isUnique": false + }, + "tickets_status_assignee_idx": { + "name": "tickets_status_assignee_idx", + "columns": [ + "status", + "assignee_id" + ], + "isUnique": false + } + }, + "foreignKeys": { + "tickets_creator_id_users_id_fk": { + "name": "tickets_creator_id_users_id_fk", + "tableFrom": "tickets", + "tableTo": "users", + "columnsFrom": [ + "creator_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "restrict", + "onUpdate": "no action" + }, + "tickets_assignee_id_users_id_fk": { + "name": "tickets_assignee_id_users_id_fk", + "tableFrom": "tickets", + "tableTo": "users", + "columnsFrom": [ + "assignee_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "set null", + "onUpdate": "no action" + }, + "tickets_category_id_categories_id_fk": { + "name": "tickets_category_id_categories_id_fk", + "tableFrom": "tickets", + "tableTo": "categories", + "columnsFrom": [ + "category_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "set null", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "users": { + "name": "users", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "email": { + "name": "email", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "password": { + "name": "password", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "role": { + "name": "role", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "'requester'" + }, + "is_active": { + "name": "is_active", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": true + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + }, + "updated_at": { + "name": "updated_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))" + } + }, + "indexes": { + "users_email_unique_idx": { + "name": "users_email_unique_idx", + "columns": [ + "email" + ], + "isUnique": true + }, + "users_role_idx": { + "name": "users_role_idx", + "columns": [ + "role" + ], + "isUnique": false + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + } + }, + "enums": {}, + "_meta": { + "schemas": {}, + "tables": {}, + "columns": {} + }, + "internal": { + "indexes": {} + } +}
\ No newline at end of file diff --git a/api/src/db/migrations/meta/_journal.json b/api/src/db/migrations/meta/_journal.json new file mode 100644 index 0000000..b3ea0c0 --- /dev/null +++ b/api/src/db/migrations/meta/_journal.json @@ -0,0 +1,20 @@ +{ + "version": "7", + "dialect": "sqlite", + "entries": [ + { + "idx": 0, + "version": "6", + "when": 1784989322795, + "tag": "0000_huge_luke_cage", + "breakpoints": true + }, + { + "idx": 1, + "version": "6", + "when": 1785005330713, + "tag": "0001_ambitious_emma_frost", + "breakpoints": true + } + ] +}
\ No newline at end of file diff --git a/api/src/db/schema.ts b/api/src/db/schema.ts new file mode 100644 index 0000000..ce95493 --- /dev/null +++ b/api/src/db/schema.ts @@ -0,0 +1,240 @@ +/** + * schema.ts + * + * Fonte única de verdade do banco de dados. + * Todos os tipos TypeScript são *inferidos* deste arquivo — nunca definidos + * manualmente — garantindo que DB e aplicação nunca fiquem dessincronizados. + */ + +import { sql } from 'drizzle-orm'; +import { + sqliteTable, + text, + integer, + index, + uniqueIndex, +} from 'drizzle-orm/sqlite-core'; + +// ───────────────────────────────────────────────────────────────────────────── +// HELPERS +// ───────────────────────────────────────────────────────────────────────────── + +/** + * Colunas de auditoria reutilizáveis. + * SQLite armazena timestamps como texto ISO 8601. + */ +const timestamps = { + createdAt: text('created_at') + .notNull() + .default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`), + updatedAt: text('updated_at') + .notNull() + .default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`), +}; + +// ───────────────────────────────────────────────────────────────────────────── +// ENUMS (simulados via union types — SQLite não tem tipo ENUM nativo) +// ───────────────────────────────────────────────────────────────────────────── + +export const USER_ROLES = ['requester', 'agent', 'admin'] as const; +export type UserRole = (typeof USER_ROLES)[number]; + +export const TICKET_STATUSES = [ + 'open', // Chamado aberto, ainda não atribuído + 'in_progress', // Atribuído a um agente, em andamento + 'waiting', // Aguardando resposta do solicitante + 'resolved', // Solução aplicada, aguardando confirmação + 'closed', // Encerrado definitivamente +] as const; +export type TicketStatus = (typeof TICKET_STATUSES)[number]; + +export const TICKET_PRIORITIES = ['low', 'medium', 'high', 'critical'] as const; +export type TicketPriority = (typeof TICKET_PRIORITIES)[number]; + +// ───────────────────────────────────────────────────────────────────────────── +// TABELA: users +// ───────────────────────────────────────────────────────────────────────────── + +export const users = sqliteTable( + 'users', + { + id: integer('id').primaryKey({ autoIncrement: true }), + + // Identificação + name: text('name').notNull(), + email: text('email').notNull(), + password: text('password').notNull(), // hash bcrypt — nunca plain text + + // Controle de acesso + role: text('role', { enum: USER_ROLES }).notNull().default('requester'), + + // Conta ativa? Soft-delete sem perder histórico de tickets/comments + isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true), + + ...timestamps, + }, + (table) => ({ + // Garante emails únicos na camada do banco + emailUniqueIdx: uniqueIndex('users_email_unique_idx').on(table.email), + + // Consultas frequentes por role (ex: listar todos os agentes) + roleIdx: index('users_role_idx').on(table.role), + }), +); + +// ───────────────────────────────────────────────────────────────────────────── +// TABELA: categories +// ───────────────────────────────────────────────────────────────────────────── + +export const categories = sqliteTable( + 'categories', + { + id: integer('id').primaryKey({ autoIncrement: true }), + name: text('name').notNull(), + + // Slug para uso em URLs e filtros: "hardware", "acesso-sistema", etc. + slug: text('slug').notNull(), + + description: text('description'), + + ...timestamps, + }, + (table) => ({ + slugUniqueIdx: uniqueIndex('categories_slug_unique_idx').on(table.slug), + }), +); + +// ───────────────────────────────────────────────────────────────────────────── +// TABELA: tickets +// ───────────────────────────────────────────────────────────────────────────── + +export const tickets = sqliteTable( + 'tickets', + { + id: integer('id').primaryKey({ autoIncrement: true }), + + // Conteúdo + title: text('title').notNull(), + description: text('description').notNull(), + + // Classificação + status: text('status', { enum: TICKET_STATUSES }) + .notNull() + .default('open'), + + priority: text('priority', { enum: TICKET_PRIORITIES }) + .notNull() + .default('medium'), + + // ── Relacionamentos ──────────────────────────────────────────────────── + // Quem abriu o chamado (obrigatório) + creatorId: integer('creator_id') + .notNull() + .references(() => users.id, { onDelete: 'restrict' }), + + // Agente responsável (opcional — pode ser atribuído depois) + assigneeId: integer('assignee_id') + .references(() => users.id, { onDelete: 'set null' }), + + // Categoria do chamado (opcional — pode ser classificado depois) + categoryId: integer('category_id') + .references(() => categories.id, { onDelete: 'set null' }), + + // Data limite para resolução (SLA) + dueAt: text('due_at'), + + // Data em que o chamado foi efetivamente resolvido + resolvedAt: text('resolved_at'), + + ...timestamps, + }, + (table) => ({ + // Índices para os filtros mais comuns no painel do agente + statusIdx: index('tickets_status_idx').on(table.status), + priorityIdx: index('tickets_priority_idx').on(table.priority), + assigneeIdx: index('tickets_assignee_id_idx').on(table.assigneeId), + creatorIdx: index('tickets_creator_id_idx').on(table.creatorId), + categoryIdx: index('tickets_category_id_idx').on(table.categoryId), + + // Índice composto: painel do agente filtra por status + assignee com frequência + statusAssigneeIdx: index('tickets_status_assignee_idx').on( + table.status, + table.assigneeId, + ), + }), +); + +// ───────────────────────────────────────────────────────────────────────────── +// TABELA: comments +// ───────────────────────────────────────────────────────────────────────────── + +export const comments = sqliteTable( + 'comments', + { + id: integer('id').primaryKey({ autoIncrement: true }), + + content: text('content').notNull(), + + /** + * Diferencia mensagens públicas de notas internas da equipe. + * true → nota interna (visível apenas para agentes/admins) + * false → resposta pública (visível para o solicitante) + */ + isInternal: integer('is_internal', { mode: 'boolean' }) + .notNull() + .default(false), + + // ── Relacionamentos ──────────────────────────────────────────────────── + ticketId: integer('ticket_id') + .notNull() + .references(() => tickets.id, { onDelete: 'cascade' }), + + authorId: integer('author_id') + .notNull() + .references(() => users.id, { onDelete: 'restrict' }), + + ...timestamps, + }, + (table) => ({ + // Buscar todos os comentários de um ticket é a query mais frequente + ticketIdx: index('comments_ticket_id_idx').on(table.ticketId), + authorIdx: index('comments_author_id_idx').on(table.authorId), + }), +); + +// ───────────────────────────────────────────────────────────────────────────── +// TABELA: system_settings +// ───────────────────────────────────────────────────────────────────────────── + +export const systemSettings = sqliteTable( + 'system_settings', + { + id: integer('id').primaryKey({ autoIncrement: true }), + + // Configurações Globais + allowRequesterChat: integer('allow_requester_chat', { mode: 'boolean' }).notNull().default(true), + + ...timestamps, + } +); + +// ───────────────────────────────────────────────────────────────────────────── +// TIPOS INFERIDOS +// +// Use estes tipos em toda a aplicação. +// Ex: função que retorna um ticket → retorno tipado como `Ticket` +// ───────────────────────────────────────────────────────────────────────────── + +export type User = typeof users.$inferSelect; +export type NewUser = typeof users.$inferInsert; + +export type Category = typeof categories.$inferSelect; +export type NewCategory = typeof categories.$inferInsert; + +export type Ticket = typeof tickets.$inferSelect; +export type NewTicket = typeof tickets.$inferInsert; + +export type Comment = typeof comments.$inferSelect; +export type NewComment = typeof comments.$inferInsert; + +export type SystemSetting = typeof systemSettings.$inferSelect; diff --git a/api/src/db/seed.ts b/api/src/db/seed.ts new file mode 100644 index 0000000..2cde3ce --- /dev/null +++ b/api/src/db/seed.ts @@ -0,0 +1,60 @@ +import Database from 'better-sqlite3'; +import { drizzle } from 'drizzle-orm/better-sqlite3'; +import * as schema from './schema.js'; +import bcrypt from 'bcryptjs'; +import path from 'node:path'; +import { fileURLToPath } from 'node:url'; + +const DATABASE_URL = process.env['DATABASE_URL'] ?? 'file:./helpdesk.db'; +const dbPath = DATABASE_URL.replace(/^file:/, ''); + +const sqlite = new Database(dbPath); +const db = drizzle(sqlite, { schema }); + +async function seed() { + console.log('🌱 Verificando usuário admin...'); + + const existingAdmin = await db.query.users.findFirst({ + where: (users, { eq }) => eq(users.email, 'admin@helpdesk.local'), + }); + + if (!existingAdmin) { + const hashedPassword = await bcrypt.hash('admin123', 10); + + await db.insert(schema.users).values({ + name: 'Administrador TI', + email: 'admin@helpdesk.local', + password: hashedPassword, + role: 'admin', + isActive: true, + }); + + console.log('✅ Usuário admin criado com sucesso!'); + console.log('E-mail: admin@helpdesk.local'); + console.log('Senha: admin123'); + } else { + console.log('✅ Usuário admin já existe!'); + } + + console.log('🌱 Verificando categorias...'); + const existingCategories = await db.query.categories.findMany(); + if (existingCategories.length === 0) { + await db.insert(schema.categories).values([ + { name: 'Hardware', slug: 'hardware', description: 'Problemas físicos em computadores, impressoras e periféricos' }, + { name: 'Software', slug: 'software', description: 'Instalação, erros e licenças de programas' }, + { name: 'Redes e Internet', slug: 'redes', description: 'Sem conexão, lentidão ou falhas no Wi-Fi/Cabo' }, + { name: 'Acessos e Senhas', slug: 'acessos', description: 'Redefinição de senha, criação de e-mail e acessos a sistemas' }, + { name: 'Dúvidas e Suporte', slug: 'duvidas', description: 'Dúvidas gerais sobre uso de sistemas e equipamentos' } + ]); + console.log('✅ Categorias padrão criadas com sucesso!'); + } else { + console.log('✅ Categorias já existem!'); + } + + sqlite.close(); +} + +seed().catch((err) => { + console.error('❌ Erro no seed:', err); + process.exit(1); +}); |