CREATE TABLE IF NOT EXISTS building_requirements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  object_id INT UNSIGNED NOT NULL,
  required_building_id INT UNSIGNED NULL,
  required_technology_id INT UNSIGNED NULL,
  required_level SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  KEY ix_breq_object (object_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS technology_requirements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  object_id INT UNSIGNED NOT NULL,
  required_building_id INT UNSIGNED NULL,
  required_technology_id INT UNSIGNED NULL,
  required_level SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  KEY ix_treq_object (object_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ship_requirements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  object_id INT UNSIGNED NOT NULL,
  required_building_id INT UNSIGNED NULL,
  required_technology_id INT UNSIGNED NULL,
  required_level SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  KEY ix_sreq_object (object_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS npc_registration_queue (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(40) NOT NULL UNIQUE,
  schedule_type VARCHAR(20) NOT NULL,
  weekly_schedule JSON NOT NULL,
  aggressiveness TINYINT UNSIGNED NOT NULL,
  economy_focus TINYINT UNSIGNED NOT NULL,
  research_focus TINYINT UNSIGNED NOT NULL,
  expansion_focus TINYINT UNSIGNED NOT NULL,
  min_reaction_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 3,
  max_reaction_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 20,
  absence_chance TINYINT UNSIGNED NOT NULL DEFAULT 5,
  register_at DATETIME NOT NULL,
  registered_user_id INT UNSIGNED NULL,
  registered_at DATETIME NULL,
  status ENUM('waiting','registered','failed') NOT NULL DEFAULT 'waiting',
  last_error VARCHAR(255) NULL,
  KEY ix_npc_registration_due (status,register_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;