-- Fix customers table address column
-- Check if address exists and address_1 doesn't
SET @customers_has_address = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'customers' 
    AND COLUMN_NAME = 'address'
);

SET @customers_has_address_1 = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'customers' 
    AND COLUMN_NAME = 'address_1'
);

-- If address exists and address_1 doesn't, create address_1 and copy data
SET @sql_customers = IF(@customers_has_address > 0 AND @customers_has_address_1 = 0,
    'ALTER TABLE customers ADD COLUMN address_1 TEXT NULL AFTER city_id;
     UPDATE customers SET address_1 = address;
     ALTER TABLE customers DROP COLUMN address;',
    IF(@customers_has_address > 0 AND @customers_has_address_1 > 0,
        'UPDATE customers SET address_1 = COALESCE(address_1, address) WHERE address_1 IS NULL;
         ALTER TABLE customers DROP COLUMN address;',
        'SELECT "Customers table already fixed" AS Status;'
    )
);

-- Execute customers fix
PREPARE stmt FROM @sql_customers;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix suppliers table address column
SET @suppliers_has_address = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'suppliers' 
    AND COLUMN_NAME = 'address'
);

SET @suppliers_has_address_1 = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'suppliers' 
    AND COLUMN_NAME = 'address_1'
);

-- If address exists and address_1 doesn't, create address_1 and copy data
SET @sql_suppliers = IF(@suppliers_has_address > 0 AND @suppliers_has_address_1 = 0,
    'ALTER TABLE suppliers ADD COLUMN address_1 TEXT NULL AFTER city_id;
     UPDATE suppliers SET address_1 = address;
     ALTER TABLE suppliers DROP COLUMN address;',
    IF(@suppliers_has_address > 0 AND @suppliers_has_address_1 > 0,
        'UPDATE suppliers SET address_1 = COALESCE(address_1, address) WHERE address_1 IS NULL;
         ALTER TABLE suppliers DROP COLUMN address;',
        'SELECT "Suppliers table already fixed" AS Status;'
    )
);

-- Execute suppliers fix
PREPARE stmt FROM @sql_suppliers;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'Address columns fixed successfully!' AS Result;