USE MASTER;
GO

BEGIN TRY
    DROP DATABASE SPOTIFY;
END TRY
BEGIN CATCH
end CATCH;

CREATE DATABASE SPOTIFY;
GO

USE SPOTIFY;
GO

CREATE TABLE MUSICA(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    NOME VARCHAR(50) NOT NULL,
    DURACAO TIME NOT NULL,
    ANO INT
);

CREATE TABLE CANTOR(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    NOME VARCHAR(100) NOT NULL,
    NACIONALIDADE VARCHAR(20) NOT NULL
);

CREATE TABLE MUSICA_CANTOR(
    MUSICA_ID INT NOT NULL,
    CANTOR_ID INT NOT NULL,
    PRIMARY KEY(MUSICA_ID, CANTOR_ID)
);
--ALTERANDO A TABELA DE RELACIONAMENTOS PARA ADICIONAR A FK DE MUSICA
ALTER TABLE MUSICA_CANTOR
ADD CONSTRAINT FK_MUSICA
FOREIGN KEY (MUSICA_ID)
REFERENCES MUSICA(ID);

--ALTERANDO A TABELA DE RELACIONAMENTOS PARA ADICIONAR A FK DE CANTOR
ALTER TABLE MUSICA_CANTOR
ADD CONSTRAINT FK_CANTOR
FOREIGN KEY (CANTOR_ID)
REFERENCES CANTOR(ID);

--POPULAR DADOS DO CANTOR
INSERT INTO
    CANTOR(NOME, NACIONALIDADE)
VALUES
    ('Luan Santana', 'Brasileira'),
    ('Marilia Mendonça', 'Brasileira'),
    ('Jorge e Mateus', 'Brasileira'),
    ('Felipe Original', 'Brasileira'),
    ('MC Kevin o Cris', 'Brasileira'),
    ('João Gustavo e Murilo','Brasileira'),
    ('Matuê', 'Brasileira'),
    ('Matheus e Kauan', 'Brasileira'),
    ('Shawn Mendes', 'Canadense'),
    ('Camila Cabello', 'Cubana'),
    ('Post Malone', 'Americana');

--POPULAR DADOS DAS MUSICAS
INSERT INTO
    MUSICA(NOME, ANO, DURACAO)
VALUES
    ('quando a bad bater - ao vivo', 2019, '00:02:50'),
    ('Supera - Ao vivo', 2019, '00:03:50'),
    ('Tijolão - Ao vivo', 2019, '00:03:43'),
    ('Hit Contagiante', 2019, '00:01:00'),
    ('Lençol Dobrado', 2019, '00:03:55'),
    ('Kenny G', 2019, '00:02:55'),
    ('Quarta Cadeira - Ao vivo', 2019, '00:03:21'),
    ('Señorita', 2019, '00:04:01'),
    ('Circles', 2019, '00:03:51');


--CRIAR E POPULAR TABELA DE ESTILOS MUSICAIS
CREATE TABLE ESTILO_MUSICAL(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    NOME VARCHAR(50)
);

INSERT INTO
    ESTILO_MUSICAL (NOME)
VALUES
    ('Sertanejo'),
    ('Rock'),
    ('Podcast'),
    ('Eletrônica/Dance'),
    ('Indie'),
    ('Para dormir'),
    ('Punk');

-- CRIAR E POPULAR TABELAS DE USUARIOS

CREATE TABLE USUARIO(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    NOME VARCHAR(100) NOT NULL,
    ULTIMO_ACESSO DATETIME,
    EMAIL VARCHAR(100) NOT NULL,
    EMAIL_CONFIRMADO BIT NOT NULL DEFAULT 0,
    DATA_CADASTRO DATETIME NOT NULL DEFAULT GETDATE(),
    SENHA VARCHAR(50) NOT NULL
);

INSERT INTO
    USUARIO(NOME, EMAIL, SENHA)
VALUES
    ('João Antonio Bulgareli', 'jabulgareli@gmail.com', 'BD2019'),
    ('Walison Barberá', 'fafibe@gmail.com', 'FAFIBE');

