-- ICYLAB OS - P4.2 Customer Geolocation Support
-- No agrega columnas nuevas: usa columnas existentes de crm_customers
-- address, district, city, region, country, latitude, longitude, geocode_status,
-- geocode_source, geocode_query, geocoded_at.

UPDATE crm_customers
SET geocode_status = 'pending'
WHERE geocode_status IS NULL OR geocode_status = '';

DELIMITER //
CREATE PROCEDURE icylab_add_customer_geo_indexes_if_needed()
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'crm_customers'
          AND INDEX_NAME = 'idx_crm_customers_geo'
    ) THEN
        ALTER TABLE crm_customers ADD KEY idx_crm_customers_geo (latitude, longitude);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'crm_customers'
          AND INDEX_NAME = 'idx_crm_customers_location_text'
    ) THEN
        ALTER TABLE crm_customers ADD KEY idx_crm_customers_location_text (district, city, region);
    END IF;
END//
DELIMITER ;

CALL icylab_add_customer_geo_indexes_if_needed();
DROP PROCEDURE icylab_add_customer_geo_indexes_if_needed;
