BEGIN;

CREATE TABLE positions (
	pos_id		BIGINT PRIMARY KEY,
	pos_x		DOUBLE PRECISION,
	pos_y		DOUBLE PRECISION,
	pos_z		DOUBLE PRECISION
);

CREATE TABLE maps (
	map_id		INTEGER PRIMARY KEY,
	map_name	TEXT
);

CREATE TABLE rounds (
	round_id	INTEGER PRIMARY KEY,
	map_id		INTEGER REFERENCES maps ON DELETE RESTRICT,
	began		TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	ended		TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE entity_states (
	state_id	BIGINT PRIMARY KEY,
	hp		INTEGER,
	armor		INTEGER,
	pos_id		BIGINT REFERENCES positions ON DELETE RESTRICT,
	round_id	INTEGER REFERENCES rounds ON DELETE RESTRICT
);

CREATE TYPE entity_type_t AS ENUM (
	'map',
	'ai',
	'human'
);

CREATE TABLE entities (
	entity_id	INTEGER PRIMARY KEY,
	round_id	INTEGER REFERENCES rounds ON DELETE RESTRICT,
	entity_type	entity_type_t,
	entity_name	TEXT
);

CREATE TABLE duels (
	duel_id		INTEGER PRIMARY KEY,
	round_id	INTEGER REFERENCES rounds ON DELETE RESTRICT,
	source_id	INTEGER REFERENCES entities ON DELETE RESTRICT,
	dest_id		INTEGER REFERENCES entities ON DELETE RESTRICT
);

CREATE TABLE events (
	event_id	BIGINT PRIMARY KEY,
	damage		INTEGER,
	source_state	BIGINT REFERENCES entity_states ON DELETE CASCADE,
	dest_state	BIGINT REFERENCES entity_states ON DELETE CASCADE,
	duel_id		BIGINT REFERENCES duels ON DELETE RESTRICT,
	round_id	INTEGER REFERENCES rounds ON DELETE RESTRICT
);

COMMIT;