-- ============================================================================
-- MIGRACION 005: Triggers de sincronizacion bidireccional
-- Mantiene "1234567890" y numeros_whatsapp_business sincronizadas
--
-- Fecha: 2026-01-25
-- Autor: Claude Code (PostgreSQL Backend Agent)
-- ============================================================================

-- ============================================================================
-- FUNCION 1: Sincronizar "1234567890" → numeros_whatsapp_business
-- ============================================================================

CREATE OR REPLACE FUNCTION sync_whatsapp_legacy_to_nueva()
RETURNS TRIGGER AS $$
BEGIN
    -- Prevenir loops infinitos usando variable de sesion
    IF current_setting('app.whatsapp_sync_in_progress', true) = 'true' THEN
        RETURN NULL;
    END IF;

    -- Marcar que estamos en proceso de sincronizacion
    PERFORM set_config('app.whatsapp_sync_in_progress', 'true', true);

    IF TG_OP = 'INSERT' THEN
        INSERT INTO numeros_whatsapp_business (
            id, phone_number, business_id_display, sucursal_id_legacy,
            branch_priority, business_id, sucursal, soft_delete,
            whatsapp_contact_url, meta_access_token_encrypted,
            meta_phone_number_id, meta_waba_id, status, pin_2fa_encrypted,
            display_name, display_name_status, code_verification_status,
            webhook_subscribed, registered_at, signup_started_at,
            session_info_received_at, registration_attempts,
            last_registration_attempt_at, last_error_code, last_error_message,
            last_error_at, webhook_subscribed_at, last_webhook_event,
            last_webhook_at, api_call_count, last_api_success_at
        ) VALUES (
            NEW."4", NEW."1", NEW."2", NEW."3",
            NEW."5", NEW.business_id, NEW.sucursal, NEW.soft_delete,
            NEW.label_4, NEW.label_6, NEW.label_7, NEW.label_8,
            NEW.status, NEW.pin_2fa_encrypted, NEW.display_name,
            NEW.display_name_status, NEW.code_verification_status,
            NEW.webhook_subscribed, NEW.registered_at, NEW.signup_started_at,
            NEW.session_info_received_at, NEW.registration_attempts,
            NEW.last_registration_attempt_at, NEW.last_error_code,
            NEW.last_error_message, NEW.last_error_at, NEW.webhook_subscribed_at,
            NEW.last_webhook_event, NEW.last_webhook_at, NEW.api_call_count,
            NEW.last_api_success_at
        )
        ON CONFLICT (id) DO UPDATE SET
            phone_number = EXCLUDED.phone_number,
            business_id_display = EXCLUDED.business_id_display,
            sucursal_id_legacy = EXCLUDED.sucursal_id_legacy,
            branch_priority = EXCLUDED.branch_priority,
            business_id = EXCLUDED.business_id,
            sucursal = EXCLUDED.sucursal,
            soft_delete = EXCLUDED.soft_delete,
            whatsapp_contact_url = EXCLUDED.whatsapp_contact_url,
            meta_access_token_encrypted = EXCLUDED.meta_access_token_encrypted,
            meta_phone_number_id = EXCLUDED.meta_phone_number_id,
            meta_waba_id = EXCLUDED.meta_waba_id,
            status = EXCLUDED.status,
            pin_2fa_encrypted = EXCLUDED.pin_2fa_encrypted,
            display_name = EXCLUDED.display_name,
            display_name_status = EXCLUDED.display_name_status,
            code_verification_status = EXCLUDED.code_verification_status,
            webhook_subscribed = EXCLUDED.webhook_subscribed,
            registered_at = EXCLUDED.registered_at,
            signup_started_at = EXCLUDED.signup_started_at,
            session_info_received_at = EXCLUDED.session_info_received_at,
            registration_attempts = EXCLUDED.registration_attempts,
            last_registration_attempt_at = EXCLUDED.last_registration_attempt_at,
            last_error_code = EXCLUDED.last_error_code,
            last_error_message = EXCLUDED.last_error_message,
            last_error_at = EXCLUDED.last_error_at,
            webhook_subscribed_at = EXCLUDED.webhook_subscribed_at,
            last_webhook_event = EXCLUDED.last_webhook_event,
            last_webhook_at = EXCLUDED.last_webhook_at,
            api_call_count = EXCLUDED.api_call_count,
            last_api_success_at = EXCLUDED.last_api_success_at;

        PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE numeros_whatsapp_business SET
            phone_number = NEW."1",
            business_id_display = NEW."2",
            sucursal_id_legacy = NEW."3",
            branch_priority = NEW."5",
            business_id = NEW.business_id,
            sucursal = NEW.sucursal,
            soft_delete = NEW.soft_delete,
            whatsapp_contact_url = NEW.label_4,
            meta_access_token_encrypted = NEW.label_6,
            meta_phone_number_id = NEW.label_7,
            meta_waba_id = NEW.label_8,
            status = NEW.status,
            pin_2fa_encrypted = NEW.pin_2fa_encrypted,
            display_name = NEW.display_name,
            display_name_status = NEW.display_name_status,
            code_verification_status = NEW.code_verification_status,
            webhook_subscribed = NEW.webhook_subscribed,
            registered_at = NEW.registered_at,
            signup_started_at = NEW.signup_started_at,
            session_info_received_at = NEW.session_info_received_at,
            registration_attempts = NEW.registration_attempts,
            last_registration_attempt_at = NEW.last_registration_attempt_at,
            last_error_code = NEW.last_error_code,
            last_error_message = NEW.last_error_message,
            last_error_at = NEW.last_error_at,
            webhook_subscribed_at = NEW.webhook_subscribed_at,
            last_webhook_event = NEW.last_webhook_event,
            last_webhook_at = NEW.last_webhook_at,
            api_call_count = NEW.api_call_count,
            last_api_success_at = NEW.last_api_success_at
        WHERE id = NEW."4";

        PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM numeros_whatsapp_business WHERE id = OLD."4";
        PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
        RETURN OLD;
    END IF;

    PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- FUNCION 2: Sincronizar numeros_whatsapp_business → "1234567890"
