/*

This is what this file performs:

# Drop tables #
- Drop all tables

## Create tables ##
- create Team table (id, name(P) start_year owner)
- create Coach table
- - create College table
- create Player table
- - create Position table
- - create Alias table
- - create Current_team table
- - create Former_team table

### Add Foreign Keys ###
- coach: team -> team.name
- colleges: coach_id -> coach.id
- Position: player_id -> player.id
- Alias: player_id -> player.id
- current_team: team -> team.name
                player_id -> player.id
- former_team: team -> team.name
               player_id -> player.id

*/

DROP TABLE IF EXISTS colleges;
DROP TABLE IF EXISTS position;
DROP TABLE IF EXISTS alias;
DROP TABLE IF EXISTS current_team;
DROP TABLE IF EXISTS former_team;
DROP TABLE IF EXISTS coach;
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS team;

-- Create Team

CREATE TABLE team (id INT NOT NULL DEFAULT 0,
	   name VARCHAR(100) PRIMARY KEY NOT NULL DEFAULT 'noname',
	   owner VARCHAR(250) DEFAULT 'unknown',
	   syear DATE DEFAULT '1970-01-01');

-- Create Coach and colleges

CREATE TABLE coach (id INT PRIMARY KEY NOT NULL DEFAULT 0,
	   name VARCHAR(100) NOT NULL DEFAULT '',
	   info VARCHAR(3000),
	   bdate DATE DEFAULT 0,
	   bplace VARCHAR(250),
	   team VARCHAR(100) );

CREATE TABLE colleges (name VARCHAR(100) NOT NULL DEFAULT 'noname',
	   coach_id INT NOT NULL,
	   CONSTRAINT PK_college PRIMARY KEY (name, coach_id));

-- Create Player related tables

CREATE TABLE player (id INT NOT NULL PRIMARY KEY DEFAULT 0,
	   name VARCHAR(100) DEFAULT 'noname',
	   info VARCHAR(500),
	   weight FLOAT,
	   bplace VARCHAR(250),
	   bdate DATE,
	   height FLOAT,
	   debutteam VARCHAR(250),
	   draftyear DATE);

CREATE TABLE position (name VARCHAR(250) NOT NULL DEFAULT 'no position',
     player_id INT NOT NULL DEFAULT 0,
     CONSTRAINT PK_position PRIMARY KEY (name, player_id));

CREATE TABLE alias (name VARCHAR(250) NOT NULL DEFAULT 'no alias',
    player_id INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_alias PRIMARY KEY (name, player_id));

CREATE TABLE current_team (team VARCHAR(250) NOT NULL,
    player_id INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_current_team PRIMARY KEY (team, player_id));

CREATE TABLE former_team (team VARCHAR(250) NOT NULL DEFAULT 'no team',
    player_id INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_former_team PRIMARY KEY (team, player_id));

-- Add foreign keys
ALTER TABLE coach
  ADD CONSTRAINT FK_team
  FOREIGN KEY (team) REFERENCES team(name);


ALTER TABLE colleges
  ADD CONSTRAINT FK_coach_id
  FOREIGN KEY (coach_id) REFERENCES coach(id);

ALTER TABLE position
  ADD CONSTRAINT FK_pos_player_id
  FOREIGN KEY (player_id) REFERENCES player(id);

ALTER TABLE alias
  ADD CONSTRAINT FK_als_player_id
  FOREIGN KEY (player_id) REFERENCES player(id);

ALTER TABLE current_team
  ADD CONSTRAINT FK_cur_team
  FOREIGN KEY (team) REFERENCES team(name),
  ADD CONSTRAINT FK_cur_player_id
  FOREIGN KEY (player_id) REFERENCES player(id);

ALTER TABLE former_team
  ADD CONSTRAINT FK_for_team
  FOREIGN KEY (team) REFERENCES team(name),
  ADD CONSTRAINT FK_for_player_id
  FOREIGN KEY (player_id) REFERENCES player(id);