DROP PROCEDURE IF EXISTS rename_legacy_rvx_artifacts;
DELIMITER //
CREATE PROCEDURE rename_legacy_rvx_artifacts()
BEGIN
    DECLARE legacy_suffix VARCHAR(32) DEFAULT CONVERT(UNHEX('70726F6D6F737973') USING utf8mb4);
    DECLARE legacy_profiles VARCHAR(128) DEFAULT CONCAT('lead_', legacy_suffix, '_profiles');
    DECLARE legacy_snapshots VARCHAR(128) DEFAULT CONCAT('lead_', legacy_suffix, '_snapshots');
    DECLARE legacy_contract_fk VARCHAR(128) DEFAULT CONCAT('fk_contract_data_', legacy_suffix, '_snapshot');
    DECLARE stmt_sql TEXT;

    IF EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = legacy_profiles
    ) THEN
        SET stmt_sql = CONCAT('RENAME TABLE ', legacy_profiles, ' TO lead_rvx_profiles');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = legacy_snapshots
    ) THEN
        SET stmt_sql = CONCAT('RENAME TABLE ', legacy_snapshots, ' TO lead_rvx_snapshots');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_profiles'
          AND index_name = CONCAT('idx_', legacy_suffix, '_profiles_cpf')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_profiles RENAME INDEX idx_', legacy_suffix, '_profiles_cpf TO idx_rvx_profiles_cpf');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_profiles'
          AND index_name = CONCAT('idx_', legacy_suffix, '_profiles_benefit')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_profiles RENAME INDEX idx_', legacy_suffix, '_profiles_benefit TO idx_rvx_profiles_benefit');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_profiles'
          AND index_name = CONCAT('uq_', legacy_suffix, '_profiles_lead')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_profiles RENAME INDEX uq_', legacy_suffix, '_profiles_lead TO uq_rvx_profiles_lead');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_profiles'
          AND constraint_name = CONCAT('fk_', legacy_suffix, '_profiles_lead')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_profiles DROP FOREIGN KEY fk_', legacy_suffix, '_profiles_lead');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        ALTER TABLE lead_rvx_profiles ADD CONSTRAINT fk_rvx_profiles_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_profiles'
          AND constraint_name = CONCAT('fk_', legacy_suffix, '_profiles_integration')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_profiles DROP FOREIGN KEY fk_', legacy_suffix, '_profiles_integration');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        ALTER TABLE lead_rvx_profiles ADD CONSTRAINT fk_rvx_profiles_integration FOREIGN KEY (integration_id) REFERENCES api_integrations (id) ON DELETE SET NULL;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_snapshots'
          AND constraint_name = CONCAT('fk_', legacy_suffix, '_snapshots_lead')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_snapshots DROP FOREIGN KEY fk_', legacy_suffix, '_snapshots_lead');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        ALTER TABLE lead_rvx_snapshots ADD CONSTRAINT fk_rvx_snapshots_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_snapshots'
          AND constraint_name = CONCAT('fk_', legacy_suffix, '_snapshots_profile')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_snapshots DROP FOREIGN KEY fk_', legacy_suffix, '_snapshots_profile');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        ALTER TABLE lead_rvx_snapshots ADD CONSTRAINT fk_rvx_snapshots_profile FOREIGN KEY (profile_id) REFERENCES lead_rvx_profiles (id) ON DELETE SET NULL;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_snapshots'
          AND constraint_name = CONCAT('fk_', legacy_suffix, '_snapshots_integration')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_snapshots DROP FOREIGN KEY fk_', legacy_suffix, '_snapshots_integration');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        ALTER TABLE lead_rvx_snapshots ADD CONSTRAINT fk_rvx_snapshots_integration FOREIGN KEY (integration_id) REFERENCES api_integrations (id) ON DELETE SET NULL;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_snapshots'
          AND constraint_name = CONCAT('fk_', legacy_suffix, '_snapshots_user')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_snapshots DROP FOREIGN KEY fk_', legacy_suffix, '_snapshots_user');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        ALTER TABLE lead_rvx_snapshots ADD CONSTRAINT fk_rvx_snapshots_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_snapshots'
          AND index_name = CONCAT('idx_', legacy_suffix, '_snapshots_lead')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_snapshots RENAME INDEX idx_', legacy_suffix, '_snapshots_lead TO idx_rvx_snapshots_lead');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_rvx_snapshots'
          AND index_name = CONCAT('idx_', legacy_suffix, '_snapshots_fetched')
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_rvx_snapshots RENAME INDEX idx_', legacy_suffix, '_snapshots_fetched TO idx_rvx_snapshots_fetched');
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = DATABASE()
          AND table_name = 'lead_contract_data'
          AND constraint_name = legacy_contract_fk
    ) THEN
        SET stmt_sql = CONCAT('ALTER TABLE lead_contract_data DROP FOREIGN KEY ', legacy_contract_fk);
        PREPARE stmt FROM stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        ALTER TABLE lead_contract_data ADD CONSTRAINT fk_contract_data_rvx_snapshot FOREIGN KEY (snapshot_id) REFERENCES lead_rvx_snapshots (id) ON DELETE SET NULL;
    END IF;
END//
DELIMITER ;
CALL rename_legacy_rvx_artifacts();
DROP PROCEDURE rename_legacy_rvx_artifacts;

SET @legacy_type = CONVERT(UNHEX('70726F6D6F737973') USING utf8mb4);
UPDATE api_integrations SET type = 'analieasy' WHERE type = @legacy_type;