-- ============================================================================

CREATE OR REPLACE FUNCTION sync_whatsapp_nueva_to_legacy()
RETURNS TRIGGER AS $$
BEGIN
    -- Prevenir loops infinitos
    IF current_setting('app.whatsapp_sync_in_progress', true) = 'true' THEN
        RETURN NULL;
    END IF;

    PERFORM set_config('app.whatsapp_sync_in_progress', 'true', true);

    IF TG_OP = 'INSERT' THEN
        INSERT INTO "1234567890" (
            "4", "1", "2", "3", "5", business_id, sucursal, soft_delete,
            label_4, label_6, label_7, label_8, status, pin_2fa_encrypted,
            display_name, display_name_status, code_verification_status,
            webhook_subscribed, registered_at, signup_started_at,
            session_info_received_at, registration_attempts,
            last_registration_attempt_at, last_error_code, last_error_message,
            last_error_at, webhook_subscribed_at, last_webhook_event,
            last_webhook_at, api_call_count, last_api_success_at,
            label_1, label_2, label_3, label_5
        ) VALUES (
            NEW.id, NEW.phone_number, NEW.business_id_display,
            NEW.sucursal_id_legacy, NEW.branch_priority, NEW.business_id,
            NEW.sucursal, NEW.soft_delete, NEW.whatsapp_contact_url,
            NEW.meta_access_token_encrypted, NEW.meta_phone_number_id,
            NEW.meta_waba_id, NEW.status, NEW.pin_2fa_encrypted,
            NEW.display_name, NEW.display_name_status,
            NEW.code_verification_status, NEW.webhook_subscribed,
            NEW.registered_at, NEW.signup_started_at,
            NEW.session_info_received_at, NEW.registration_attempts,
            NEW.last_registration_attempt_at, NEW.last_error_code,
            NEW.last_error_message, NEW.last_error_at,
            NEW.webhook_subscribed_at, NEW.last_webhook_event,
            NEW.last_webhook_at, NEW.api_call_count, NEW.last_api_success_at,
            'WhatsApp', 'Business', 'Sucursal ID', 'BotPressID'
        )
        ON CONFLICT ("4") DO UPDATE SET
            "1" = EXCLUDED."1",
            "2" = EXCLUDED."2",
            "3" = EXCLUDED."3",
            "5" = EXCLUDED."5",
            business_id = EXCLUDED.business_id,
            sucursal = EXCLUDED.sucursal,
            soft_delete = EXCLUDED.soft_delete,
            label_4 = EXCLUDED.label_4,
            label_6 = EXCLUDED.label_6,
            label_7 = EXCLUDED.label_7,
            label_8 = EXCLUDED.label_8,
            status = EXCLUDED.status,
            pin_2fa_encrypted = EXCLUDED.pin_2fa_encrypted,
            display_name = EXCLUDED.display_name,
            display_name_status = EXCLUDED.display_name_status,
            code_verification_status = EXCLUDED.code_verification_status,
            webhook_subscribed = EXCLUDED.webhook_subscribed,
            registered_at = EXCLUDED.registered_at,
            signup_started_at = EXCLUDED.signup_started_at,
            session_info_received_at = EXCLUDED.session_info_received_at,
            registration_attempts = EXCLUDED.registration_attempts,
            last_registration_attempt_at = EXCLUDED.last_registration_attempt_at,
            last_error_code = EXCLUDED.last_error_code,
            last_error_message = EXCLUDED.last_error_message,
            last_error_at = EXCLUDED.last_error_at,
            webhook_subscribed_at = EXCLUDED.webhook_subscribed_at,
            last_webhook_event = EXCLUDED.last_webhook_event,
            last_webhook_at = EXCLUDED.last_webhook_at,
            api_call_count = EXCLUDED.api_call_count,
            last_api_success_at = EXCLUDED.last_api_success_at;

        PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE "1234567890" SET
            "1" = NEW.phone_number,
            "2" = NEW.business_id_display,
            "3" = NEW.sucursal_id_legacy,
            "5" = NEW.branch_priority,
            business_id = NEW.business_id,
            sucursal = NEW.sucursal,
            soft_delete = NEW.soft_delete,
            label_4 = NEW.whatsapp_contact_url,
            label_6 = NEW.meta_access_token_encrypted,
            label_7 = NEW.meta_phone_number_id,
            label_8 = NEW.meta_waba_id,
            status = NEW.status,
            pin_2fa_encrypted = NEW.pin_2fa_encrypted,
            display_name = NEW.display_name,
            display_name_status = NEW.display_name_status,
            code_verification_status = NEW.code_verification_status,
            webhook_subscribed = NEW.webhook_subscribed,
            registered_at = NEW.registered_at,
            signup_started_at = NEW.signup_started_at,
            session_info_received_at = NEW.session_info_received_at,
            registration_attempts = NEW.registration_attempts,
            last_registration_attempt_at = NEW.last_registration_attempt_at,
            last_error_code = NEW.last_error_code,
            last_error_message = NEW.last_error_message,
            last_error_at = NEW.last_error_at,
            webhook_subscribed_at = NEW.webhook_subscribed_at,
            last_webhook_event = NEW.last_webhook_event,
            last_webhook_at = NEW.last_webhook_at,
            api_call_count = NEW.api_call_count,
            last_api_success_at = NEW.last_api_success_at
        WHERE "4" = NEW.id;

        PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM "1234567890" WHERE "4" = OLD.id;
        PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
        RETURN OLD;
    END IF;

    PERFORM set_config('app.whatsapp_sync_in_progress', 'false', true);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- TRIGGERS: Activar sincronizacion bidireccional
