-- Database: mjuk
-- mjuk@localhost

-- CREATE DATABASE mjuk;
-- USE mjuk;

--
-- Drop tables and sequences
--
DROP TABLE Album    CASCADE;
DROP TABLE Genre    CASCADE;
DROP TABLE Location CASCADE;
DROP TABLE Privs    CASCADE;
DROP TABLE Users    CASCADE;
DROP TABLE Track    CASCADE;
DROP TABLE Record;
DROP TABLE Artist   CASCADE;
DROP TABLE AlbumGenre;
DROP TABLE ArtistGenre;
DROP TABLE TrackGenre;
DROP TABLE UserGenreRate;
DROP TABLE UserLog;
DROP TABLE UserPriv;
DROP TABLE UserTrackRate;
DROP TABLE TrackLog;
DROP TABLE TrackArtist;
DROP TABLE MemberArtist;
DROP TABLE AlbumArtist;
DROP TABLE MimeType;


--
-- Table structure for table 'Album'
--
CREATE TABLE Album (
  albumId bigserial,
  name varchar(255) NOT NULL,
  date date,

  PRIMARY KEY (albumId)
);

--
-- Table structure for table 'Genre'
--
CREATE TABLE Genre (
  genreId bigserial,
  name varchar(255) NOT NULL,
  description varchar(255),
  lambdaweight text,

  PRIMARY KEY  (genreId)
);

--
-- Table structure for table 'Location'
--
CREATE TABLE Location (
  locationId bigserial,
  description varchar(255) NOT NULL,

  PRIMARY KEY  (locationId)
);

--
-- Table structure for table 'Privs'
--
CREATE TABLE Privs (
  privId bigserial,
  name varchar(255) NOT NULL,

  PRIMARY KEY  (privId)
);

--
-- Table structure for table 'Users'
--
CREATE TABLE Users (
  userId bigserial,
  username varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  name varchar(255),
  mail varchar(255),

  PRIMARY KEY  (userId)
);

--
-- Table structure for table 'Track'
--
CREATE TABLE Track (
  trackId bigserial,
  recordId bigint,
  trackno smallint,
  title text,
  length time,
  year smallint,
  URI text,
  fail bool,
  gain float,
  manualgain float,
  ripinfo text,
  fileMD5sum varchar(255),
  recordUpdateTime timestamp,
  mimeID bigint,

  PRIMARY KEY  (trackId)
);

--
-- Table structure for table 'Record'
--
CREATE TABLE Record (
  recordId bigserial,
  albumId bigint,
  volume smallint,
  length time,
  URI text,
  gain float,
  ripinfo text,

  PRIMARY KEY  (recordId)
);

--
-- Table structure for table 'Artist'
--

CREATE TABLE Artist (
  artistId bigserial,
  name varchar(255) NOT NULL,
  hompage text,

  PRIMARY KEY (artistId)
);

--
-- Table structure for table 'AlbumGenre'
--

CREATE TABLE AlbumGenre (
  id bigserial,
  albumId bigint,
  genreId bigint,

--  INDEX albumId_ind (albumId),
--  INDEX genreId_ind (genreId),
  PRIMARY KEY  (id),
  FOREIGN KEY (albumId) REFERENCES Album(albumId),
  FOREIGN KEY (genreId) REFERENCES Genre(genreId)
);

--
-- Table structure for table 'ArtistGenre'
--

CREATE TABLE ArtistGenre (
  id bigserial,
  artistId bigint,
  genreId bigint,

--  INDEX artistId_ind (artistId),
--  INDEX genreId_ind (genreId),
  PRIMARY KEY  (id),
  FOREIGN KEY (artistId) REFERENCES Artist(artistId),
  FOREIGN KEY (genreId) REFERENCES Genre(genreId)
);


--
-- Table structure for table 'TrackGenre'
--

