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

  name VARCHAR,
  race VARCHAR,
  race_id VARCHAR,
  caste VARCHAR,
  appeared INTEGER,
  birth_year INTEGER,
  birth_seconds72 INTEGER,
  death_year INTEGER,
  death_seconds72 INTEGER,
  associated_type VARCHAR,
  deity VARCHAR,
  goal VARCHAR,
  ent_pop_id INTEGER,
  active_interaction VARCHAR,
  force BOOLEAN,
  current_identity_id INTEGER,
  holds_artifact INTEGER,
  used_identity_id INTEGER,
  animated BOOLEAN,
  animated_string VARCHAR,
  adventurer BOOLEAN,

  PRIMARY KEY (id, world_id)
);

CREATE TABLE hf_entity_links (
  hf_id INTEGER NOT NULL,
  entity_id INTEGER NOT NULL,
  link_type VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,

  link_strength INTEGER,

  PRIMARY KEY (hf_id, entity_id, link_type, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (entity_id) REFERENCES historical_figures (id)
);

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

  hf_id INTEGER NOT NULL,
  entity_id INTEGER NOT NULL,

  position_profile_id INTEGER,
  start_year INTEGER,
  end_year INTEGER,

  PRIMARY KEY (id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (entity_id) REFERENCES historical_figures (id)
);

CREATE TABLE hf_site_links (
  hf_id INTEGER NOT NULL,
  site_id INTEGER NOT NULL,
  link_type VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,

  entity_id INTEGER,
  occupation_id INTEGER,
  sub_id INTEGER,

  PRIMARY KEY (hf_id, site_id, link_type, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (site_id) REFERENCES sites (id)
  -- FOREIGN KEY (entity_id) REFERENCES historical_figures (id)
);

CREATE TABLE hf_skills (
  hf_id INTEGER NOT NULL,
  skill VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,

  total_ip INTEGER,

  PRIMARY KEY (hf_id, skill, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
);

CREATE TABLE hf_relationship_profile_hf (
  hf_id INTEGER NOT NULL,
  hf_id_other INTEGER NOT NULL,
  world_id INTEGER NOT NULL,
  visual BOOLEAN NOT NULL,
  historical BOOLEAN NOT NULL,

  meet_count INTEGER,
  last_meet_year INTEGER,
  last_meet_seconds72 INTEGER,
  love INTEGER,
  respect INTEGER,
  trust INTEGER,
  loyalty INTEGER,
  fear INTEGER,
  known_identity_id INTEGER,
  rep_bonded INTEGER,
  rep_buddy INTEGER,
  rep_flatterer INTEGER,
  rep_friendly INTEGER,
  rep_grudge INTEGER,
  rep_information_source INTEGER,
  rep_killer INTEGER,
  rep_murderer INTEGER,
  rep_quarreler INTEGER,
  rep_violent INTEGER,

  PRIMARY KEY (hf_id, hf_id_other, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- Some HF are missing, and this might trigger an error
  -- FOREIGN KEY (hf_id_other) REFERENCES historical_figures (id)
);

CREATE TABLE hf_intrigue_actors (
  hf_id INTEGER NOT NULL,
  local_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  entity_id INTEGER,
  hf_id_other INTEGER,
  role VARCHAR,
  strategy VARCHAR,
  strategy_en_id INTEGER,
  strategy_epp_id INTEGER,
  handle_actor_id INTEGER,
  promised_actor_immortality BOOLEAN,
  promised_me_immortality BOOLEAN,

  PRIMARY KEY (hf_id, local_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
);

CREATE TABLE hf_intrigue_plots (
  hf_id INTEGER NOT NULL,
  local_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  type VARCHAR,
  on_hold BOOLEAN,
  actor_id INTEGER,
  artifact_id INTEGER,
  delegated_plot_id INTEGER,
  delegated_plot_hf_id INTEGER,
  entity_id INTEGER,
  parent_plot_hf_id INTEGER,
  parent_plot_id INTEGER,

  PRIMARY KEY (hf_id, local_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (delegated_plot_hf_id) REFERENCES historical_figures (id),
  -- FOREIGN KEY (parent_plot_hf_id) REFERENCES historical_figures (id)
  -- FOREIGN KEY (entity_id) REFERENCES historical_figures (id)
);

CREATE TABLE hf_plot_actors (
  hf_id INTEGER NOT NULL,
  local_id INTEGER NOT NULL,
  actor_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  plot_role VARCHAR,
  agreement_id INTEGER,
  agreement_has_messenger BOOLEAN,

  PRIMARY KEY (hf_id, local_id, actor_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (agreement_id) REFERENCES historical_figures (id)
);

CREATE TABLE hf_entity_reputations (
  hf_id INTEGER NOT NULL,
  entity_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  first_ageless_year INTEGER,
  first_ageless_season_count INTEGER,
  unsolved_murders INTEGER,

  rep_hunter INTEGER,
  rep_killer INTEGER,
  rep_knowledge_preserver INTEGER,
  rep_treasure_hunter INTEGER,

  PRIMARY KEY (hf_id, entity_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (entity_id) REFERENCES historical_figures (id)
);

CREATE TABLE hf_vague_relationships (
  hf_id INTEGER NOT NULL,
  hf_id_other INTEGER NOT NULL,
  world_id INTEGER NOT NULL,
  
  war_buddy BOOLEAN,
  artistic_buddy BOOLEAN,
  atheletic_rival BOOLEAN,
  athlete_buddy BOOLEAN,
  business_rival BOOLEAN,
  childhood_friend BOOLEAN,
  grudge BOOLEAN,
  jealous_obsession BOOLEAN,
  jealous_relationship_grudge BOOLEAN,
  persecution_grudge BOOLEAN,
  religious_persecution_grudge BOOLEAN,
  scholar_buddy BOOLEAN,
  supernatural_grudge BOOLEAN,

  PRIMARY KEY (hf_id, hf_id_other, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (hf_id_other) REFERENCES historical_figures (id)
);

CREATE TABLE hf_entity_squad_links (
  hf_id INTEGER NOT NULL,
  entity_id INTEGER NOT NULL,
  squad_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,
  
  squad_position INTEGER,
  start_year INTEGER,
  end_year INTEGER,

  PRIMARY KEY (hf_id, entity_id, squad_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (entity_id) REFERENCES historical_figures (id)
);

CREATE TABLE hf_links (
  hf_id INTEGER NOT NULL,
  hf_id_other INTEGER NOT NULL,
  link_type VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,
  
  link_strength INTEGER,

  PRIMARY KEY (hf_id, hf_id_other, link_type, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (hf_id_other) REFERENCES historical_figures (id)
);

CREATE TABLE hf_honor_entities (
  hf_id INTEGER NOT NULL,
  entity_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,
  
  battles INTEGER,
  kills INTEGER,

  PRIMARY KEY (hf_id, entity_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
  -- FOREIGN KEY (entity_id) REFERENCES historical_figures (id)
);

CREATE TABLE hf_site_properties (
  hf_id INTEGER NOT NULL,
  site_id INTEGER NOT NULL,
  property_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hf_id, site_id, property_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id),
  FOREIGN KEY (site_id, world_id) REFERENCES sites (id, world_id)
  -- FOREIGN KEY (site_id,property_id) REFERENCES sites_properties (site_id,local_id)
);

CREATE TABLE hf_spheres (
  hf_id INTEGER NOT NULL,
  sphere VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hf_id, sphere, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
);

CREATE TABLE hf_interaction_knowledges (
  hf_id INTEGER NOT NULL,
  interaction_knowledge VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hf_id, interaction_knowledge, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
);

CREATE TABLE hf_journey_pets (
  hf_id INTEGER NOT NULL,
  journey_pet VARCHAR NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hf_id, journey_pet, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id)
);

CREATE TABLE hf_honor_ids (
  hf_id INTEGER NOT NULL,
  entity_id INTEGER NOT NULL,
  honor_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (hf_id, entity_id, honor_id, world_id),
  FOREIGN KEY (hf_id, world_id) REFERENCES historical_figures (id, world_id),
  FOREIGN KEY (hf_id, entity_id, world_id) REFERENCES hf_honor_entities (hf_id, entity_id, world_id)
);