-- 1. Creación de la Base de Datos (opcional, dependiendo del entorno)
CREATE DATABASE ChatDB;
USE ChatDB;

-- 2. Tabla: CONVERSACION
-- Representa el elemento <conversacion> del XML.
CREATE TABLE Conversacion (
    id INT AUTO_INCREMENT PRIMARY KEY,     -- Identificador único numérico
    uuid_xml VARCHAR(50),                  -- Para guardar el ID original del XML (ej. "c001")
    contacto VARCHAR(100) NOT NULL,        -- Atributo 'contacto'
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tabla: MENSAJE
-- Representa el elemento <mensaje>. Se vincula a una Conversacion.
CREATE TABLE Mensaje (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid_xml VARCHAR(50),                  -- ID original del XML (ej. "m101")
    conversacion_id INT NOT NULL,          -- Clave foránea hacia Conversacion
    remitente VARCHAR(100) NOT NULL,       -- Atributo 'remitente'
    fecha_hora DATETIME NOT NULL,          -- Combinación de atributos 'fecha' y 'hora'
    texto TEXT,                            -- Elemento hijo <texto>
    
    -- Definición de la relación (Foreign Key)
    CONSTRAINT fk_mensaje_conversacion 
        FOREIGN KEY (conversacion_id) 
        REFERENCES Conversacion(id)
        ON DELETE CASCADE                  -- Si se borra la conversación, se borran sus mensajes
);

-- 4. Tabla: ADJUNTO
-- Representa el elemento <adjunto>. Se vincula a un Mensaje.
CREATE TABLE Adjunto (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mensaje_id INT NOT NULL UNIQUE,        -- Clave foránea (UNIQUE fuerza relación 1:1 si se desea)
    tipo VARCHAR(50) NOT NULL,             -- Atributo 'tipo'
    url_archivo VARCHAR(255),              -- Campo extra útil en BD real (ruta del archivo)
    
    -- Definición de la relación
    CONSTRAINT fk_adjunto_mensaje
        FOREIGN KEY (mensaje_id)
        REFERENCES Mensaje(id)
        ON DELETE CASCADE
);

-- ==========================================
-- DATOS DE EJEMPLO (Basados en conversacion.xml)
-- ==========================================

-- Insertar Conversaciones
INSERT INTO Conversacion (uuid_xml, contacto) VALUES 
('c001', 'Ana López'),
('c002', 'Carlos Ruiz (Jefe)');

-- Insertar Mensajes de la Conversación c001 (Asumiendo que c001 tiene ID 1)
INSERT INTO Mensaje (uuid_xml, conversacion_id, remitente, fecha_hora, texto) VALUES
('m101', 1, 'Yo', '2025-01-15 09:15:30', 'Hola Ana, ¿puedes enviar los documentos del proyecto?'),
('m102', 1, 'Ana López', '2025-01-15 09:16:45', 'Claro, justo te lo estaba enviando. Revisa tu email.'),
('m103', 1, 'Yo', '2025-01-15 09:18:00', 'Recibido. ¡Gracias!');

-- Insertar Adjuntos para los mensajes anteriores (Asumiendo IDs 1, 2, 3)
INSERT INTO Adjunto (mensaje_id, tipo) VALUES
(1, 'ninguno'),
(2, 'email'),
(3, 'ninguno');

-- Insertar Mensajes de la Conversación c002 (Asumiendo que c002 tiene ID 2)
INSERT INTO Mensaje (uuid_xml, conversacion_id, remitente, fecha_hora, texto) VALUES
('m201', 2, 'Carlos Ruiz (Jefe)', '2025-01-15 15:00:00', 'Reunión de emergencia en 15 minutos.'),
('m202', 2, 'Yo', '2025-01-15 15:00:45', 'Confirmado, estoy en ello.');

-- Insertar Adjuntos para la conversación 2 (IDs 4, 5)
INSERT INTO Adjunto (mensaje_id, tipo) VALUES
(4, 'urgente'),
(5, 'ninguno');