
DROP TABLE IF EXISTS game_template_square;
DROP TABLE IF EXISTS game_square;
DROP TABLE  IF EXISTS game_player;

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)
)
ENGINE innoDB;

DROP TABLE IF EXISTS game_template_square;

CREATE TABLE game_template_square
(
	game_template_id smallint not null,
	square_id tinyint not null,
	square_value tinyint not null,
	
	PRIMARY KEY (game_template_id, square_id),
	CONSTRAINT FOREIGN KEY (game_template_id) references game_template(game_template_id) ON DELETE cascade ON UPDATE cascade
)
ENGINE innoDB;

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)
)
ENGINE innoDB;

DROP TABLE IF EXISTS game_square;

CREATE TABLE game_square
(
	game_square_id varchar(52) not null,
	game_nm varchar(50) not null,
	square_id tinyint not null,
	square_value tinyint not null,
	square_owner varchar(50),
	
	PRIMARY KEY (game_square_id)
--	,CONSTRAINT FOREIGN KEY (game_nm) references game(game_nm) ON DELETE cascade ON UPDATE cascade
)
ENGINE innoDB;

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),
        player_score smallint default 0,
        
        primary key (player_email)
)
ENGINE innoDB;

DROP TABLE  IF EXISTS game_player;

CREATE TABLE game_player
(
		game_nm varchar(50) not null,
		player_email varchar(50) not null,
		
		
		primary key (game_nm, player_email),
		CONSTRAINT FOREIGN KEY (game_nm) references game(game_nm) ON DELETE restrict ON UPDATE restrict,
		CONSTRAINT FOREIGN KEY (player_email) references player(player_email) ON DELETE restrict ON UPDATE restrict
)
ENGINE innoDB;

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)	
)
ENGINE innoDB;

DROP TABLE IF EXISTS message_log;

CREATE TABLE message_log
(
		client_id smallint not null,
		message_id smallint not null,
		message_ts timestamp default CURRENT_TIMESTAMP
							ON UPDATE CURRENT_TIMESTAMP,
		
		primary key (client_id)
)
ENGINE innoDB;

DROP TABLE IF EXISTS server;

CREATE TABLE server
(
	address varchar(50) not null default 'localhost',
	port varchar(5) not null default '1099',
	is_primary tinyint,
	
	primary key (address)
)
ENGINE innoDB;

DROP TABLE IF EXISTS topic;

CREATE TABLE topic
(		
		topic_nm varchar(50) not null,
		game_nm varchar(50) null,
		
		primary key (topic_nm)
--		,CONSTRAINT FOREIGN KEY (game_nm) references game(game_nm) ON DELETE restrict ON UPDATE restrict
)
ENGINE innoDB;
