SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE orders (
  id BINARY(16) NOT NULL, public_reference VARCHAR(24) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  buyer_id BINARY(16) NOT NULL, seller_id BINARY(16) NOT NULL, listing_id BINARY(16) NOT NULL,
  kind VARCHAR(32) NOT NULL, delivery_mode VARCHAR(32) NOT NULL,
  title_snapshot VARCHAR(160) NOT NULL, region_snapshot VARCHAR(40) NOT NULL,
  currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  unit_price_minor BIGINT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL, subtotal_minor BIGINT UNSIGNED NOT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'awaiting_payment', version INT UNSIGNED NOT NULL DEFAULT 1,
  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 orders_reference_unique (public_reference),
  KEY orders_buyer_idx (buyer_id, created_at), KEY orders_seller_idx (seller_id, created_at),
  KEY orders_status_idx (status, created_at), KEY orders_listing_idx (listing_id),
  CONSTRAINT orders_buyer_fk FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT orders_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT orders_listing_fk FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE RESTRICT,
  CONSTRAINT orders_kind_check CHECK (kind IN ('game_key','gift_card','in_game_item','game_currency','dlc','e_money','digital_movie')),
  CONSTRAINT orders_delivery_check CHECK (delivery_mode IN ('instant_code','manual_trade','account_topup','licensed_stream')),
  CONSTRAINT orders_status_check CHECK (status IN ('awaiting_payment','funded','delivered','disputed','completed','refunded','cancelled')),
  CONSTRAINT orders_reference_check CHECK (public_reference REGEXP '^[A-Z0-9]{12,24}$'),
  CONSTRAINT orders_quantity_check CHECK (quantity BETWEEN 1 AND 100),
  CONSTRAINT orders_amount_check CHECK (unit_price_minor > 0 AND subtotal_minor = unit_price_minor * quantity),
  CONSTRAINT orders_parties_check CHECK (buyer_id <> seller_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE inventory_items ADD CONSTRAINT inventory_order_fk
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT;

CREATE TABLE order_status_events (
  id BINARY(16) NOT NULL, order_id BINARY(16) NOT NULL, event_key VARCHAR(190) NOT NULL,
  from_status VARCHAR(24) NULL, to_status VARCHAR(24) NOT NULL, actor_user_id BINARY(16) NULL,
  reason_code VARCHAR(120) NOT NULL, metadata JSON NOT NULL,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY order_event_key_unique (event_key),
  KEY order_status_events_order_idx (order_id, occurred_at), KEY order_status_events_actor_idx (actor_user_id),
  CONSTRAINT order_event_order_fk FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT,
  CONSTRAINT order_event_actor_fk FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT order_event_to_status_check CHECK (to_status IN ('awaiting_payment','funded','delivered','disputed','completed','refunded','cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;