-- ============================================================
-- DataRoom – Database Setup Script (MariaDB)
-- Run once:  mariadb -u root -p < scripts/setup-db.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS dataroom
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;


GRANT ALL PRIVILEGES ON dataroom.* TO 'dataroom'@'localhost';
FLUSH PRIVILEGES;

USE dataroom;

-- ─── users ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  username              VARCHAR(100)    NOT NULL,
  password_hash         VARCHAR(255)    NOT NULL,
  -- AES-256-GCM encrypted fields (iv:tag:ciphertext in hex)
  full_name_encrypted   TEXT            NULL,
  email_encrypted       TEXT            NULL,
  totp_secret_encrypted TEXT            NULL,
  totp_enabled          TINYINT(1)      NOT NULL DEFAULT 0,
  is_admin              TINYINT(1)      NOT NULL DEFAULT 0,
  is_active             TINYINT(1)      NOT NULL DEFAULT 1,
  last_login            DATETIME        NULL,
  created_at            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── permissions ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS permissions (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id           INT UNSIGNED NOT NULL,
  can_view          TINYINT(1)   NOT NULL DEFAULT 1,
  can_download      TINYINT(1)   NOT NULL DEFAULT 1,
  can_upload        TINYINT(1)   NOT NULL DEFAULT 0,
  can_create_folder TINYINT(1)   NOT NULL DEFAULT 0,
  can_delete        TINYINT(1)   NOT NULL DEFAULT 0,
  can_rename        TINYINT(1)   NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user (user_id),
  CONSTRAINT fk_perm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── folders ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS folders (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(255) NOT NULL,
  parent_id  INT UNSIGNED NULL DEFAULT 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,
  PRIMARY KEY (id),
  KEY idx_parent (parent_id),
  CONSTRAINT fk_folder_parent FOREIGN KEY (parent_id)  REFERENCES folders (id) ON DELETE CASCADE,
  CONSTRAINT fk_folder_user   FOREIGN KEY (created_by) REFERENCES users   (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── files ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS files (
  id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name         VARCHAR(255)  NOT NULL,            -- display name
  stored_name  VARCHAR(255)  NOT NULL,            -- UUID filename on disk
  folder_id    INT UNSIGNED  NULL DEFAULT NULL,
  file_size    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  mime_type    VARCHAR(255)  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,
  PRIMARY KEY (id),
  KEY idx_folder   (folder_id),
  KEY idx_uploader (uploaded_by),
  FULLTEXT KEY ft_name (name),
  CONSTRAINT fk_file_folder FOREIGN KEY (folder_id)   REFERENCES folders (id) ON DELETE CASCADE,
  CONSTRAINT fk_file_user   FOREIGN KEY (uploaded_by) REFERENCES users   (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── questions ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS questions (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  username   VARCHAR(100) NOT NULL,
  title      VARCHAR(512) NOT NULL,
  body       TEXT         NOT NULL,
  status     ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_q_user    (user_id),
  KEY idx_q_status  (status),
  KEY idx_q_created (created_at),
  CONSTRAINT fk_q_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── answers ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS answers (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  question_id INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  username    VARCHAR(100) NOT NULL,
  is_admin    TINYINT(1)   NOT NULL DEFAULT 0,
  body        TEXT         NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_a_question (question_id),
  CONSTRAINT fk_a_question FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE,
  CONSTRAINT fk_a_user     FOREIGN KEY (user_id)     REFERENCES users     (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── audit_logs ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       INT UNSIGNED    NULL,
  username      VARCHAR(100)    NULL,
  action        VARCHAR(64)     NOT NULL,
  resource_type VARCHAR(32)     NULL,
  resource_id   INT UNSIGNED    NULL,
  resource_name VARCHAR(512)    NULL,
  ip_address    VARCHAR(45)     NULL,    -- supports IPv6
  user_agent    TEXT            NULL,
  details       JSON            NULL,
  success       TINYINT(1)      NOT NULL DEFAULT 1,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user      (user_id),
  KEY idx_action    (action),
  KEY idx_created   (created_at),
  KEY idx_username  (username),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── audit_logs retention (90 days) ─────────────────────────────────────────
-- Requires MariaDB event scheduler to be enabled on the server.
-- Example (run as privileged user): SET GLOBAL event_scheduler = ON;
CREATE EVENT IF NOT EXISTS ev_purge_audit_logs_90d
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
  DO
    DELETE FROM audit_logs
    WHERE created_at < (NOW() - INTERVAL 90 DAY);
