import.sql

-- -----------------------------------------------------
-- Função auxiliar pra obter o trimestre de uma data
-- -----------------------------------------------------
CREATE FUNCTION TRIMESTER(@dateVal DATETIME)
RETURNS INT
AS
BEGIN
	RETURN (MONTH(dateVal) - 1) / 3 + 1;
END;



START TRANSACTION;
USE `mydb`;

-- -----------------------------------------------------
-- Importa tabela Artist no OLAP
-- -----------------------------------------------------
INSERT INTO Artist_OLAP (idArtist, name, description)
	SELECT idArtist, name, description FROM Artist;

-- -----------------------------------------------------
-- Importa tabela Tag no OLAP
-- -----------------------------------------------------
INSERT INTO Tag_OLAP (idTag, name)
	SELECT idTag, name FROM Tag;

-- -----------------------------------------------------
-- Importa tabela Album no OLAP
-- -----------------------------------------------------
INSERT INTO Album_OLAP (idAlbum, title, release)
	SELECT idAlbum, title, release FROM Album;

-- -----------------------------------------------------
-- Importa tabela Song no OLAP
-- -----------------------------------------------------
INSERT INTO Song_OLAP (idSong, title, number, length)
	SELECT idSong, title, number, length FROM Song;

-- -----------------------------------------------------
-- Importa tabela User no OLAP
-- -----------------------------------------------------
INSERT INTO User_OLAP (login, name, gender, age, country)
	SELECT login, name, gender, age, country FROM User;

-- -----------------------------------------------------
-- Importa tabela Album_has_Song no OLAP
-- -----------------------------------------------------
INSERT INTO Album_has_Song_OLAP (Album_idAlbum, Song_idSong)
	SELECT Album_idAlbum, Song_idSong FROM Album_has_Song;

-- -----------------------------------------------------
-- Importa tabela Artist_is_in_Album no OLAP
-- -----------------------------------------------------
INSERT INTO Artist_is_in_Album_OLAP (Artist_idArtist, Album_idAlbum)
	SELECT Artist_idArtist, Album_idAlbum FROM Artist_is_in_Album;

-- -----------------------------------------------------
-- Importa tabela Tag_has_Artist no OLAP
-- -----------------------------------------------------
INSERT INTO Tag_has_Artist_OLAP (Tag_idTag, Artist_idArtist)
	SELECT Tag_idTag, Artist_idArtist FROM Tag_has_Artist;

-- -----------------------------------------------------
-- Cria entradas da tabela Time no OLAP
-- -----------------------------------------------------
INSERT INTO Time_OLAP (year, trim, month, day)
	SELECT UNIQUE YEAR(date), TRIMESTER(date), MONTH(date), DAY(date) FROM Play;

-- -----------------------------------------------------
-- Importar tabela Play no OLAP
-- -----------------------------------------------------
-- FIXME: to ignorando a tabela Location aqui, muito foda importar com aquilo
INSERT INTO Play_OLAP (Quantity, Time_idTime, Song_idSong, User_login)
	SELECT COUNT(DISTINCT date), (
		SELECT idTime FROM Time_OLAP
		WHERE year = YEAR(date) AND month = MONTH(date) AND day = DAY(date)
	), Song_idSong, User_login
	FROM Play GROUP BY DATE(date);

COMMIT;