-- migrate:up

CREATE TABLE media_files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  storage_scope ENUM('public', 'private', 'quarantine') NOT NULL,
  storage_path VARCHAR(512) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  file_extension VARCHAR(16) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL,
  width_pixels INT UNSIGNED NULL,
  height_pixels INT UNSIGNED NULL,
  sha256 BINARY(32) NOT NULL,
  status ENUM('quarantined', 'verified', 'rejected', 'deleted') NOT NULL DEFAULT 'quarantined',
  uploaded_by_admin_id BIGINT UNSIGNED NULL,
  verified_at DATETIME(6) NULL,
  deleted_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_media_files_public_id (public_id),
  UNIQUE KEY uq_media_files_storage_path (storage_path),
  KEY idx_media_files_hash (sha256),
  KEY idx_media_files_status_time (status, created_at),
  CONSTRAINT fk_media_files_uploader FOREIGN KEY (uploaded_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_media_files_size CHECK (size_bytes > 0),
  CONSTRAINT chk_media_files_dimensions CHECK (
    (width_pixels IS NULL AND height_pixels IS NULL) OR (width_pixels > 0 AND height_pixels > 0)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE banners (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  title VARCHAR(191) NOT NULL,
  body VARCHAR(1000) NULL,
  media_file_id BIGINT UNSIGNED NOT NULL,
  action_label VARCHAR(80) NULL,
  action_url VARCHAR(2048) NULL,
  display_order INT NOT NULL DEFAULT 0,
  starts_at DATETIME(6) NULL,
  ends_at DATETIME(6) NULL,
  status ENUM('draft', 'active', 'paused', 'archived') NOT NULL DEFAULT 'draft',
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_banners_public_id (public_id),
  KEY idx_banners_listing (status, starts_at, ends_at, display_order),
  CONSTRAINT fk_banners_media FOREIGN KEY (media_file_id) REFERENCES media_files (id) ON DELETE RESTRICT,
  CONSTRAINT fk_banners_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT chk_banners_dates CHECK (ends_at IS NULL OR starts_at IS NULL OR ends_at > starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE announcements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  title VARCHAR(191) NOT NULL,
  body_html MEDIUMTEXT NOT NULL,
  severity ENUM('info', 'success', 'warning', 'critical') NOT NULL DEFAULT 'info',
  target_rules JSON NULL,
  starts_at DATETIME(6) NULL,
  ends_at DATETIME(6) NULL,
  status ENUM('draft', 'scheduled', 'active', 'expired', 'archived') NOT NULL DEFAULT 'draft',
  published_at DATETIME(6) NULL,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_announcements_public_id (public_id),
  KEY idx_announcements_listing (status, starts_at, ends_at, id),
  CONSTRAINT fk_announcements_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT chk_announcements_dates CHECK (ends_at IS NULL OR starts_at IS NULL OR ends_at > starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE announcement_reads (
  announcement_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  read_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (announcement_id, user_id),
  KEY idx_announcement_reads_user (user_id, read_at),
  CONSTRAINT fk_announcement_reads_announcement FOREIGN KEY (announcement_id) REFERENCES announcements (id) ON DELETE CASCADE,
  CONSTRAINT fk_announcement_reads_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE tutorials (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  title VARCHAR(191) NOT NULL,
  slug VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  summary VARCHAR(1000) NULL,
  body_html MEDIUMTEXT NOT NULL,
  cover_media_file_id BIGINT UNSIGNED NULL,
  video_url VARCHAR(2048) NULL,
  display_order INT NOT NULL DEFAULT 0,
  status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft',
  published_at DATETIME(6) NULL,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_tutorials_public_id (public_id),
  UNIQUE KEY uq_tutorials_slug (slug),
  KEY idx_tutorials_listing (status, display_order, published_at),
  CONSTRAINT fk_tutorials_cover FOREIGN KEY (cover_media_file_id) REFERENCES media_files (id) ON DELETE SET NULL,
  CONSTRAINT fk_tutorials_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE maintenance_windows (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status ENUM('scheduled', 'active', 'completed', 'cancelled') NOT NULL DEFAULT 'scheduled',
  public_message VARCHAR(1000) NOT NULL,
  starts_at DATETIME(6) NOT NULL,
  ends_at DATETIME(6) NULL,
  allow_admin_access TINYINT(1) NOT NULL DEFAULT 1,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_maintenance_windows_public_id (public_id),
  KEY idx_maintenance_windows_status_time (status, starts_at, ends_at),
  CONSTRAINT fk_maintenance_windows_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT chk_maintenance_windows_dates CHECK (ends_at IS NULL OR ends_at > starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS maintenance_windows;
DROP TABLE IF EXISTS tutorials;
DROP TABLE IF EXISTS announcement_reads;
DROP TABLE IF EXISTS announcements;
DROP TABLE IF EXISTS banners;
DROP TABLE IF EXISTS media_files;