-- +goose Up
CREATE TABLE IF NOT EXISTS file_folders (
  id BINARY(16) NOT NULL,
  parent_id BINARY(16) NULL,
  name VARCHAR(128) NOT NULL,
  name_normalized VARCHAR(128) NOT NULL,
  depth SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  path_cache VARCHAR(2048) NULL,
  name_slot CHAR(36) NOT NULL DEFAULT 'live',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_by BINARY(16) NULL,
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  updated_by BINARY(16) NULL,
  deleted_at DATETIME(6) NULL,
  deleted_by BINARY(16) NULL,
  purge_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_file_folders_parent
    FOREIGN KEY (parent_id) REFERENCES file_folders(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  UNIQUE KEY uq_file_folders_parent_name_slot (parent_id, name_normalized, name_slot),
  KEY idx_file_folders_parent_active (parent_id, deleted_at),
  KEY idx_file_folders_deleted_at (deleted_at),
  KEY idx_file_folders_purge_at (purge_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS file_files (
  id BINARY(16) NOT NULL,
  folder_id BINARY(16) NOT NULL,
  name VARCHAR(255) NOT NULL,
  name_normalized VARCHAR(255) NOT NULL,
  extension VARCHAR(32) NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  mime_type VARCHAR(255) NOT NULL,
  bucket VARCHAR(128) NOT NULL,
  object_key VARCHAR(512) NOT NULL,
  object_version VARCHAR(128) NULL,
  etag VARCHAR(128) NULL,
  checksum_sha256 CHAR(64) NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  upload_error TEXT NULL,
  name_slot CHAR(36) NOT NULL DEFAULT 'live',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_by BINARY(16) NULL,
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  updated_by BINARY(16) NULL,
  deleted_at DATETIME(6) NULL,
  deleted_by BINARY(16) NULL,
  trashed_at DATETIME(6) NULL,
  purge_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_file_files_folder
    FOREIGN KEY (folder_id) REFERENCES file_folders(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  UNIQUE KEY uq_file_files_object_key (object_key),
  UNIQUE KEY uq_file_files_folder_name_slot (folder_id, name_normalized, name_slot),
  KEY idx_file_files_folder_active (folder_id, deleted_at),
  KEY idx_file_files_status_updated (status, updated_at),
  KEY idx_file_files_purge_at (purge_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- +goose Down
DROP TABLE IF EXISTS file_files;
DROP TABLE IF EXISTS file_folders;