CREATE TABLE historical_event_collections (
  id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  type VARCHAR,
  start_year INTEGER,
  start_seconds72 INTEGER,
  end_year INTEGER,
  end_seconds72 INTEGER,

  PRIMARY KEY (id, world_id)
);

CREATE TABLE historical_event_collections_a_c (
  hec_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  a_support_merc_en_id INTEGER,
  adjective VARCHAR,
  aggressor_ent_id INTEGER,
  attacking_en_id INTEGER,
  attacking_merc_enid INTEGER,
  civ_id INTEGER,
  company_merc BOOLEAN,
  coords VARCHAR,

  PRIMARY KEY (hec_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE historical_event_collections_d_z (
  hec_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  d_support_merc_en_id INTEGER,
  defender_ent_id INTEGER,
  defending_en_id INTEGER,
  defending_merc_en_id INTEGER,
  feature_layer_id INTEGER,
  name VARCHAR,
  occasion_id INTEGER,
  ordinal INTEGER,
  parent_hec_id INTEGER,
  site_id INTEGER,
  subregion_id INTEGER,
  target_entity_id INTEGER,
  war_hec_id INTEGER,

  PRIMARY KEY (hec_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

-- The following should first be grouped 
-- othersize they are not really usefull
  -- attacking_squad_animated: Vec<bool>,
  -- attacking_squad_deaths: Vec<i32>,
  -- attacking_squad_entity_pop: Vec<i32>,
  -- attacking_squad_number: Vec<i32>,
  -- attacking_squad_race: Vec<String>,
  -- attacking_squad_site: Vec<i32>,

  -- defending_squad_animated: Vec<bool>,
  -- defending_squad_deaths: Vec<i32>,
  -- defending_squad_entity_pop: Vec<i32>,
  -- defending_squad_number: Vec<i32>,
  -- defending_squad_race: Vec<String>,
  -- defending_squad_site: Vec<i32>,

CREATE TABLE hec_he_ids (
  hec_id INTEGER NOT NULL,
  he_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, he_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_related_ids (
  hec_id INTEGER NOT NULL,
  rel_hec_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, rel_hec_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_individual_mercs (
  hec_id INTEGER NOT NULL,
  individual_merc BOOLEAN NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, individual_merc, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_noncom_hf_ids (
  hec_id INTEGER NOT NULL,
  noncom_hf_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, noncom_hf_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_outcomes (
  hec_id INTEGER NOT NULL,
  outcome VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, outcome, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_attacking_hf_ids (
  hec_id INTEGER NOT NULL,
  attacking_hf_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, attacking_hf_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_defending_hf_ids (
  hec_id INTEGER NOT NULL,
  defending_hf_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, defending_hf_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_a_support_merc_hf_ids (
  hec_id INTEGER NOT NULL,
  a_support_merc_hf_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, a_support_merc_hf_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);

CREATE TABLE hec_d_support_merc_hf_ids (
  hec_id INTEGER NOT NULL,
  d_support_merc_hf_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hec_id, d_support_merc_hf_id, world_id),
  FOREIGN KEY (hec_id, world_id) 
    REFERENCES historical_event_collections (id, world_id)
);