-- ============================================================
-- MIGRACIÓN SEGURA COMPLETA — compatible MySQL 5.7+
-- En phpMyAdmin: Import → marcá "Force all queries, do not
-- abort on errors" antes de ejecutar.
-- Los errores "Duplicate column name" son normales si la
-- columna ya existe — el script sigue igual.
-- ============================================================

DROP PROCEDURE IF EXISTS migrate_vale;

DELIMITER //
CREATE PROCEDURE migrate_vale()
BEGIN
    -- Ignora error 1060 (Duplicate column) y 1061 (Duplicate key)
    DECLARE CONTINUE HANDLER FOR 1060 BEGIN END;
    DECLARE CONTINUE HANDLER FOR 1061 BEGIN END;
    DECLARE CONTINUE HANDLER FOR 1091 BEGIN END;

    -- ── 002: web_public_key + web_visitor_id ────────────────
    ALTER TABLE tenants
        ADD COLUMN web_public_key CHAR(32) NULL AFTER whatsapp_number;

    ALTER TABLE contacts
        ADD COLUMN web_visitor_id VARCHAR(64) NULL AFTER phone;

    -- ── 004: Google OAuth ────────────────────────────────────
    ALTER TABLE users
        MODIFY COLUMN password_hash VARCHAR(255) NULL;

    ALTER TABLE users
        ADD COLUMN google_sub VARCHAR(64) NULL AFTER email;

    ALTER TABLE users
        ADD COLUMN avatar_url VARCHAR(512) NULL AFTER name;

    ALTER TABLE tenants
        ADD COLUMN google_email VARCHAR(160) NULL;

    ALTER TABLE tenants
        ADD COLUMN google_refresh_token TEXT NULL;

    ALTER TABLE tenants
        ADD COLUMN google_connected_at TIMESTAMP NULL;

    -- ── 005: Onboarding ──────────────────────────────────────
    ALTER TABLE tenants
        ADD COLUMN business_type VARCHAR(80) NULL;

    ALTER TABLE tenants
        ADD COLUMN profile_kind ENUM('cita','reserva','pedido','lead','generic') NOT NULL DEFAULT 'generic';

    ALTER TABLE tenants
        ADD COLUMN onboarding_status ENUM('chatting','configuring','live') NOT NULL DEFAULT 'chatting';

    ALTER TABLE vale_config
        ADD COLUMN persona TEXT NULL;

    -- ── 006: Perfil público + auth_provider ─────────────────
    ALTER TABLE tenants
        ADD COLUMN public_slug VARCHAR(80) NULL;

    ALTER TABLE users
        ADD COLUMN auth_provider ENUM('google','password') NULL AFTER role;

    -- ── 007: Teléfono del dueño ──────────────────────────────
    ALTER TABLE users
        ADD COLUMN phone VARCHAR(32) NULL AFTER email;

    -- ── 008: País y moneda ───────────────────────────────────
    ALTER TABLE tenants
        ADD COLUMN country VARCHAR(2) NULL AFTER locale;

    ALTER TABLE tenants
        ADD COLUMN currency VARCHAR(8) NULL AFTER country;

    -- ── 009: Platform settings ───────────────────────────────
    CREATE TABLE IF NOT EXISTS platform_settings (
        id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        key_name VARCHAR(80) NOT NULL,
        value    TEXT NULL,
        UNIQUE KEY uq_platform_settings_key (key_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    -- ── 010: Google Calendar ─────────────────────────────────
    ALTER TABLE tenants
        ADD COLUMN google_calendar_connected TINYINT(1) NOT NULL DEFAULT 0;

    -- ── 011: Canal preferido ─────────────────────────────────
    ALTER TABLE tenants
        ADD COLUMN preferred_channel VARCHAR(16) NOT NULL DEFAULT 'web';

    -- ── 022: Campos de contacto del negocio ─────────────────
    ALTER TABLE tenants
        ADD COLUMN telefono VARCHAR(32) NULL;

    ALTER TABLE tenants
        ADD COLUMN direccion VARCHAR(255) NULL;

    ALTER TABLE tenants
        ADD COLUMN descripcion TEXT NULL;

    -- ── Backfills ────────────────────────────────────────────
    UPDATE tenants
    SET web_public_key = LEFT(SHA2(CONCAT(id, UUID(), RAND()), 256), 32)
    WHERE web_public_key IS NULL;

    UPDATE tenants
    SET public_slug = CONCAT('negocio-', id)
    WHERE public_slug IS NULL;

    UPDATE users
    SET auth_provider = 'google'
    WHERE google_sub IS NOT NULL AND auth_provider IS NULL;

    UPDATE users
    SET auth_provider = 'password'
    WHERE auth_provider IS NULL;

    -- ── 024: meta_message_id en messages (deduplicación de webhooks) ──
    ALTER TABLE messages
        ADD COLUMN meta_message_id VARCHAR(64) NULL DEFAULT NULL
            COMMENT 'ID del mensaje Meta Cloud API — deduplicación de webhooks';

    ALTER TABLE messages
        ADD UNIQUE KEY idx_meta_message_id (meta_message_id);

    -- ── 025: branding por tenant (logo, portada, colores de marca) ──
    ALTER TABLE tenants ADD COLUMN logo_url VARCHAR(512) NULL;
    ALTER TABLE tenants ADD COLUMN banner_url VARCHAR(512) NULL;
    ALTER TABLE tenants ADD COLUMN color_primario CHAR(7) NULL;
    ALTER TABLE tenants ADD COLUMN color_acento CHAR(7) NULL;
    ALTER TABLE tenants ADD COLUMN color_texto_cta CHAR(7) NULL;

END//
DELIMITER ;

CALL migrate_vale();
DROP PROCEDURE IF EXISTS migrate_vale;

-- ── Verificación: columnas de tenants ───────────────────────
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'tenants'
ORDER BY ORDINAL_POSITION;