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

  type VARCHAR,
  name VARCHAR,
  coordinate_id INTEGER,
  rectangle_id INTEGER,
  civ_id INTEGER,
  cur_owner_id INTEGER,

  PRIMARY KEY (id, world_id),
  FOREIGN KEY (coordinate_id, world_id) REFERENCES coordinates (id, world_id),
  FOREIGN KEY (rectangle_id, world_id) REFERENCES rectangles (id, world_id)
  -- FOREIGN KEY (civ_id) REFERENCES rectangle (id)
  -- FOREIGN KEY (cur_owner_id) REFERENCES rectangle (id)
);

CREATE TABLE sites_structures (
  site_id INTEGER NOT NULL,
  local_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  type VARCHAR,
  subtype VARCHAR,
  name VARCHAR,
  name2 VARCHAR,
  entity_id INTEGER,
  worship_hf_id INTEGER,
  deity_type INTEGER,
  deity_id INTEGER,
  religion_id INTEGER,
  dungeon_type INTEGER,

  PRIMARY KEY (site_id, local_id, world_id),
  FOREIGN KEY (site_id, world_id) REFERENCES sites (id, world_id)
);


CREATE TABLE structure_copied_artifact_ids (
  site_id INTEGER NOT NULL,
  structure_local_id INTEGER NOT NULL,
  copied_artifact_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (site_id, structure_local_id, copied_artifact_id, world_id),
  FOREIGN KEY (site_id, world_id) REFERENCES sites (id, world_id),
  FOREIGN KEY (site_id, structure_local_id, world_id) 
    REFERENCES sites_structures (site_id, local_id, world_id)
  -- FOREIGN KEY (copied_artifact_id) REFERENCES sites (id)
);

CREATE TABLE structure_inhabitant_hf_ids (
  site_id INTEGER NOT NULL,
  structure_local_id INTEGER NOT NULL,
  inhabitant_hf_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  PRIMARY KEY (site_id, structure_local_id, inhabitant_hf_id, world_id),
  FOREIGN KEY (site_id, world_id) REFERENCES sites (id, world_id),
  FOREIGN KEY (site_id, structure_local_id, world_id) 
    REFERENCES sites_structures (site_id, local_id, world_id)
  -- FOREIGN KEY (inhabitant_hf_id) REFERENCES sites (id)
);

CREATE TABLE sites_properties (
  site_id INTEGER NOT NULL,
  local_id INTEGER NOT NULL,
  world_id INTEGER NOT NULL,

  type VARCHAR,
  structure_id INTEGER,
  owner_hf_id INTEGER,

  PRIMARY KEY (site_id, local_id, world_id),
  FOREIGN KEY (site_id, world_id) REFERENCES sites (id, world_id),
  FOREIGN KEY (site_id, structure_id, world_id) 
    REFERENCES sites_structures (site_id, local_id, world_id)
  -- FOREIGN KEY (owner_hf_id) REFERENCES sites (id)
);