-- Migration 021: allow users.tenant_id to be NULL for platform-level users (sellers)
-- Sellers do not belong to a tenant; they log in to /seller/* routes only.
-- Safe version: detects FK name from information_schema before dropping it.

DROP PROCEDURE IF EXISTS migrate_021;
DELIMITER //
CREATE PROCEDURE migrate_021()
BEGIN
    DECLARE fk_name VARCHAR(255) DEFAULT NULL;

    -- Solo actuar si la columna aún es NOT NULL
    IF (
        SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME   = 'users'
          AND COLUMN_NAME  = 'tenant_id'
    ) = 'NO' THEN

        -- Obtener nombre real de la FK
        SELECT CONSTRAINT_NAME INTO fk_name
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA    = DATABASE()
          AND TABLE_NAME      = 'users'
          AND COLUMN_NAME     = 'tenant_id'
          AND REFERENCED_TABLE_NAME IS NOT NULL
        LIMIT 1;

        -- Eliminar la FK existente (si hay)
        IF fk_name IS NOT NULL THEN
            SET @drop_fk = CONCAT('ALTER TABLE users DROP FOREIGN KEY `', fk_name, '`');
            PREPARE stmt FROM @drop_fk;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        -- Hacer la columna nullable
        ALTER TABLE users
            MODIFY COLUMN tenant_id BIGINT UNSIGNED NULL DEFAULT NULL;

        -- Re-añadir FK (permite NULL → ON DELETE CASCADE solo aplica para valores no-NULL)
        ALTER TABLE users
            ADD CONSTRAINT fk_users_tenant
            FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;

    END IF;
END //
DELIMITER ;
CALL migrate_021();
DROP PROCEDURE IF EXISTS migrate_021;