-- Add text-based location columns to lands table
-- This allows users to enter any country, region, district freely

-- Add country column if not exists
ALTER TABLE lands ADD COLUMN IF NOT EXISTS country VARCHAR(100) DEFAULT NULL AFTER location_address;

-- Add region as text column if not exists (separate from region_id)
ALTER TABLE lands ADD COLUMN IF NOT EXISTS region VARCHAR(100) DEFAULT NULL AFTER country;

-- Add district as text column if not exists (separate from district_id)
ALTER TABLE lands ADD COLUMN IF NOT EXISTS district VARCHAR(100) DEFAULT NULL AFTER region;

-- For MySQL versions that don't support IF NOT EXISTS in ALTER TABLE:
-- Run these if the above fails:

-- SET @sql = (SELECT IF(
--     (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
--      WHERE TABLE_NAME = 'lands' AND COLUMN_NAME = 'country' AND TABLE_SCHEMA = DATABASE()) > 0,
--     "SELECT 'Column country already exists'",
--     "ALTER TABLE lands ADD COLUMN country VARCHAR(100) DEFAULT NULL AFTER location_address"
-- ));
-- PREPARE stmt FROM @sql;
-- EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;