INSERT INTO
    USUARIO(NOME, EMAIL, SENHA)
VALUES
    ('Jean Marcelo', 'jean@gmail.com', (SELECT HASHBYTES('MD5', 'FAFIBE3')));

--CRIAR, RELACIONAR E POPULAR PLAYLIST
CREATE TABLE PLAYLIST(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    NOME VARCHAR(100) NOT NULL,
    USUARIO_ID INT,
    ESTILO_MUSICAL_ID INT NOT NULL
);

ALTER TABLE PLAYLIST
ADD CONSTRAINT FK_ESTILO_MUSICAL
FOREIGN KEY (ESTILO_MUSICAL_ID)
REFERENCES ESTILO_MUSICAL(ID);

ALTER TABLE PLAYLIST
ADD CONSTRAINT FK_USUARIO
FOREIGN KEY (USUARIO_ID)
REFERENCES USUARIO(ID);



CREATE TABLE PLAYLIST_MUSICA(
    PLAYLIST_ID INT NOT NULL,
    MUSICA_ID INT NOT NULL,
    ADICIONADA_EM DATETIME NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (PLAYLIST_ID, MUSICA_ID)
);

ALTER TABLE PLAYLIST_MUSICA
ADD CONSTRAINT FK_PLAYLIST_MUSICA_PLAYLIST
FOREIGN KEY (PLAYLIST_ID)
REFERENCES PLAYLIST(ID);

ALTER TABLE PLAYLIST_MUSICA
ADD CONSTRAINT FK_PLAYLIST_MUSICA_MUSICA
FOREIGN KEY (MUSICA_ID)
REFERENCES MUSICA(ID);

-- Paramos aqui

INSERT INTO ESTILO_MUSICAL(NOME) VALUES ('Ranking');

INSERT INTO PLAYLIST(ESTILO_MUSICAL_ID, NOME, USUARIO_ID)
VALUES  (1, 'Sertanejo pra viajar', 1),
        (3, 'Eletronicas para corrigir prova', 2),
        (8, 'As mais tocadas do Brasil', NULL),
        (8, 'As mais tocadas dos Estados Unidos', NULL);

INSERT INTO PLAYLIST_MUSICA(PLAYLIST_ID, MUSICA_ID)
VALUES (1, 1),
       (1, 2),
       (1, 3),
       (2, 8),
       (2, 9),
       (3, 1),
       (3, 2),
       (3, 3),
       (3, 4),
       (3, 5),
       (3, 6),
       (3, 7),
       (4, 8),
       (4, 9);

INSERT INTO MUSICA_CANTOR (CANTOR_ID, MUSICA_ID)
VALUES (1, 1),
       (2, 2),
       (3, 3),
       (4, 4),
       (5, 4),
       (6, 5),
       (7, 6),
       (8, 7),
       (3, 7),
       (9, 8),
       (10, 8),
       (11, 9);

INSERT INTO USUARIO(EMAIL, NOME, SENHA)
VALUES ('bruna@gmail.com', 'Bruna', '123'),
       ('antonio@gmail.com', 'Antonio', '123');

CREATE TABLE FAMILIA (
    USUARIO_ID_ADMINISTRADOR INT NOT NULL,
    USUARIO_ID_INTEGRANTE INT NOT NULL,
    PRIMARY KEY (USUARIO_ID_ADMINISTRADOR, USUARIO_ID_INTEGRANTE)
);

ALTER TABLE FAMILIA ADD CONSTRAINT FK_USUARIO_USUARIO_ADMINISTRADOR FOREIGN KEY (USUARIO_ID_ADMINISTRADOR) REFERENCES USUARIO(ID);
ALTER TABLE FAMILIA ADD CONSTRAINT FK_USUARIO_USUARIO_INTEGRANTE FOREIGN KEY (USUARIO_ID_ADMINISTRADOR) REFERENCES USUARIO(ID);

INSERT INTO FAMILIA VALUES (1, 3), (1, 4);

