DROP TABLE  IF EXISTS game_template;

CREATE TABLE game_template
(
        game_template_id smallint not null, 
        game_level_id tinyint null,
        
        primary key (game_template_id)
);

DROP TABLE IF EXISTS game_template_square;

CREATE TABLE game_template_square
(
	game_template_id smallint not null references game_template,
	square_id tinyint not null,
	square_value tinyint not null,
	
	PRIMARY KEY (game_template_id, square_id)
);

DROP TABLE  IF EXISTS game;

CREATE TABLE game
(
        game_nm varchar(50) not null, 
        game_status tinyint null,
        game_template_id smallint null,
        players_required tinyint null,
 
        primary key (game_nm)
);

DROP TABLE IF EXISTS game_square;

CREATE TABLE game_square
(
	game_square_id varchar(52) not null,
	game_nm varchar(50) not null references game,
	square_id tinyint not null,
	square_value tinyint not null,
	square_owner varchar(50),
	
	PRIMARY KEY (game_square_id)
);

DROP TABLE  IF EXISTS player;

CREATE TABLE player
(
        player_email varchar(50) not null,
        player_nm varchar(50),
        client_id smallint, 
        game_nm varchar(50),
        
        primary key (player_email)
);

DROP TABLE  IF EXISTS game_player;

CREATE TABLE game_player
(
		game_nm varchar(50) not null references game,
		player_email varchar(50) not null references player,
		
		
		primary key (game_nm, player_email)
);

DROP TABLE IF EXISTS game_room;

CREATE TABLE game_room
(
		game_room_client_id smallint not null,
		client_id smallint not null,	

		primary key (game_room_client_id)	
);