CREATE TABLE IF NOT EXISTS platform (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) UNIQUE NOT NULL,
	url VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS language (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS update_period (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS owner (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	url VARCHAR(255) NOT NULL UNIQUE,
	platform INT NOT NULL,
	CONSTRAINT fk_owner_platform FOREIGN KEY (platform) REFERENCES platform(id)
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS entry (
	id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(255) NOT NULL,
	synopsis VARCHAR(255) NOT NULL,
	owner INT NOT NULL,
	platform INT NOT NULL,
	url VARCHAR(255) UNIQUE NOT NULL,
	language INT NOT NULL,
	stars VARCHAR(255) NOT NULL,
	update_period INT NOT NULL,
	created_at TIMESTAMP NOT NULL,
	CONSTRAINT fk_entry_owner FOREIGN KEY (owner) REFERENCES owner(id),
	CONSTRAINT fk_entry_platform FOREIGN KEY (platform) REFERENCES platform(id),
	CONSTRAINT fk_entry_language FOREIGN KEY (language) REFERENCES language(id),
	CONSTRAINT fk_entry_period FOREIGN KEY (update_period) REFERENCES update_period(id)
) CHARSET=utf8;

CREATE OR REPLACE VIEW entry_view AS
SELECT
        entry.id,
        title,
        synopsis,
        owner.name AS owner,
        platform.name AS platform,
        platform.url AS platform_url,
        CONCAT( platform.url, entry.url) AS url,
        language.name AS language,
        stars,
        created_at
FROM entry
JOIN owner ON
        entry.owner = owner.id
JOIN platform on
        entry.platform = platform.id
JOIN language
        ON entry.language = language.id
ORDER BY created_at DESC;