-- Cleanup existing tables and data
DROP TABLE IF EXISTS Casts, MovieGenre, ProducedIn, Spoken, 
  Ratings, Actors, Movies, Countries, Languages, Genres;

-- Create all tables, incl constraints

CREATE TABLE Languages(
  LKey CHAR(2) PRIMARY KEY,
  Name VARCHAR(128) UNIQUE NOT NULL
);

CREATE TABLE Countries(
  CoKey CHAR(2) PRIMARY KEY,
  Name VARCHAR(128) UNIQUE NOT NULL
);


CREATE TABLE Actors(
  -- NOTE: AKey SERIAL would be better, but we keep VARCHAR for simplicity
  AKey VARCHAR(24) PRIMARY KEY,
  Name VARCHAR(256) NOT NULL,
  Gender INT
);

CREATE TABLE Movies(
  MKey INT PRIMARY KEY,
  OrigTitle VARCHAR(256) NOT NULL,
  EngTitle VARCHAR(256) NOT NULL,
  RDate Date,
  Budget INT,
  Revenue INT,
  Runtime INT,
  Homepage VARCHAR(256),
  OrigLang CHAR(2) REFERENCES Languages
);

CREATE TABLE Casts(
  AKey VARCHAR REFERENCES Actors,
  MKey INT REFERENCES Movies,
  Character VARCHAR(256),
  PRIMARY KEY(AKey, MKey, Character)
);

CREATE TABLE ProducedIn(
  MKey INT REFERENCES Movies,
  CoKey CHAR(2) REFERENCES Countries,
  PRIMARY KEY(MKey, CoKey)
);

CREATE TABLE Spoken(
  MKey INT REFERENCES Movies,
  LKey CHAR(2) REFERENCES Languages,
  PRIMARY KEY(MKey, LKey)
);

CREATE TABLE Genres(
  GKey SERIAL PRIMARY KEY,
  Name VARCHAR(256) UNIQUE NOT NULL
);

CREATE TABLE MovieGenre(
  MKey INT REFERENCES Movies,
  GKey INT REFERENCES Genres,
  PRIMARY KEY(MKey, GKey)
);

CREATE TABLE Ratings(
  RKey SERIAL PRIMARY KEY,
  UKey INT NOT NULL,
  Rating FLOAT NOT NULL,
  RDate Date NOT NULL,
  MKey INT REFERENCES Movies,
  UNIQUE(MKey, UKey),
  CHECK(Rating >= 0.5 AND Rating <= 5.0)
);