CREATE TABLE PLANO (
    ID INT NOT NULL PRIMARY KEY IDENTITY,
    NOME VARCHAR(100) NOT NULL,
    PRECO DECIMAL (14,2) NOT NULL
);

INSERT INTO PLANO (NOME, PRECO) VALUES ('Free', 0.00), ('Família', 29.90), ('Estudante', 19.90);
ALTER TABLE USUARIO ADD PLANO_ID INT NOT NULL DEFAULT 1;
ALTER TABLE USUARIO ADD CONSTRAINT FK_PLANO FOREIGN KEY (PLANO_ID) REFERENCES PLANO(ID);

CREATE TABLE FATURA (
    ID INT NOT NULL PRIMARY KEY IDENTITY,
    USUARIO_ID INT NOT NULL,
    CRIADA_EM DATETIME NOT NULL,
    VENCIMENTO_EM DATETIME NOT NULL,
    PAGAMENTO_REALIZADO_EM DATETIME,
    VALOR DECIMAL(14,2) DEFAULT 0);


INSERT INTO FATURA (USUARIO_ID, CRIADA_EM, VENCIMENTO_EM, VALOR)
SELECT ID, GETDATE(), DATEADD(DAY, 15, GETDATE()), 19.99 FROM USUARIO

INSERT INTO FATURA (USUARIO_ID, CRIADA_EM, VENCIMENTO_EM, VALOR)
SELECT ID, '2019-11-09', DATEADD(DAY, 15, '2019-11-09'), 21.01 FROM USUARIO

INSERT INTO FATURA (USUARIO_ID, CRIADA_EM, VENCIMENTO_EM, VALOR)
SELECT ID, '2019-08-09', DATEADD(DAY, 15, '2019-08-09'), 19.99 FROM USUARIO


/*
Relatório: Usuários Ativos
Campos: Código
*/

SELECT * FROM USUARIO;

SELECT
ID AS 'CÓDIGO',
NOME AS 'NOME',
DATA_CADASTRO AS 'DATA DE CADASTRO'
FROM
USUARIO
WHERE
EMAIL_CONFIRMADO = 1;


--CONFIRMANDO *TODOS* OS EMAILS LEMBREM DO WHERE
UPDATE USUARIO
SET
EMAIL_CONFIRMADO = 1,
ULTIMO_ACESSO = GETDATE();

UPDATE
USUARIO
SET
NOME = 'BRUNA LETICIA'
WHERE
ID = 4;


--COMECANDO UMA TRANSACAO
BEGIN TRAN;

--UPDATE ERRADO
UPDATE USUARIO SET NOME = 'NOME ERRADO';

--VOLTA AS ALTERAÇÕES (CTRL z)
ROLLBACK;

--EFETIVA A TRANSAÇÃO
COMMIT;

--REGISTRAR NOVO USUÁRIO
INSERT INTO
USUARIO(EMAIL, NOME, SENHA)
VALUES
('CESAR@GMAIL.COM', 'CESAR', '1231231');

/* RELATÓRIO: USÁRIOS POR PLANO
CAMPOS: CÓDIGO, NOME, QUANTIDADE DE USUÁRIOS
*/

SELECT * FROM USUARIO;
SELECT * FROM PLANO;

SELECT
PLANO.ID AS 'CÓDIGO',
PLANO.NOME AS 'NOME',
COUNT(*) AS 'QUANTIDADE DE USUARIOS'
FROM
PLANO
INNER JOIN
USUARIO ON USUARIO.PLANO_ID = PLANO.ID
GROUP BY
PLANO.ID, PLANO.NOME;

/*
RELATÓRIO: RESTIMATIVA DE LUCRO POR PLANO
CAMPOS: CÓDIGO, NOME, QUANTIDADE DE USUÁRIOS, PREÇO DO PLANO, A ESTIMATIVA DE LUCRO
*/

SELECT
PLANO.ID AS 'CÓDIGO',
PLANO.NOME AS 'NOME',
COUNT(*) AS 'QTD DE USUÁRIOS'

SELECT *
FROM
PLANO
LEFT JOIN
USUARIO ON USUARIO.PLANO_ID = PLANO.ID