CREATE TABLE TrackGenre (
  id bigserial,
  trackId bigint,
  genreId bigint,

--  INDEX trackId_ind (trackId),
--  INDEX genreId_ind (genreId),
  PRIMARY KEY  (id),
  FOREIGN KEY (trackId) REFERENCES Track(trackId),
  FOREIGN KEY (genreId) REFERENCES Genre(genreId)
);

--
-- Table structure for table 'UserGenreRate'
--

CREATE TABLE UserGenreRate (
  id bigserial,
  userId bigint,
  genreId bigint,

--  INDEX userId_ind (userId),
--  INDEX genreId_ind (genreId),
  PRIMARY KEY  (id),
  FOREIGN KEY (userId) REFERENCES Users(userId),
  FOREIGN KEY (genreId) REFERENCES Genre(genreId)

);

--
-- Table structure for table 'UserLog'
--

CREATE TABLE UserLog (
  id bigserial,
  userId bigint,
  locationId bigint,
  logintime timestamp(14) NOT NULL,
  logouttime timestamp(14) NOT NULL,

--  INDEX userId_ind (userId),
--  INDEX locationId_ind (locationId),
  PRIMARY KEY  (id),
  FOREIGN KEY (userId) REFERENCES Users(userId),
  FOREIGN KEY (locationId) REFERENCES Location(locationId)

);

--
-- Table structure for table 'UserPriv'
--

CREATE TABLE UserPriv (
  userId bigserial,
  privId bigint,

--  INDEX userId_ind (userId),
--  INDEX privid_ind (privId),
  FOREIGN KEY (userId) REFERENCES Users(userId),
  FOREIGN KEY (privId) REFERENCES Privs(privId)
);

--
-- Table structure for table 'UserTrackRate'
--

CREATE TABLE UserTrackRate (
  id bigserial,
  userId bigint,
  trackId bigint,
  rate smallint default NULL,

--  INDEX userId_ind (userId),
--  INDEX trackId_ind (trackId),
  PRIMARY KEY  (id),
  FOREIGN KEY (userId) REFERENCES Users(userId),
  FOREIGN KEY (trackId) REFERENCES Track(trackId)

);

--
-- Table structure for table 'TrackLog'
--

CREATE TABLE TrackLog (
  id bigserial,
  trackId bigint,
  locationId bigint,
  time timestamp(14) NOT NULL,

--  INDEX trackId_ind (trackId),
--  INDEX locationId_ind (locationId),
  PRIMARY KEY  (id),
  FOREIGN KEY (trackId) REFERENCES Track(trackId),
  FOREIGN KEY (locationId) REFERENCES Location(locationId)
);

--
-- Table structure for table 'TrackArtist'
--

CREATE TABLE TrackArtist (
  id bigserial,
  trackId bigint,
  artistId bigint,

--  INDEX trackId_ind (trackId),
--  INDEX artistId_ind (artistId),
  PRIMARY KEY  (id),
  FOREIGN KEY (trackId) REFERENCES Track(trackId),
  FOREIGN KEY (artistId) REFERENCES Artist(artistId)
);

--
-- Table structure for table 'MemberArtist'
--

CREATE TABLE MemberArtist (
  id bigserial,
  artistId bigint,
  groupId bigint,

--  INDEX artist_ind (artistId),
--  INDEX group_ind (groupId),
  PRIMARY KEY  (id),
  FOREIGN KEY (artistId) REFERENCES Artist(artistId),
  FOREIGN KEY (groupId) REFERENCES Artist(artistId)
);


--
-- Table structure for table 'AlbumArtist'
--

CREATE TABLE AlbumArtist (
  id bigserial,
  albumId  bigint not null,
  artistId bigint not null,

--  INDEX albumId_ind (albumId),
--  INDEX artistId_ind (artistId),
  PRIMARY KEY  (id),
  FOREIGN KEY (albumId) REFERENCES Album(albumId),
  FOREIGN KEY (artistId) REFERENCES Artist(artistId)
);

--
-- Table structure for table 'MimeType'
--

CREATE TABLE MimeType (
  mimeId bigserial,
  main varchar(255),
  sub varchar (255),

  PRIMARY KEY  (mimeId)
);