SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE marketplace_categories (
  id BINARY(16) NOT NULL, parent_id BINARY(16) NULL, slug VARCHAR(120) NOT NULL,
  name VARCHAR(120) NOT NULL, product_kind VARCHAR(32) NOT NULL, enabled BOOLEAN NOT NULL DEFAULT FALSE,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0, 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 marketplace_category_slug_unique (slug),
  KEY marketplace_category_browse_idx (product_kind, enabled, sort_order),
  CONSTRAINT marketplace_category_parent_fk FOREIGN KEY (parent_id) REFERENCES marketplace_categories(id) ON DELETE RESTRICT,
  CONSTRAINT marketplace_category_kind_check CHECK (product_kind IN ('game_key','gift_card','in_game_item','game_currency','dlc','e_money','digital_movie'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE catalog_products ADD COLUMN category_id BINARY(16) NULL AFTER id,
  ADD KEY catalog_category_idx (category_id, enabled),
  ADD CONSTRAINT catalog_category_fk FOREIGN KEY (category_id) REFERENCES marketplace_categories(id) ON DELETE RESTRICT;

CREATE TABLE catalog_country_availability (
  catalog_product_id BINARY(16) NOT NULL, country_code CHAR(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  available BOOLEAN NOT NULL DEFAULT FALSE, reason_code VARCHAR(80) NULL,
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (catalog_product_id, country_code),
  CONSTRAINT catalog_country_product_fk FOREIGN KEY (catalog_product_id) REFERENCES catalog_products(id) ON DELETE CASCADE,
  CONSTRAINT catalog_country_code_check CHECK (country_code REGEXP '^[A-Z]{2}$')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE listing_moderation_events (
  id BINARY(16) NOT NULL, listing_id BINARY(16) NOT NULL, event_key VARCHAR(190) NOT NULL,
  actor_staff_id BINARY(16) NOT NULL, decision VARCHAR(16) NOT NULL, reason VARCHAR(500) NULL,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY listing_moderation_event_unique (event_key),
  KEY listing_moderation_listing_idx (listing_id, occurred_at),
  CONSTRAINT listing_moderation_listing_fk FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE RESTRICT,
  CONSTRAINT listing_moderation_staff_fk FOREIGN KEY (actor_staff_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT listing_moderation_decision_check CHECK (decision IN ('approved','rejected')),
  CONSTRAINT listing_moderation_reason_check CHECK ((decision = 'approved' AND reason IS NULL) OR (decision = 'rejected' AND reason IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;