CREATE TABLE Usuario (
    id_usuario INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    telefone TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE,
    foto_perfil TEXT
);

CREATE TABLE Contato (
    id_contato INTEGER PRIMARY KEY AUTOINCREMENT,
    id_usuario INTEGER NOT NULL,
    id_contato_usuario INTEGER NOT NULL,

    FOREIGN KEY (id_usuario) REFERENCES Usuario(id_usuario),
    FOREIGN KEY (id_contato_usuario) REFERENCES Usuario(id_usuario)
);

CREATE TABLE Grupo_Chat (
    id_grupo INTEGER PRIMARY KEY AUTOINCREMENT,
    nome_grupo TEXT NOT NULL,
    descricao TEXT,
    data_criacao DATE
);

CREATE TABLE Participante_Grupo (
    id_participante INTEGER PRIMARY KEY AUTOINCREMENT,
    id_grupo INTEGER NOT NULL,
    id_usuario INTEGER NOT NULL,
    data_entrada DATE,
    data_saida DATE,

    FOREIGN KEY (id_grupo) REFERENCES Grupo_Chat(id_grupo),
    FOREIGN KEY (id_usuario) REFERENCES Usuario(id_usuario)
);

CREATE TABLE Conversa (
    id_conversa INTEGER PRIMARY KEY AUTOINCREMENT,
    tipo TEXT NOT NULL CHECK(tipo IN ('Privada','Grupo')),
    id_grupo INTEGER,

    FOREIGN KEY (id_grupo) REFERENCES Grupo_Chat(id_grupo)
);

CREATE TABLE Mensagem (
    id_mensagem INTEGER PRIMARY KEY AUTOINCREMENT,
    id_conversa INTEGER NOT NULL,
    id_remetente INTEGER NOT NULL,
    texto TEXT,
    data_envio DATE,
    hora_envio TIME,
    status TEXT NOT NULL CHECK(status IN ('Enviada','Entregue','Lida')),

    FOREIGN KEY (id_conversa) REFERENCES Conversa(id_conversa),
    FOREIGN KEY (id_remetente) REFERENCES Usuario(id_usuario)
);

CREATE TABLE Arquivo (
    id_arquivo INTEGER PRIMARY KEY AUTOINCREMENT,
    id_mensagem INTEGER NOT NULL,
    nome_arquivo TEXT,
    tipo_arquivo TEXT,
    caminho TEXT,

    FOREIGN KEY (id_mensagem) REFERENCES Mensagem(id_mensagem)
);

CREATE TABLE Historico_Conversa (
    id_historico INTEGER PRIMARY KEY AUTOINCREMENT,
    id_conversa INTEGER NOT NULL,
    id_usuario INTEGER NOT NULL,

    FOREIGN KEY (id_conversa) REFERENCES Conversa(id_conversa),
    FOREIGN KEY (id_usuario) REFERENCES Usuario(id_usuario)
);

INSERT INTO Usuario (nome, telefone, email)
VALUES
('Ana','71999990001','ana@email.com'),
('Bruno','71999990002','bruno@email.com'),
('Carlos','71999990003','carlos@email.com');

INSERT INTO Contato(id_usuario,id_contato_usuario)
VALUES
(1,2),
(1,3),
(2,3);

INSERT INTO Grupo_Chat(nome_grupo,descricao,data_criacao)
VALUES
('IFBA ADS','Grupo da turma','2026-06-09');

INSERT INTO Participante_Grupo
(id_grupo,id_usuario,data_entrada)
VALUES
(1,1,'2026-06-09'),
(1,2,'2026-06-09'),
(1,3,'2026-06-09');

INSERT INTO Conversa(tipo,id_grupo)
VALUES
('Grupo',1),
('Privada',NULL);

INSERT INTO Historico_Conversa(id_conversa,id_usuario)
VALUES
(1,1),
(1,2),
(1,3),
(2,1),
(2,2);

INSERT INTO Mensagem
(id_conversa,id_remetente,texto,data_envio,hora_envio,status)
VALUES
(1,1,'Bom dia pessoal!','2026-06-09','08:30','Lida'),
(1,2,'Bom dia!','2026-06-09','08:31','Entregue'),
(2,1,'Oi Bruno','2026-06-09','09:00','Enviada');

INSERT INTO Arquivo
(id_mensagem,nome_arquivo,tipo_arquivo,caminho)
VALUES
(1,'foto.jpg','Imagem','/imagens/foto.jpg'),
(3,'documento.pdf','PDF','/arquivos/documento.pdf');


SELECT * FROM Usuario;

SELECT
u.nome AS Usuario,
c2.nome AS Contato
FROM Contato c
JOIN Usuario u
ON c.id_usuario = u.id_usuario
JOIN Usuario c2
ON c.id_contato_usuario = c2.id_usuario;

SELECT
g.nome_grupo,
u.nome
FROM Participante_Grupo pg
JOIN Grupo_Chat g
ON pg.id_grupo = g.id_grupo
JOIN Usuario u
ON pg.id_usuario = u.id_usuario;

SELECT
u.nome AS Remetente,
m.texto,
m.data_envio,
m.hora_envio,
m.status
FROM Mensagem m
JOIN Usuario u
ON m.id_remetente = u.id_usuario;


SELECT
u.nome,
a.nome_arquivo,
a.tipo_arquivo
FROM Arquivo a
JOIN Mensagem m
ON a.id_mensagem = m.id_mensagem
JOIN Usuario u
ON m.id_remetente = u.id_usuario;


SELECT
h.id_conversa,
u.nome
FROM Historico_Conversa h
JOIN Usuario u
ON h.id_usuario = u.id_usuario;