-- ============================================================================

-- Trigger en tabla legacy → nueva
DROP TRIGGER IF EXISTS sync_to_numeros_whatsapp_business ON "1234567890";
CREATE TRIGGER sync_to_numeros_whatsapp_business
    AFTER INSERT OR UPDATE OR DELETE ON "1234567890"
    FOR EACH ROW
    EXECUTE FUNCTION sync_whatsapp_legacy_to_nueva();

-- Trigger en tabla nueva → legacy
DROP TRIGGER IF EXISTS sync_to_legacy_1234567890 ON numeros_whatsapp_business;
CREATE TRIGGER sync_to_legacy_1234567890
    AFTER INSERT OR UPDATE OR DELETE ON numeros_whatsapp_business
    FOR EACH ROW
    EXECUTE FUNCTION sync_whatsapp_nueva_to_legacy();

-- ============================================================================
-- DOCUMENTACION
-- ============================================================================

COMMENT ON FUNCTION sync_whatsapp_legacy_to_nueva() IS
    'Sincroniza cambios de tabla legacy "1234567890" a numeros_whatsapp_business';

COMMENT ON FUNCTION sync_whatsapp_nueva_to_legacy() IS
    'Sincroniza cambios de numeros_whatsapp_business a tabla legacy "1234567890"';
