CREATE TABLE country (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE specification (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NULL,
  definition TEXT NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE ideology (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NULL,
  description TEXT NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE weapon_types (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NULL,
  effect TEXT NULL,
  danger_degree INTEGER UNSIGNED NULL,
  range INTEGER UNSIGNED NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE human (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  country_id INTEGER UNSIGNED NOT NULL,
  name VARCHAR(20) NULL,
  surename VARCHAR(40) NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(country_id)
    REFERENCES country(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE trader (
  human_id INTEGER UNSIGNED NOT NULL,
  mobile INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(human_id),
  FOREIGN KEY(human_id)
    REFERENCES human(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE organization (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  work_for INTEGER UNSIGNED NOT NULL,
  ideology_id INTEGER UNSIGNED NOT NULL,
  name VARCHAR(30) NULL,
  danger INTEGER UNSIGNED NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(ideology_id)
    REFERENCES ideology(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(work_for)
    REFERENCES country(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE weapon (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  trader_id INTEGER UNSIGNED NOT NULL,
  type_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(type_id)
    REFERENCES weapon_types(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(trader_id)
    REFERENCES trader(Human_id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE murders (
  victim_id INTEGER UNSIGNED NOT NULL,
  occurs DATE NOT NULL,
  weapon_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(victim_id),
  FOREIGN KEY(victim_id)
    REFERENCES human(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(weapon_id)
    REFERENCES weapon(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE cell (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  country_id INTEGER UNSIGNED NOT NULL,
  organization_id INTEGER UNSIGNED NOT NULL,
  superior_cell INTEGER UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(superior_cell)
    REFERENCES cell(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(organization_id)
    REFERENCES organization(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(country_id)
    REFERENCES country(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE human_knows_human (
  who INTEGER UNSIGNED NOT NULL,
  whom INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(who, whom),
  FOREIGN KEY(who)
    REFERENCES human(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(whom)
    REFERENCES human(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE human_has_weapon (
  human_id INTEGER UNSIGNED NOT NULL,
  weapon_id INTEGER UNSIGNED NOT NULL,
  since DATE NOT NULL,
  onto DATE NULL,
  PRIMARY KEY(human_id, weapon_id, since),
  FOREIGN KEY(human_id)
    REFERENCES human(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(weapon_id)
    REFERENCES weapon(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE agent (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  cell_id INTEGER UNSIGNED NOT NULL,
  human_id INTEGER UNSIGNED NOT NULL,
  codename VARCHAR(20) NULL,
  compromited BOOL NOT NULL DEFAULT FALSE,
  level INTEGER UNSIGNED NULL DEFAULT 0,
  PRIMARY KEY(id),
  FOREIGN KEY(human_id)
    REFERENCES human(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(cell_id)
    REFERENCES cell(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE agent_has_function (
  agent_id INTEGER UNSIGNED NOT NULL,
  specification_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(agent_id, specification_id),
  FOREIGN KEY(agent_id)
    REFERENCES agent(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(specification_id)
    REFERENCES specification(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;