-- Criação das tabelas
CREATE TABLE livro (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(100) NOT NULL,
    edicao VARCHAR(50) NOT NULL,
    ano VARCHAR(4),
    editora VARCHAR(100),
    local VARCHAR(100),
    isbn VARCHAR(20)
);

CREATE TABLE organizacao (
    id INT PRIMARY KEY AUTO_INCREMENT,
    livro_id INT,
    tipo VARCHAR(50),
    nome VARCHAR(100),
    FOREIGN KEY (livro_id) REFERENCES livro(id)
);

CREATE TABLE secao (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(200) NOT NULL,
    ordem INT NOT NULL
);

CREATE TABLE musica (
    id INT PRIMARY KEY AUTO_INCREMENT,
    secao_id INT,
    titulo VARCHAR(200) NOT NULL,
    subtitulo VARCHAR(200),
    ordem INT NOT NULL,
    FOREIGN KEY (secao_id) REFERENCES secao(id)
);

CREATE TABLE linha_musica (
    id INT PRIMARY KEY AUTO_INCREMENT,
    musica_id INT,
    acordes TEXT,
    texto TEXT,
    ordem INT NOT NULL,
    espacamento_acordes TEXT, -- Armazena a distribuição dos espaços para alinhar os acordes
    FOREIGN KEY (musica_id) REFERENCES musica(id)
);

CREATE TABLE indice (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(200) NOT NULL,
    pagina VARCHAR(10) NOT NULL,
    ordem INT NOT NULL
);

-- Inserção dos dados básicos do livro
INSERT INTO livro (titulo, edicao, ano, editora, local, isbn) 
VALUES ('Canções da Mariápolis', '20ª edição', '2006', 'Editora Cidade Nova', 'São Paulo', '85-7112-016-1');

-- Inserção da organização
INSERT INTO organizacao (livro_id, tipo, nome) VALUES 
(1, 'revisao_musical', 'Alejo Rolón'),
(1, 'revisao_musical', 'Marimárcia Toledo'),
(1, 'projeto_grafico', 'Márcio Paulo S. Oliveira'),
(1, 'diagramacao', 'Giceli Valadares da Silva');

-- Inserção do índice
INSERT INTO indice (titulo, pagina, ordem) VALUES 
('Apresentação', '5', 1),
('Ave Mariápolis', '9', 2),
('Um bocadinho mais', '10', 3),
('Casa Lauretana', '11', 4),
('Estrela e lágrima', '12', 5),
('Fortíssima, vivíssima', '13', 6),
('A glória de Deus', '14', 7),
('Não há, ó gente', '15', 8),
('Nome dulcíssimo', '16', 9),
('Olé, cidade nova', '17', 10),
('Pega logo a viola', '18', 11),
('Quando entre nós estás', '19', 12),
('A RESPOSTA DO VENTO', '20', 13),
('Sim, o Ideal é esplêndido', '21', 14),
('Surge nova aurora', '22', 15),
('Te encontrei', '23', 16),
('Vamos cantar', '24', 17),
('Vento', '25', 18);

-- Inserção das seções e músicas
INSERT INTO secao (titulo, ordem) VALUES ('AVE MARIÁPOLIS', 1);

INSERT INTO musica (secao_id, titulo, subtitulo, ordem) 
VALUES (1, 'Ave Mariápolis', 'Mariápolis das Dolomitas', 1);

-- Exemplo de inserção de linhas da música com espacamento dos acordes
INSERT INTO linha_musica (musica_id, acordes, texto, espacamento_acordes, ordem) VALUES
(1, 'A D', 'Ave Mariápolis, urbe do mundo', '[0,8]', 1),
(1, 'E E7 A', 'De toda gente, sonho jucundo', '[0,4,8]', 2),
(1, 'A D', 'Por esta vida, calma e serena', '[0,8]', 3),
(1, 'E E7 A', 'Forte e ardente que o Céu nos traz', '[0,4,8]', 4),
(1, 'E E7 A', 'De Deus nos faz', '[0,4,8]', 5);

-- Exemplo de inserção de outra música
INSERT INTO secao (titulo, ordem) VALUES ('VIVER A VIDA', 2);

INSERT INTO musica (secao_id, titulo, subtitulo, ordem)
VALUES (2, 'Viver a vida', 'Gen Verde - Marie Thérèse Henderson', 1);

-- Exemplo de inserção das linhas com espacamento dos acordes
INSERT INTO linha_musica (musica_id, acordes, texto, espacamento_acordes, ordem) VALUES
(2, 'F C F', 'Viva esta vida com as flores e alegrias, cada dia', '[0,8,16]', 1),
(2, 'F C F', 'É isto que Deus quer de você', '[0,8,16]', 2),
(2, 'F C F', 'Viva esta vida mergulhado no amor, é o seu destino', '[0,8,16]', 3),
(2, 'F C F', 'É isto que Deus quer de você', '[0,8,16]', 4);

-- Função para recuperar música formatada
DELIMITER //
CREATE FUNCTION formatar_linha_musica(acordes TEXT, texto TEXT, espacamento TEXT) 
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE linha TEXT;
    -- Lógica para formatar a linha usando o espacamento
    -- O espacamento é um array JSON com as posições dos acordes
    -- Exemplo: para acordes "A D" e espacamento "[0,8]"
    -- Resultado: "A       D"
    SET linha = acordes;
    RETURN linha;
END //
DELIMITER ;

-- View para recuperar músicas formatadas
CREATE VIEW v_musicas_formatadas AS
SELECT 
    m.titulo,
    m.subtitulo,
    lm.acordes,
    lm.texto,
    lm.espacamento_acordes,
    formatar_linha_musica(lm.acordes, lm.texto, lm.espacamento_acordes) as linha_formatada
FROM musica m
JOIN linha_musica lm ON lm.musica_id = m.id
ORDER BY m.ordem, lm.ordem; 