CREATE TABLE IF NOT EXISTS doclap_folders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  parent_id INT NULL,
  folder_name VARCHAR(180) NOT NULL,
  folder_slug VARCHAR(120) NOT NULL,
  unit_name VARCHAR(120) NULL,
  description TEXT NULL,
  drive_url TEXT NULL,
  created_by INT NULL,
  created_by_name VARCHAR(160) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  INDEX idx_parent (parent_id),
  INDEX idx_unit (unit_name),
  INDEX idx_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS doclap_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  folder_id INT NOT NULL,
  doc_title VARCHAR(220) NOT NULL,
  doc_type VARCHAR(80) NULL,
  original_name VARCHAR(255) NULL,
  stored_name VARCHAR(255) NULL,
  file_path TEXT NULL,
  mime_type VARCHAR(160) NULL,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  drive_file_url TEXT NULL,
  note TEXT NULL,
  uploaded_by INT NULL,
  uploaded_by_name VARCHAR(160) NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  INDEX idx_folder (folder_id),
  INDEX idx_deleted (is_deleted),
  INDEX idx_uploaded_at (uploaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS doclap_activity (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  user_name VARCHAR(160) NULL,
  action VARCHAR(80) NOT NULL,
  target_type VARCHAR(50) NOT NULL,
  target_id INT NULL,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_target (target_type, target_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
