-- ============================================================
-- DocManager — MySQL Schema
-- Run once on your remote MySQL server
-- ============================================================

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

USE docmanager;

-- ─────────────────────────────────────────
-- USERS
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(100) NOT NULL UNIQUE,
  password    VARCHAR(255) NOT NULL,             -- bcrypt hash
  role        ENUM('admin', 'user') NOT NULL DEFAULT 'user',
  is_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,
  INDEX idx_role (role),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────
-- FOLDERS  (supports unlimited nesting via parent_id)
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS folders (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  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,
  CONSTRAINT fk_folder_parent  FOREIGN KEY (parent_id)  REFERENCES folders(id) ON DELETE CASCADE,
  CONSTRAINT fk_folder_creator FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_parent (parent_id),
  INDEX idx_creator (created_by),
  FULLTEXT INDEX ft_folder_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────
-- FILES
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS files (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,           -- display / renamed name
  original_name VARCHAR(255) NOT NULL,           -- original upload name
  s3_key        VARCHAR(1024) NOT NULL,          -- full S3 object key
  size          BIGINT UNSIGNED NOT NULL,        -- bytes
  mime_type     VARCHAR(127),
  folder_id     INT UNSIGNED NOT 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,
  CONSTRAINT fk_file_folder   FOREIGN KEY (folder_id)   REFERENCES folders(id) ON DELETE CASCADE,
  CONSTRAINT fk_file_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id),
  INDEX idx_folder   (folder_id),
  INDEX idx_uploader (uploaded_by),
  INDEX idx_created  (created_at),
  FULLTEXT INDEX ft_file_name (name, original_name)  -- for fast search
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────
-- SEED: default admin user
-- password = "Admin@123"  (change immediately after first login)
-- ─────────────────────────────────────────
INSERT IGNORE INTO users (username, password, role)
VALUES (
  'admin',
  '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj/VK.7LFqJ2',
  'admin'
);