-- ============================================
-- VISTAS ÚTILES PARA REPORTES
-- ============================================

      -- Vista: Oportunidades con información completa
      CREATE OR REPLACE VIEW `mod_crm_view_opp_full` AS
      SELECT 
          o.mod_crm_opp_id,
          o.mod_crm_opp_name,
          o.mod_crm_opp_amount,
          o.mod_crm_opp_weighted_amount,
          o.mod_crm_opp_currency,
          o.mod_crm_opp_probability,
          o.mod_crm_opp_status,
          o.mod_crm_opp_expected_close_date,
          o.mod_crm_opp_actual_close_date,
          c.mod_con_id,
          CONCAT_WS(' ', c.mod_con_name, c.mod_con_lastname_father, c.mod_con_lastname_mother) AS contact_full_name,
          c.mod_con_email,
          c.mod_con_company,
          p.mod_crm_pip_name AS pipeline_name,
          s.mod_crm_pst_name AS stage_name,
          s.mod_crm_pst_color AS stage_color,
          o.mod_crm_opp_assigned_user_id,
          -- u.user_name AS assigned_user_name, -- Comentado: tabla users no existe aún
          o.mod_crm_opp_created_at,
          o.mod_crm_opp_updated_at,
          DATEDIFF(COALESCE(o.mod_crm_opp_actual_close_date, CURDATE()), o.mod_crm_opp_created_at) AS days_in_pipeline
      FROM mod_crm_opportunities o
      INNER JOIN mod_contacts c ON o.mod_crm_opp_con_id = c.mod_con_id
      INNER JOIN mod_crm_pipelines p ON o.mod_crm_opp_pip_id = p.mod_crm_pip_id
      INNER JOIN mod_crm_pipeline_stages s ON o.mod_crm_opp_pst_id = s.mod_crm_pst_id
      -- LEFT JOIN users u ON o.mod_crm_opp_assigned_user_id = u.user_id -- Comentado: tabla users no existe aún
      WHERE o.mod_crm_opp_state = 0;

      -- Vista: Resumen de actividades por usuario
      CREATE OR REPLACE VIEW `mod_crm_view_act_summary` AS
      SELECT 
          mod_crm_act_assigned_user_id,
          mod_crm_act_type,
          mod_crm_act_status,
          COUNT(*) AS total_activities,
          SUM(CASE WHEN mod_crm_act_status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
          SUM(CASE WHEN mod_crm_act_status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
          SUM(CASE WHEN mod_crm_act_status = 'overdue' THEN 1 ELSE 0 END) AS overdue_count
      FROM mod_crm_activities
      WHERE mod_crm_act_state = 0
      GROUP BY mod_crm_act_assigned_user_id, mod_crm_act_type, mod_crm_act_status;

      -- Vista: Contactos con su último contacto
      CREATE OR REPLACE VIEW `mod_crm_view_con_last_interaction` AS
      SELECT 
    c.*,
    i.last_interaction_date,
    i.last_interaction_type
FROM mod_contacts c
LEFT JOIN (
    SELECT 
        mod_crm_int_con_id,
        MAX(mod_crm_int_created_at) AS last_interaction_date,
        (SELECT mod_crm_int_type FROM mod_crm_interactions 
         WHERE mod_crm_int_con_id = i2.mod_crm_int_con_id 
         ORDER BY mod_crm_int_created_at DESC LIMIT 1) AS last_interaction_type
    FROM mod_crm_interactions i2
    WHERE mod_crm_int_state = 0
    GROUP BY mod_crm_int_con_id
) i ON c.mod_con_id = i.mod_crm_int_con_id
WHERE c.mod_con_state = 0;

-- ============================================
-- TRIGGERS PARA AUTOMATIZACIÓN
-- ============================================

-- Trigger: Actualizar fecha de último contacto en contactos
DELIMITER //
CREATE TRIGGER `trg_update_last_contact_date`
AFTER INSERT ON `mod_crm_interactions`
FOR EACH ROW
BEGIN
    UPDATE mod_contacts 
    SET mod_con_last_contact_date = NEW.mod_crm_int_created_at
    WHERE mod_con_id = NEW.mod_crm_int_con_id;
END//
DELIMITER ;

-- Trigger: Incrementar contador de ejecuciones de workflow
DELIMITER //
CREATE TRIGGER `mod_crm_trg_increment_workflow_execution`
AFTER INSERT ON `mod_crm_workflow_logs`
FOR EACH ROW
BEGIN
    UPDATE mod_crm_workflows 
    SET mod_crm_wfl_execution_count = mod_crm_wfl_execution_count + 1,
        mod_crm_wfl_last_execution = NEW.mod_crm_wfl_log_executed_at
    WHERE mod_crm_wfl_id = NEW.mod_crm_wfl_log_wfl_id;
END//
DELIMITER ;

-- Trigger: Incrementar contador de uso de plantillas
DELIMITER //
CREATE TRIGGER `mod_crm_trg_increment_template_usage`
AFTER INSERT ON `mod_crm_interactions`
FOR EACH ROW
BEGIN
    -- Este trigger se puede expandir cuando se implemente el envío de emails con plantillas
    -- Por ahora es un placeholder para futuras funcionalidades
    IF NEW.mod_crm_int_type = 'email' THEN
        -- Lógica para incrementar contador de plantilla usada
        SET @template_used = 0; -- Placeholder
    END IF;
END//
DELIMITER ;

-- ============================================
-- STORED PROCEDURES ÚTILES
-- ============================================

-- Procedure: Mover oportunidad a siguiente etapa
DELIMITER //
CREATE PROCEDURE `mod_crm_sp_opp_move_next_stage`(
    IN p_opportunity_id INT,
    IN p_user_id INT
)
BEGIN
    DECLARE v_current_stage_id INT;
    DECLARE v_pipeline_id INT;
    DECLARE v_next_stage_id INT;
    DECLARE v_next_order INT;
    
    -- Obtener etapa y pipeline actual
    SELECT mod_crm_opp_pst_id, mod_crm_opp_pip_id
    INTO v_current_stage_id, v_pipeline_id
    FROM mod_crm_opportunities
    WHERE mod_crm_opp_id = p_opportunity_id;
    
    -- Obtener siguiente etapa
    SELECT mod_crm_pst_id INTO v_next_stage_id
    FROM mod_crm_pipeline_stages
    WHERE mod_crm_pst_pip_id = v_pipeline_id
    AND mod_crm_pst_order > (
        SELECT mod_crm_pst_order 
        FROM mod_crm_pipeline_stages 
        WHERE mod_crm_pst_id = v_current_stage_id
    )
    AND mod_crm_pst_state = 0
    ORDER BY mod_crm_pst_order ASC
    LIMIT 1;
    
    -- Actualizar oportunidad si hay siguiente etapa
    IF v_next_stage_id IS NOT NULL THEN
        UPDATE mod_crm_opportunities
        SET mod_crm_opp_pst_id = v_next_stage_id,
            mod_crm_opp_probability = (
                SELECT mod_crm_pst_probability 
                FROM mod_crm_pipeline_stages 
                WHERE mod_crm_pst_id = v_next_stage_id
            ),
            mod_crm_opp_updated_at = CURRENT_TIMESTAMP
        WHERE mod_crm_opp_id = p_opportunity_id;
        
        SELECT 'SUCCESS' AS result, v_next_stage_id AS new_stage_id;
    ELSE
        SELECT 'NO_NEXT_STAGE' AS result;
    END IF;
END//
DELIMITER ;

-- Procedure: Convertir contacto a cliente
DELIMITER //
CREATE PROCEDURE `mod_crm_sp_con_convert_to_customer`(
    IN p_contact_id INT,
    IN p_customer_id INT,
    IN p_customer_type ENUM('person','enterprise'),
    IN p_user_id INT
)
BEGIN
    IF p_customer_type = 'person' THEN
        UPDATE mod_contacts
        SET mod_con_type = 'customer',
            mod_con_status = 'converted',
            mod_con_cpe_id = p_customer_id,
            mod_con_conversion_date = CURRENT_TIMESTAMP
        WHERE mod_con_id = p_contact_id;
    ELSE
        UPDATE mod_contacts
        SET mod_con_type = 'customer',
            mod_con_status = 'converted',
            mod_con_cen_id = p_customer_id,
            mod_con_conversion_date = CURRENT_TIMESTAMP
        WHERE mod_con_id = p_contact_id;
    END IF;
    
    UPDATE mod_crm_opportunities
    SET mod_crm_opp_status = 'won',
        mod_crm_opp_actual_close_date = CURDATE()
    WHERE mod_crm_opp_con_id = p_contact_id
    AND mod_crm_opp_status = 'open';
    
    SELECT 'SUCCESS' AS result;
END//
DELIMITER ;

-- Procedure: Calcular lead score automático
DELIMITER //
CREATE PROCEDURE `mod_crm_sp_con_calculate_score`(
    IN p_contact_id INT
)
BEGIN
    DECLARE v_score INT DEFAULT 0;
    DECLARE v_interaction_count INT;
    DECLARE v_days_since_creation INT;
    DECLARE v_has_company BOOLEAN;
    DECLARE v_has_email BOOLEAN;
    DECLARE v_has_phone BOOLEAN;
    
    -- Obtener datos del contacto
    SELECT 
        (mod_con_company IS NOT NULL AND mod_con_company != '') AS has_company,
        (mod_con_email IS NOT NULL AND mod_con_email != '') AS has_email,
        (mod_con_phone IS NOT NULL AND mod_con_phone != '') AS has_phone,
        DATEDIFF(CURDATE(), DATE(mod_con_created_at)) AS days_since_creation
    INTO v_has_company, v_has_email, v_has_phone, v_days_since_creation
    FROM mod_contacts
    WHERE mod_con_id = p_contact_id;
    
    -- Contar interacciones
    SELECT COUNT(*) INTO v_interaction_count
    FROM mod_crm_interactions
    WHERE mod_crm_int_con_id = p_contact_id
    AND mod_crm_int_state = 0;
    
    -- Calcular score
    -- +20 puntos si tiene empresa
    IF v_has_company THEN
        SET v_score = v_score + 20;
    END IF;
    
    -- +15 puntos si tiene email
    IF v_has_email THEN
        SET v_score = v_score + 15;
    END IF;
    
    -- +10 puntos si tiene teléfono
    IF v_has_phone THEN
        SET v_score = v_score + 10;
    END IF;
    
    -- +5 puntos por cada interacción (máximo 30)
    SET v_score = v_score + LEAST(v_interaction_count * 5, 30);
    
    -- -2 puntos por cada día sin contacto (máximo -20)
    IF v_days_since_creation > 7 THEN
        SET v_score = v_score - LEAST((v_days_since_creation - 7) * 2, 20);
    END IF;
    
    -- Asegurar que el score esté entre 0 y 100
    SET v_score = GREATEST(0, LEAST(100, v_score));
    
    -- Actualizar score del contacto
    UPDATE mod_contacts
    SET mod_con_score = v_score
    WHERE mod_con_id = p_contact_id;
    
    SELECT 'SUCCESS' AS result, v_score AS calculated_score;
END//
DELIMITER ;

-- Procedure: Obtener estadísticas del pipeline
DELIMITER //
CREATE PROCEDURE `mod_crm_sp_pip_get_stats`(
    IN p_pipeline_id INT,
    IN p_entity_id INT
)
BEGIN
    SELECT 
        s.mod_crm_pst_id,
        s.mod_crm_pst_name,
        s.mod_crm_pst_color,
        s.mod_crm_pst_probability,
        COUNT(o.mod_crm_opp_id) AS opportunity_count,
        COALESCE(SUM(o.mod_crm_opp_amount), 0) AS total_amount,
        COALESCE(SUM(o.mod_crm_opp_weighted_amount), 0) AS total_weighted_amount,
        COALESCE(AVG(DATEDIFF(CURDATE(), o.mod_crm_opp_created_at)), 0) AS avg_days_in_stage
    FROM mod_crm_pipeline_stages s
    LEFT JOIN mod_crm_opportunities o ON s.mod_crm_pst_id = o.mod_crm_opp_pst_id
        AND o.mod_crm_opp_status = 'open'
        AND o.mod_crm_opp_state = 0
        AND o.mod_crm_opp_ent_id = p_entity_id
    WHERE s.mod_crm_pst_pip_id = p_pipeline_id
    AND s.mod_crm_pst_state = 0
    GROUP BY s.mod_crm_pst_id, s.mod_crm_pst_name, s.mod_crm_pst_color, s.mod_crm_pst_probability
    ORDER BY s.mod_crm_pst_order;
END//
DELIMITER ;

-- Procedure: Reporte de conversión
DELIMITER //
CREATE PROCEDURE `mod_crm_sp_report_conversion`(
    IN p_entity_id INT,
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    SELECT 
        DATE_FORMAT(mod_con_created_at, '%Y-%m') AS period,
        mod_con_source,
        COUNT(*) AS total_contacts,
        SUM(CASE WHEN mod_con_status = 'converted' THEN 1 ELSE 0 END) AS converted_count,
        ROUND(SUM(CASE WHEN mod_con_status = 'converted' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS conversion_rate,
        SUM(CASE WHEN mod_con_status = 'lost' THEN 1 ELSE 0 END) AS lost_count,
        ROUND(AVG(CASE 
            WHEN mod_con_conversion_date IS NOT NULL 
            THEN DATEDIFF(mod_con_conversion_date, mod_con_created_at) 
            ELSE NULL 
        END), 1) AS avg_days_to_convert
    FROM mod_contacts
    WHERE mod_con_ent_id = p_entity_id
    AND DATE(mod_con_created_at) BETWEEN p_start_date AND p_end_date
    AND mod_con_state = 0
    GROUP BY DATE_FORMAT(mod_con_created_at, '%Y-%m'), mod_con_source
    ORDER BY period DESC, mod_con_source;
END//
DELIMITER ;

-- ============================================
-- TRIGGERS Y EVENTOS
-- ============================================

DELIMITER //
CREATE TRIGGER `mod_crm_trg_update_last_contact_date`
AFTER INSERT ON `mod_crm_interactions`
FOR EACH ROW
BEGIN
    UPDATE mod_contacts 
    SET mod_con_last_contact_date = NEW.mod_crm_int_created_at
    WHERE mod_con_id = NEW.mod_crm_int_con_id;
END//
DELIMITER ;

SET GLOBAL event_scheduler = ON;

-- Evento: Actualizar actividades vencidas
DELIMITER //
CREATE EVENT IF NOT EXISTS `mod_crm_evt_update_overdue_activities`
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    UPDATE mod_crm_activities
    SET mod_crm_act_status = 'overdue'
    WHERE mod_crm_act_due_date < NOW()
    AND mod_crm_act_status = 'pending';
END//
DELIMITER ;