SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE catalog_products (
  id BINARY(16) NOT NULL, slug VARCHAR(160) NOT NULL, name VARCHAR(160) NOT NULL,
  kind VARCHAR(32) NOT NULL, enabled BOOLEAN NOT NULL DEFAULT FALSE,
  capabilities JSON 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 catalog_slug_unique (slug),
  CONSTRAINT catalog_kind_check CHECK (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;

CREATE TABLE listings (
  id BINARY(16) NOT NULL, seller_id BINARY(16) NOT NULL, catalog_product_id BINARY(16) NOT NULL,
  kind VARCHAR(32) NOT NULL, delivery_mode VARCHAR(32) NOT NULL, title VARCHAR(160) NOT NULL,
  description TEXT NOT NULL, region_code VARCHAR(40) NOT NULL, server_code VARCHAR(80) NULL,
  currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, unit_price_minor BIGINT UNSIGNED NOT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'draft', version INT UNSIGNED NOT NULL DEFAULT 1,
  rejection_reason VARCHAR(500) 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), deleted_at DATETIME(6) NULL,
  PRIMARY KEY (id), KEY listings_marketplace_idx (kind, region_code, status, deleted_at),
  KEY listings_seller_idx (seller_id, status, deleted_at),
  CONSTRAINT listings_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT listings_catalog_fk FOREIGN KEY (catalog_product_id) REFERENCES catalog_products(id) ON DELETE RESTRICT,
  CONSTRAINT listings_kind_check CHECK (kind IN ('game_key','gift_card','in_game_item','game_currency','dlc','e_money','digital_movie')),
  CONSTRAINT listings_delivery_check CHECK (delivery_mode IN ('instant_code','manual_trade','account_topup','licensed_stream')),
  CONSTRAINT listings_status_check CHECK (status IN ('draft','pending_review','active','paused','sold_out','rejected')),
  CONSTRAINT listings_title_check CHECK (CHAR_LENGTH(title) BETWEEN 5 AND 160),
  CONSTRAINT listings_price_check CHECK (unit_price_minor > 0),
  CONSTRAINT listings_version_check CHECK (version > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE inventory_items (
  id BINARY(16) NOT NULL, listing_id BINARY(16) NOT NULL, seller_id BINARY(16) NOT NULL,
  secret_ciphertext MEDIUMBLOB NOT NULL, encryption_key_version VARCHAR(80) NOT NULL,
  secret_fingerprint BINARY(32) NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'available',
  order_id BINARY(16) NULL, reserved_until DATETIME(6) 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 inventory_fingerprint_unique (listing_id, secret_fingerprint),
  UNIQUE KEY inventory_one_item_per_order (order_id), KEY inventory_reservation_idx (listing_id, status, created_at),
  KEY inventory_seller_idx (seller_id),
  CONSTRAINT inventory_listing_fk FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE RESTRICT,
  CONSTRAINT inventory_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT inventory_status_check CHECK (status IN ('available','reserved','sold_funded','delivered','completed')),
  CONSTRAINT inventory_state_shape CHECK (
    (status = 'available' AND order_id IS NULL AND reserved_until IS NULL)
    OR (status = 'reserved' AND order_id IS NOT NULL AND reserved_until IS NOT NULL)
    OR (status IN ('sold_funded','delivered','completed') AND order_id IS NOT NULL)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;