-- ============================================================
-- Migração 002 — Tabela de Registos de Tempo
-- Executar apenas se a BD já existir (instalações anteriores)
-- ============================================================
USE mn_advogados;

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
);

CREATE INDEX IF NOT EXISTS idx_time_entries_user    ON time_entries(user_id);
CREATE INDEX IF NOT EXISTS idx_time_entries_process ON time_entries(process_id);
CREATE INDEX IF NOT EXISTS idx_time_entries_saved   ON time_entries(saved_at);