-- ============================================================================
-- MIGRACION 004: Crear tabla numeros_whatsapp_business
-- Copia de "1234567890" con columnas renombradas a nombres descriptivos
--
-- Fecha: 2026-01-25
-- Autor: Claude Code (PostgreSQL Backend Agent)
--
-- IMPORTANTE: Esta migracion NO elimina la tabla original
-- La tabla "1234567890" permanecera con triggers de sincronizacion
-- ============================================================================

-- 1. Crear tabla con nueva estructura
CREATE TABLE IF NOT EXISTS numeros_whatsapp_business (
    -- Columnas principales (renombradas)
    id SERIAL PRIMARY KEY,                              -- Era "4"
    phone_number BIGINT,                                -- Era "1"
    business_id_display VARCHAR(100),                   -- Era "2"
    sucursal_id_legacy INTEGER,                         -- Era "3"
    branch_priority INTEGER,                            -- Era "5"

    -- Columnas de multi-tenancy (sin cambio)
    business_id VARCHAR(100) DEFAULT 'ApexCare',
    sucursal INT4[] DEFAULT '{1}'::integer[],
    soft_delete BOOLEAN,

    -- Labels renombrados (solo los que tienen datos reales)
    whatsapp_contact_url VARCHAR(100),                  -- Era label_4
    meta_access_token_encrypted TEXT,                   -- Era label_6
    meta_phone_number_id BIGINT,                        -- Era label_7
    meta_waba_id BIGINT,                                -- Era label_8

    -- Columnas de estado (sin cambio de nombre)
    status VARCHAR(50) DEFAULT 'pending_registration',
    pin_2fa_encrypted TEXT,
    display_name VARCHAR(100),
    display_name_status VARCHAR(50),
    code_verification_status VARCHAR(50),
    webhook_subscribed BOOLEAN DEFAULT FALSE,
    registered_at TIMESTAMP,

    -- Columnas de tracking Embedded Signup (sin cambio de nombre)
    signup_started_at TIMESTAMP,
    session_info_received_at TIMESTAMP,
    registration_attempts INTEGER DEFAULT 0,
    last_registration_attempt_at TIMESTAMP,
    last_error_code INTEGER,
    last_error_message VARCHAR(500),
    last_error_at TIMESTAMP,
    webhook_subscribed_at TIMESTAMP,
    last_webhook_event VARCHAR(100),
    last_webhook_at TIMESTAMP,
    api_call_count INTEGER DEFAULT 0,
    last_api_success_at TIMESTAMP
);

-- 2. Copiar datos de tabla legacy con mapeo de columnas
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
)
SELECT
    "4",                    -- id
    "1",                    -- phone_number
    "2",                    -- business_id_display
    "3",                    -- sucursal_id_legacy
    "5",                    -- branch_priority
    business_id,
    sucursal,
    soft_delete,
    label_4,                -- whatsapp_contact_url
    label_6,                -- meta_access_token_encrypted
    label_7,                -- meta_phone_number_id
    label_8,                -- 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
FROM "1234567890"
ON CONFLICT (id) DO NOTHING;

-- 3. Sincronizar secuencia del ID para que nuevos registros continuen
SELECT setval(
    pg_get_serial_sequence('numeros_whatsapp_business', 'id'),
    COALESCE((SELECT MAX(id) FROM numeros_whatsapp_business), 0) + 1,
    false
);

-- 4. Crear indices equivalentes a los de la tabla legacy
CREATE INDEX IF NOT EXISTS idx_nwb_business_id
    ON numeros_whatsapp_business(business_id);

CREATE INDEX IF NOT EXISTS idx_nwb_phone_number
    ON numeros_whatsapp_business(phone_number);

CREATE INDEX IF NOT EXISTS idx_nwb_status
    ON numeros_whatsapp_business(status);

CREATE INDEX IF NOT EXISTS idx_nwb_meta_phone_number_id
    ON numeros_whatsapp_business(meta_phone_number_id);

CREATE INDEX IF NOT EXISTS idx_nwb_business_status
    ON numeros_whatsapp_business(business_id, status);

CREATE INDEX IF NOT EXISTS idx_nwb_business_id_display
    ON numeros_whatsapp_business(business_id_display);

-- 5. Documentacion inline en PostgreSQL
COMMENT ON TABLE numeros_whatsapp_business IS
    'Registro de numeros WhatsApp Business conectados via Embedded Signup. Reemplazo de tabla "1234567890".';

COMMENT ON COLUMN numeros_whatsapp_business.phone_number IS
    'Numero telefonico de 10 digitos sin separadores (formato E.164 sin codigo pais)';

COMMENT ON COLUMN numeros_whatsapp_business.business_id_display IS
    'Identificador del negocio mostrado en WhatsApp. Puede coincidir o no con business_id de multi-tenancy';

COMMENT ON COLUMN numeros_whatsapp_business.meta_access_token_encrypted IS
    'Access token de Meta WhatsApp Cloud API encriptado con AES-256-CBC';

COMMENT ON COLUMN numeros_whatsapp_business.meta_phone_number_id IS
    'phone_number_id de Meta WhatsApp Cloud API (ID publico, no requiere encriptacion)';

COMMENT ON COLUMN numeros_whatsapp_business.meta_waba_id IS
    'waba_id (WhatsApp Business Account ID) de Meta (ID publico)';

COMMENT ON COLUMN numeros_whatsapp_business.status IS
    'Estado del registro: pending_registration, pending_name_approval, connected, name_rejected, disconnected';
