-- Base de datos CRM - Versión Actualizada
-- Compatible con MySQL y MariaDB
-- Incluye todas las funcionalidades implementadas: estados, etiquetas, historial, etc.

-- Crear base de datos si no existe


-- Eliminar tablas si existen (para recrear)

-- Tabla de contactos (con todos los campos implementados)
CREATE TABLE contactos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NOT NULL,
    nombres_apellidos VARCHAR(200) NOT NULL,
    celular VARCHAR(20) NOT NULL,
    ciudad VARCHAR(100) NOT NULL,
    fecha_registro DATE NOT NULL,
    estado ENUM('Frio', 'Tibio', 'Caliente', 'Separó', 'Compró') DEFAULT 'Frio',
    observaciones TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    activo BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

-- Tabla de etiquetas
CREATE TABLE etiquetas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL UNIQUE,
    color VARCHAR(7) DEFAULT '#007bff',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de relación contactos-etiquetas
CREATE TABLE contacto_etiquetas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contacto_id INT NOT NULL,
    etiqueta_id INT NOT NULL,
    fecha_asignacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contacto_id) REFERENCES contactos(id) ON DELETE CASCADE,
    FOREIGN KEY (etiqueta_id) REFERENCES etiquetas(id) ON DELETE CASCADE,
    UNIQUE KEY unique_contacto_etiqueta (contacto_id, etiqueta_id)
);

-- Tabla de historial de cambios
CREATE TABLE historial_cambios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contacto_id INT NOT NULL,
    usuario_id INT NOT NULL,
    campo_modificado VARCHAR(100) NOT NULL,
    valor_anterior TEXT,
    valor_nuevo TEXT,
    fecha_cambio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contacto_id) REFERENCES contactos(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

-- Índices para mejorar rendimiento
CREATE INDEX idx_usuario_contactos ON contactos(usuario_id);
CREATE INDEX idx_fecha_registro ON contactos(fecha_registro);
CREATE INDEX idx_ciudad ON contactos(ciudad);
CREATE INDEX idx_estado ON contactos(estado);
CREATE INDEX idx_activo ON contactos(activo);
CREATE INDEX idx_contacto_etiquetas ON contacto_etiquetas(contacto_id);
CREATE INDEX idx_etiqueta_contactos ON contacto_etiquetas(etiqueta_id);
CREATE INDEX idx_historial_contacto ON historial_cambios(contacto_id);
CREATE INDEX idx_historial_fecha ON historial_cambios(fecha_cambio);
CREATE INDEX idx_historial_usuario ON historial_cambios(usuario_id);

-- Insertar etiquetas de ejemplo
INSERT INTO etiquetas (nombre, color) VALUES 
('Cliente VIP', '#28a745'),
('Prospecto', '#ffc107'),
('Seguimiento', '#17a2b8'),
('Urgente', '#dc3545'),
('Interesado', '#6f42c1');

-- Insertar algunos contactos de ejemplo (con todos los estados)
INSERT INTO contactos (usuario_id, nombres_apellidos, celular, ciudad, fecha_registro, estado, observaciones) VALUES 
(2, 'Carlos Rodríguez', '3001234567', 'Bogotá', '2024-01-15', 'Compró', 'Cliente que completó la compra exitosamente'),
(2, 'Ana López', '3009876543', 'Medellín', '2024-01-20', 'Separó', 'Cliente que separó la propiedad'),
(3, 'Pedro Martínez', '3005555555', 'Cali', '2024-01-25', 'Caliente', 'Cliente muy interesado, próximo a decidir'),
(2, 'Laura Sánchez', '3007777777', 'Barranquilla', '2024-02-01', 'Tibio', 'Necesita más información sobre financiación'),
(3, 'Diego Torres', '3008888888', 'Cartagena', '2024-02-05', 'Frio', 'Primer contacto, requiere seguimiento'),
(2, 'María Fernández', '3001111111', 'Bucaramanga', '2024-02-10', 'Caliente', 'Interesada en apartamento de 3 habitaciones'),
(3, 'José García', '3002222222', 'Pereira', '2024-02-12', 'Tibio', 'Evaluando opciones de inversión');

-- Asignar etiquetas a contactos de ejemplo
INSERT INTO contacto_etiquetas (contacto_id, etiqueta_id) VALUES 
(1, 1), -- Carlos: Cliente VIP (Compró)
(1, 4), -- Carlos: Urgente
(2, 1), -- Ana: Cliente VIP (Separó)
(2, 2), -- Ana: Prospecto
(3, 3), -- Pedro: Seguimiento
(3, 5), -- Pedro: Interesado
(4, 2), -- Laura: Prospecto
(5, 3), -- Diego: Seguimiento
(6, 1), -- María: Cliente VIP
(6, 4), -- María: Urgente
(7, 5); -- José: Interesado

-- Insertar historial de cambios de ejemplo
INSERT INTO historial_cambios (contacto_id, usuario_id, campo_modificado, valor_anterior, valor_nuevo, fecha_cambio) VALUES 
(1, 2, 'estado', 'Caliente', 'Compró', '2024-01-16 10:30:00'),
(2, 2, 'estado', 'Tibio', 'Separó', '2024-01-21 14:15:00'),
(3, 3, 'observaciones', 'Primer contacto realizado', 'Cliente muy interesado, próximo a decidir', '2024-01-26 09:45:00'),
(4, 2, 'estado', 'Caliente', 'Tibio', '2024-02-02 16:20:00'),
(6, 2, 'etiquetas', '', 'Cliente VIP, Urgente', '2024-02-10 11:00:00');

-- Insertar más etiquetas útiles
INSERT INTO etiquetas (nombre, color) VALUES 
('Referido', '#fd7e14'),
('Corporativo', '#20c997'),
('Primera Vivienda', '#e83e8c'),
('Inversionista', '#6610f2'),
('Contacto Frío', '#6c757d');

-- Verificar que todo se creó correctamente
SELECT 'Base de datos creada exitosamente' as mensaje;
SELECT COUNT(*) as total_contactos FROM contactos;
SELECT COUNT(*) as total_etiquetas FROM etiquetas;