CREATE TABLE IF NOT EXISTS technology_types (
  id INT UNSIGNED PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NOT NULL,
  base_titan BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_silicon BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_pvc BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_tritium BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_food BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_seconds INT UNSIGNED NOT NULL DEFAULT 60,
  cost_factor DECIMAL(6,3) NOT NULL DEFAULT 1.8
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_technologies (
  user_id INT UNSIGNED NOT NULL,
  technology_type_id INT UNSIGNED NOT NULL,
  level SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, technology_type_id),
  CONSTRAINT fk_ut_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ut_type FOREIGN KEY (technology_type_id) REFERENCES technology_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS research_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  planet_id INT UNSIGNED NOT NULL,
  technology_type_id INT UNSIGNED NOT NULL,
  target_level SMALLINT UNSIGNED NOT NULL,
  finishes_at DATETIME NOT NULL,
  status ENUM('queued','done','cancelled') NOT NULL DEFAULT 'queued',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_research_due (status, finishes_at),
  CONSTRAINT fk_rq_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_rq_planet FOREIGN KEY (planet_id) REFERENCES planets(id) ON DELETE CASCADE,
  CONSTRAINT fk_rq_type FOREIGN KEY (technology_type_id) REFERENCES technology_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ship_types (
  id INT UNSIGNED PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NOT NULL,
  base_titan BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_silicon BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_pvc BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_tritium BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_food BIGINT UNSIGNED NOT NULL DEFAULT 0,
  base_seconds INT UNSIGNED NOT NULL DEFAULT 60,
  attack_value INT UNSIGNED NOT NULL DEFAULT 0,
  shield_value INT UNSIGNED NOT NULL DEFAULT 0,
  hull_value INT UNSIGNED NOT NULL DEFAULT 1,
  cargo_capacity INT UNSIGNED NOT NULL DEFAULT 0,
  speed DECIMAL(8,2) NOT NULL DEFAULT 1,
  fuel_per_distance DECIMAL(8,2) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS planet_ships (
  planet_id INT UNSIGNED NOT NULL,
  ship_type_id INT UNSIGNED NOT NULL,
  quantity BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (planet_id, ship_type_id),
  CONSTRAINT fk_ps_planet FOREIGN KEY (planet_id) REFERENCES planets(id) ON DELETE CASCADE,
  CONSTRAINT fk_ps_type FOREIGN KEY (ship_type_id) REFERENCES ship_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ship_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  planet_id INT UNSIGNED NOT NULL,
  ship_type_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  finishes_at DATETIME NOT NULL,
  status ENUM('queued','done','cancelled') NOT NULL DEFAULT 'queued',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_ship_due (status, finishes_at),
  CONSTRAINT fk_sq_planet FOREIGN KEY (planet_id) REFERENCES planets(id) ON DELETE CASCADE,
  CONSTRAINT fk_sq_type FOREIGN KEY (ship_type_id) REFERENCES ship_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fleets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id INT UNSIGNED NOT NULL,
  origin_planet_id INT UNSIGNED NOT NULL,
  target_planet_id INT UNSIGNED NOT NULL,
  mission ENUM('transport','attack','colonize') NOT NULL,
  cargo_titan DECIMAL(20,4) NOT NULL DEFAULT 0,
  cargo_silicon DECIMAL(20,4) NOT NULL DEFAULT 0,
  cargo_pvc DECIMAL(20,4) NOT NULL DEFAULT 0,
  cargo_tritium DECIMAL(20,4) NOT NULL DEFAULT 0,
  cargo_food DECIMAL(20,4) NOT NULL DEFAULT 0,
  departed_at DATETIME NOT NULL,
  arrives_at DATETIME NOT NULL,
  returns_at DATETIME NULL,
  status ENUM('outbound','returning','done','destroyed') NOT NULL DEFAULT 'outbound',
  result_summary VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_fleet_due (status, arrives_at, returns_at),
  KEY ix_fleet_owner (owner_id, status),
  CONSTRAINT fk_fleet_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_fleet_origin FOREIGN KEY (origin_planet_id) REFERENCES planets(id),
  CONSTRAINT fk_fleet_target FOREIGN KEY (target_planet_id) REFERENCES planets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fleet_ships (
  fleet_id BIGINT UNSIGNED NOT NULL,
  ship_type_id INT UNSIGNED NOT NULL,
  quantity BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (fleet_id, ship_type_id),
  CONSTRAINT fk_fs_fleet FOREIGN KEY (fleet_id) REFERENCES fleets(id) ON DELETE CASCADE,
  CONSTRAINT fk_fs_type FOREIGN KEY (ship_type_id) REFERENCES ship_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  report_type ENUM('transport','battle','system') NOT NULL,
  title VARCHAR(160) NOT NULL,
  body JSON NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_report_user (user_id, created_at),
  CONSTRAINT fk_report_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;