-- Create spatial_unit records from property IDs found in transactions
-- This populates lr_spatial_unit with property numbers from lr_transaction

SET FOREIGN_KEY_CHECKS=0;

-- Get the next available ID (auto-increment or max+1)
SET @next_id = COALESCE((SELECT MAX(id) FROM lr_spatial_unit), 0) + 1;

-- Insert spatial units from distinct property IDs in transactions
INSERT IGNORE INTO lr_spatial_unit (
    id, Hjtype, refnum, spatial_unit_type, status, tenure_type, purpose, zone, layout, 
    area_unit_type, areapl_unit_type, areabld_unit_type, rec_dict1, rec_dict2, version, begin_lifespan_version
)
SELECT DISTINCT 
    (@next_id := @next_id + 1) as id,
    'com.landsystems.lrs.model.SpatialUnit' as Hjtype,
    COALESCE(
        NULLIF(TRIM(source_property_ids), ''),
        NULLIF(TRIM(target_property_ids), ''),
        NULLIF(TRIM(old_property_id), '')
    ) as refnum,
    1 as spatial_unit_type,  -- Default property type
    1 as status,  -- Default status
    1 as tenure_type,  -- Default tenure
    1 as purpose,  -- Default purpose
    1 as zone,  -- Default zone
    1 as layout,  -- Default layout
    1 as area_unit_type,  -- Default area unit
    1 as areapl_unit_type,  -- Default planned area unit
    1 as areabld_unit_type,  -- Default building area unit
    1 as rec_dict1,  -- Default rec_dict1
    1 as rec_dict2,  -- Default rec_dict2
    '1' as version,
    COALESCE(t.create_date, NOW()) as begin_lifespan_version
FROM lr_transaction t
WHERE (
    (t.source_property_ids IS NOT NULL AND t.source_property_ids != '' AND TRIM(t.source_property_ids) != '')
    OR (t.target_property_ids IS NOT NULL AND t.target_property_ids != '' AND TRIM(t.target_property_ids) != '')
    OR (t.old_property_id IS NOT NULL AND t.old_property_id != '' AND TRIM(t.old_property_id) != '')
)
AND COALESCE(
    NULLIF(TRIM(t.source_property_ids), ''),
    NULLIF(TRIM(t.target_property_ids), ''),
    NULLIF(TRIM(t.old_property_id), '')
) IS NOT NULL
AND COALESCE(
    NULLIF(TRIM(t.source_property_ids), ''),
    NULLIF(TRIM(t.target_property_ids), ''),
    NULLIF(TRIM(t.old_property_id), '')
) NOT IN (SELECT refnum FROM lr_spatial_unit WHERE refnum IS NOT NULL);

SET FOREIGN_KEY_CHECKS=1;