-- ============================================================
-- MN ADVOGADOS ASSOCIADOS — Schema da Base de Dados v1.1
-- MariaDB 10.4+ / MySQL 8+
-- ============================================================

CREATE DATABASE IF NOT EXISTS mn_advogados CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mn_advogados;

-- ------------------------------------------------------------
-- UTILIZADORES E AUTENTICAÇÃO
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(150)        NOT NULL,
  email        VARCHAR(255)        NOT NULL UNIQUE,
  password     VARCHAR(255)        NOT NULL,
  role         ENUM('admin','advogado','secretaria') NOT NULL DEFAULT 'advogado',
  avatar       VARCHAR(500)        NULL,
  oab_number   VARCHAR(30)         NULL,
  phone        VARCHAR(30)         NULL,
  active       TINYINT(1)          NOT NULL DEFAULT 1,
  created_at   DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS refresh_tokens (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED        NOT NULL,
  token        VARCHAR(500)        NOT NULL UNIQUE,
  expires_at   DATETIME            NOT NULL,
  created_at   DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- CLIENTES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS clients (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name        VARCHAR(200)      NOT NULL,
  type             ENUM('singular','coletivo') NOT NULL DEFAULT 'singular',
  document_type    ENUM('bi','nif','passaporte','outro') NOT NULL DEFAULT 'bi',
  document_number  VARCHAR(50)       NULL,
  email            VARCHAR(255)      NULL,
  phone            VARCHAR(30)       NULL,
  phone_alt        VARCHAR(30)       NULL,
  address          TEXT              NULL,
  city             VARCHAR(100)      NULL,
  province         VARCHAR(100)      NULL,
  nationality      VARCHAR(100)      NULL DEFAULT 'Angolana',
  notes            TEXT              NULL,
  active           TINYINT(1)        NOT NULL DEFAULT 1,
  created_by       INT UNSIGNED      NOT NULL,
  created_at       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

-- ------------------------------------------------------------
-- PROCESSOS JURÍDICOS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS processes (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  process_number   VARCHAR(100)      NOT NULL UNIQUE,
  title            VARCHAR(300)      NOT NULL,
  client_id        INT UNSIGNED      NOT NULL,
  lawyer_id        INT UNSIGNED      NOT NULL,
  action_type      VARCHAR(100)      NULL,
  court            VARCHAR(200)      NULL,
  judge            VARCHAR(150)      NULL,
  opposing_party   VARCHAR(200)      NULL,
  status           ENUM('ativo','suspenso','concluido','arquivado') NOT NULL DEFAULT 'ativo',
  priority         ENUM('baixa','media','alta','urgente') NOT NULL DEFAULT 'media',
  start_date       DATE              NULL,
  end_date         DATE              NULL,
  description      TEXT              NULL,
  created_by       INT UNSIGNED      NOT NULL,
  created_at       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id)  REFERENCES clients(id),
  FOREIGN KEY (lawyer_id)  REFERENCES users(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS process_updates (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  process_id   INT UNSIGNED        NOT NULL,
  user_id      INT UNSIGNED        NOT NULL,
  content      TEXT                NOT NULL,
  created_at   DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (process_id) REFERENCES processes(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)
);

-- ------------------------------------------------------------
-- AGENDA / COMPROMISSOS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS appointments (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(300)        NOT NULL,
  type           ENUM('audiencia','reuniao','entrevista ','diligencia','outro') NOT NULL DEFAULT 'outro',
  process_id     INT UNSIGNED        NULL,
  client_id      INT UNSIGNED        NULL,
  lawyer_id      INT UNSIGNED        NOT NULL,
  start_datetime DATETIME            NOT NULL,
  end_datetime   DATETIME            NULL,
  location       VARCHAR(300)        NULL,
  description    TEXT                NULL,
  status         ENUM('agendado','realizado','cancelado') NOT NULL DEFAULT 'agendado',
  reminder_sent  TINYINT(1)          NOT NULL DEFAULT 0,
  created_by     INT UNSIGNED        NOT NULL,
  created_at     DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (process_id) REFERENCES processes(id) ON DELETE SET NULL,
  FOREIGN KEY (client_id)  REFERENCES clients(id)  ON DELETE SET NULL,
  FOREIGN KEY (lawyer_id)  REFERENCES users(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

-- ------------------------------------------------------------
-- DOCUMENTOS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS documents (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(300)        NOT NULL,
  original_name  VARCHAR(300)        NOT NULL,
  file_path      VARCHAR(600)        NOT NULL,
  mime_type      VARCHAR(100)        NOT NULL,
  file_size      INT UNSIGNED        NOT NULL,
  process_id     INT UNSIGNED        NULL,
  client_id      INT UNSIGNED        NULL,
  category       ENUM('peticao','contrato','procuracao','sentenca','recurso','oficio','outro') NULL DEFAULT 'outro',
  description    TEXT                NULL,
  uploaded_by    INT UNSIGNED        NOT NULL,
  created_at     DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (process_id)  REFERENCES processes(id) ON DELETE SET NULL,
  FOREIGN KEY (client_id)   REFERENCES clients(id)  ON DELETE SET NULL,
  FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- ------------------------------------------------------------
-- FINANCEIRO — HONORÁRIOS E PAGAMENTOS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS fees (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  process_id       INT UNSIGNED        NOT NULL,
  client_id        INT UNSIGNED        NOT NULL,
  lawyer_id        INT UNSIGNED        NOT NULL,
  description      VARCHAR(400)        NOT NULL,
  amount           DECIMAL(14,2)       NOT NULL,
  currency         VARCHAR(10)         NOT NULL DEFAULT 'AOA',
  due_date         DATE                NULL,
  status           ENUM('pendente','pago','parcial','cancelado','em_atraso') NOT NULL DEFAULT 'pendente',
  invoice_number   VARCHAR(100)        NULL,
  notes            TEXT                NULL,
  created_by       INT UNSIGNED        NOT NULL,
  created_at       DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (process_id) REFERENCES processes(id),
  FOREIGN KEY (client_id)  REFERENCES clients(id),
  FOREIGN KEY (lawyer_id)  REFERENCES users(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS payments (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fee_id         INT UNSIGNED        NOT NULL,
  amount         DECIMAL(14,2)       NOT NULL,
  payment_date   DATE                NOT NULL,
  method         ENUM('transferencia','numerario','cheque','mbway','outro') NOT NULL DEFAULT 'transferencia',
  reference      VARCHAR(200)        NULL,
  notes          TEXT                NULL,
  registered_by  INT UNSIGNED        NOT NULL,
  created_at     DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fee_id)        REFERENCES fees(id) ON DELETE CASCADE,
  FOREIGN KEY (registered_by) REFERENCES users(id)
);

-- ------------------------------------------------------------
-- REGISTOS DE TEMPO (Temporizador)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS time_entries (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id          INT UNSIGNED        NOT NULL,
  process_id       INT UNSIGNED        NULL,
  client_id        INT UNSIGNED        NULL,
  description      TEXT                NULL,
  duration_seconds INT UNSIGNED        NOT NULL DEFAULT 0,
  saved_at         DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at       DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)    REFERENCES users(id),
  FOREIGN KEY (process_id) REFERENCES processes(id) ON DELETE SET NULL,
  FOREIGN KEY (client_id)  REFERENCES clients(id)  ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- LOGS DE ACTIVIDADE
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_logs (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED        NULL,
  action       VARCHAR(100)        NOT NULL,
  entity_type  VARCHAR(50)         NULL,
  entity_id    INT UNSIGNED        NULL,
  description  TEXT                NULL,
  ip_address   VARCHAR(45)         NULL,
  created_at   DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- ÍNDICES PARA PERFORMANCE
-- ------------------------------------------------------------
CREATE INDEX idx_processes_client    ON processes(client_id);
CREATE INDEX idx_processes_lawyer    ON processes(lawyer_id);
CREATE INDEX idx_processes_status    ON processes(status);
CREATE INDEX idx_processes_number    ON processes(process_number);
CREATE INDEX idx_appointments_date   ON appointments(start_datetime);
CREATE INDEX idx_appointments_lawyer ON appointments(lawyer_id);
CREATE INDEX idx_appointments_status ON appointments(status);
CREATE INDEX idx_fees_status         ON fees(status);
CREATE INDEX idx_fees_due_date       ON fees(due_date);
CREATE INDEX idx_fees_client         ON fees(client_id);
CREATE INDEX idx_documents_process   ON documents(process_id);
CREATE INDEX idx_documents_client    ON documents(client_id);
CREATE INDEX idx_logs_user           ON activity_logs(user_id);
CREATE INDEX idx_logs_created        ON activity_logs(created_at);
CREATE INDEX idx_clients_name        ON clients(full_name);
CREATE INDEX idx_users_email         ON users(email);
CREATE INDEX idx_time_entries_user   ON time_entries(user_id);
CREATE INDEX idx_time_entries_process ON time_entries(process_id);
CREATE INDEX idx_time_entries_saved  ON time_entries(saved_at);