-- COCKPIT · estrutura da base de dados (MySQL 5.7+ / MariaDB 10.3+)
-- Importar no cPanel → phpMyAdmin → selecionar a base de dados → Importar.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id     VARCHAR(40)  NOT NULL,
  email         VARCHAR(190) NOT NULL,
  name          VARCHAR(190) NOT NULL DEFAULT '',
  password_hash VARCHAR(255) NOT NULL,
  role          VARCHAR(10)  NOT NULL DEFAULT 'intern',  -- admin | tutor | intern
  must_change   TINYINT(1)   NOT NULL DEFAULT 0,
  active        TINYINT(1)   NOT NULL DEFAULT 1,
  created_at    VARCHAR(32)  NOT NULL,
  last_login    VARCHAR(32)  NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_public (public_id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Todos os registos da aplicação (pessoas, tarefas, horas, check-ins, reuniões, perfis…)
CREATE TABLE IF NOT EXISTS docs (
  col        VARCHAR(40)  NOT NULL,
  id         VARCHAR(64)  NOT NULL,
  data       LONGTEXT     NOT NULL,
  created_by INT UNSIGNED NULL,
  created_at VARCHAR(32)  NOT NULL,
  updated_at VARCHAR(32)  NOT NULL,
  PRIMARY KEY (col, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ficheiros carregados (CVs e logótipos). O conteúdo fica em app/storage/files.
CREATE TABLE IF NOT EXISTS files (
  id         VARCHAR(40)  NOT NULL,
  kind       VARCHAR(10)  NOT NULL,   -- cv | logo
  mime       VARCHAR(100) NOT NULL,
  name       VARCHAR(255) NOT NULL,
  size       INT UNSIGNED NOT NULL,
  created_by INT UNSIGNED NULL,
  created_at VARCHAR(32)  NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Limitação de tentativas (login, IA)
CREATE TABLE IF NOT EXISTS throttle (
  k  VARCHAR(120) NOT NULL,
  ts INT UNSIGNED NOT NULL,
  KEY idx_throttle (k, ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;