CREATE TABLE IF NOT EXISTS gallery_asset_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  base_key VARCHAR(190) NOT NULL,
  title VARCHAR(190) NOT NULL,
  slug VARCHAR(190) NULL,
  category VARCHAR(120) NULL,
  subcategory VARCHAR(120) NULL,
  tags_json LONGTEXT NULL,
  tags_text TEXT NULL,
  orientation VARCHAR(20) NULL,
  thumbnail_path VARCHAR(255) NULL,
  preview_path VARCHAR(255) NULL,
  preview_kind VARCHAR(20) NOT NULL DEFAULT 'image',
  file_types_text VARCHAR(255) NULL,
  file_count INT UNSIGNED NOT NULL DEFAULT 0,
  source_folder VARCHAR(190) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_by_name_snapshot VARCHAR(190) 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_gallery_asset_groups_location_base_key (location_id, base_key),
  KEY idx_gallery_asset_groups_location_category (location_id, category),
  KEY idx_gallery_asset_groups_location_orientation (location_id, orientation),
  KEY idx_gallery_asset_groups_location_updated (location_id, updated_at),
  KEY idx_gallery_asset_groups_location_title (location_id, title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gallery_asset_files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  asset_group_id BIGINT UNSIGNED NOT NULL,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  base_name VARCHAR(190) NOT NULL,
  extension VARCHAR(20) NOT NULL,
  mime_type VARCHAR(120) NULL,
  relative_path VARCHAR(255) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  is_thumbnail TINYINT(1) NOT NULL DEFAULT 0,
  is_preview TINYINT(1) NOT NULL DEFAULT 0,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_gallery_asset_files_group (asset_group_id),
  KEY idx_gallery_asset_files_location_ext (location_id, extension),
  KEY idx_gallery_asset_files_location_preview (location_id, is_preview, is_thumbnail),
  CONSTRAINT fk_gallery_asset_files_group
    FOREIGN KEY (asset_group_id) REFERENCES gallery_asset_groups(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;