CREATE DATABASE IF NOT EXISTS streamit CHARACTER SET utf8 COLLATE utf8_general_ci;

USE streamit;

CREATE TABLE catalog
(
    id INT(8) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(250) UNIQUE NOT NULL,
    cover VARCHAR(250) NOT NULL,
    type VARCHAR(50),
    collection VARCHAR(100),
    iid VARCHAR(12),
    genres VARCHAR(24),
    category VARCHAR(24)
);

CREATE TABLE genre
(
    id INT(8) AUTO_INCREMENT PRIMARY KEY,
    genre VARCHAR(50) NOT NULL
);

CREATE TABLE movie
(
    id INT(8) AUTO_INCREMENT PRIMARY KEY,
    video VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE serie 
(
    id INT(8) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(250) NOT NULL,
    episode VARCHAR(25),
    season VARCHAR(25),
    collection VARCHAR(250) NOT NULL,
    video VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE subtitle
(
    id INT(8) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(250) NOT NULL COMMENT 'BaseName of Subtitle file',
    language VARCHAR(25) NOT NULL,
    subtitle VARCHAR(250) UNIQUE NOT NULL,
    collection VARCHAR(250) NOT NULL,
    format VARCHAR(12) NOT NULL
);

CREATE TABLE summary
(
    id INT(8) AUTO_INCREMENT PRIMARY KEY,
    description TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
    language VARCHAR(16) NOT NULL,
    cId INT(8)
);

CREATE TABLE profiles
(
    guid VARCHAR(50) NOT NULL,
    username VARCHAR(50) UNIQUE KEY,
    image VARCHAR(200)
);

CREATE TABLE progress
(
    guid VARCHAR(50) NOT NULL,
    type VARCHAR(10) NOT NULL,
    title VARCHAR(100) NOT NULL,
    collection VARCHAR(50),
    season VARCHAR(3),
    episode VARCHAR(3),
    videoFile VARCHAR(100) NOT NULL,
    progress BIGINT NOT NULL,
    duration BIGINT NOT NULL,
    played BIGINT
);

-- Following code is required in order to access database
-- You are free to change the SQL password, but keep in mind that you will have to update the api file as well

CREATE USER 'streamitAccessUser'@'localhost' IDENTIFIED BY '519045bcb03016512302583548cb7888';
GRANT ALL PRIVILEGES ON streamit.* TO 'streamitAccessUser'@'localhost';
FLUSH PRIVILEGES;