service-finder/backend/app/scripts/fix_orgs_sql_final.sql

-- Complete database surgery for tester_pro organizations and vehicles
-- Run this in the shared-postgres container

-- 1. Create branches for Alpha and Beta organizations if they don't exist
DO $$
DECLARE
    person_id integer := 29;
    alpha_org_id integer;
    beta_org_id integer;
    alpha_branch_id uuid;
    beta_branch_id uuid;
    vehicle1_id uuid;
    vehicle2_id uuid;
    vehicle3_id uuid;
BEGIN
    RAISE NOTICE '=== DATABASE SURGERY STARTED ===';
    
    -- Get Alpha organization ID
    SELECT id INTO alpha_org_id 
    FROM fleet.organizations 
    WHERE owner_id = person_id AND full_name = 'Test Kft. Alpha';
    
    -- Get Beta organization ID
    SELECT id INTO beta_org_id 
    FROM fleet.organizations 
    WHERE owner_id = person_id AND full_name = 'Test Kft. Beta';
    
    RAISE NOTICE 'Alpha Organization ID: %', alpha_org_id;
    RAISE NOTICE 'Beta Organization ID: %', beta_org_id;
    
    -- Create branch for Alpha if it doesn't exist
    IF alpha_org_id IS NOT NULL THEN
        IF NOT EXISTS (SELECT 1 FROM fleet.branches WHERE organization_id = alpha_org_id) THEN
            alpha_branch_id := gen_random_uuid();
            INSERT INTO fleet.branches (
                id, name, organization_id, branch_rating, opening_hours, 
                status, is_deleted, created_at
            ) VALUES (
                alpha_branch_id, 'Test Kft. Alpha - Main Branch', alpha_org_id, 
                0.0, '{}'::jsonb, 'active', false, NOW()
            );
            RAISE NOTICE 'Created Alpha Branch: %', alpha_branch_id;
        ELSE
            RAISE NOTICE 'Alpha Branch already exists';
        END IF;
    END IF;
    
    -- Create branch for Beta if it doesn't exist
    IF beta_org_id IS NOT NULL THEN
        IF NOT EXISTS (SELECT 1 FROM fleet.branches WHERE organization_id = beta_org_id) THEN
            beta_branch_id := gen_random_uuid();
            INSERT INTO fleet.branches (
                id, name, organization_id, branch_rating, opening_hours, 
                status, is_deleted, created_at
            ) VALUES (
                beta_branch_id, 'Test Kft. Beta - Main Branch', beta_org_id, 
                0.0, '{}'::jsonb, 'active', false, NOW()
            );
            RAISE NOTICE 'Created Beta Branch: %', beta_branch_id;
        ELSE
            RAISE NOTICE 'Beta Branch already exists';
        END IF;
    END IF;
    
    -- 2. Distribute vehicles
    RAISE NOTICE 'Distributing vehicles...';
    
    -- Get 3 vehicles owned by tester_pro
    SELECT id INTO vehicle1_id FROM vehicle.assets 
    WHERE owner_person_id = person_id 
    ORDER BY license_plate 
    LIMIT 1 OFFSET 0;
    
    SELECT id INTO vehicle2_id FROM vehicle.assets 
    WHERE owner_person_id = person_id 
    ORDER BY license_plate 
    LIMIT 1 OFFSET 1;
    
    SELECT id INTO vehicle3_id FROM vehicle.assets 
    WHERE owner_person_id = person_id 
    ORDER BY license_plate 
    LIMIT 1 OFFSET 2;
    
    RAISE NOTICE 'Vehicle 1 ID: %', vehicle1_id;
    RAISE NOTICE 'Vehicle 2 ID: %', vehicle2_id;
    RAISE NOTICE 'Vehicle 3 ID: %', vehicle3_id;
    
    -- Vehicle 1: Keep in Private Organization (ID 21)
    UPDATE vehicle.assets 
    SET current_organization_id = 21
    WHERE id = vehicle1_id;
    RAISE NOTICE 'Vehicle 1 -> Private Organization (ID 21)';
    
    -- Vehicle 2: Move to Alpha Organization
    IF alpha_org_id IS NOT NULL THEN
        UPDATE vehicle.assets 
        SET current_organization_id = alpha_org_id
        WHERE id = vehicle2_id;
        RAISE NOTICE 'Vehicle 2 -> Alpha Organization (ID %)', alpha_org_id;
    END IF;
    
    -- Vehicle 3: Move to Beta Organization
    IF beta_org_id IS NOT NULL THEN
        UPDATE vehicle.assets 
        SET current_organization_id = beta_org_id
        WHERE id = vehicle3_id;
        RAISE NOTICE 'Vehicle 3 -> Beta Organization (ID %)', beta_org_id;
    END IF;
    
    -- 3. Update asset assignments
    RAISE NOTICE 'Updating asset assignments...';
    
    -- Delete existing assignments for these vehicles
    DELETE FROM fleet.asset_assignments 
    WHERE asset_id IN (vehicle1_id, vehicle2_id, vehicle3_id);
    
    -- Create new assignments with UUIDs and status
    IF vehicle1_id IS NOT NULL THEN
        INSERT INTO fleet.asset_assignments (id, asset_id, organization_id, status)
        VALUES (gen_random_uuid(), vehicle1_id, 21, 'active');
        RAISE NOTICE 'Created assignment for Vehicle 1';
    END IF;
    
    IF vehicle2_id IS NOT NULL AND alpha_org_id IS NOT NULL THEN
        INSERT INTO fleet.asset_assignments (id, asset_id, organization_id, status)
        VALUES (gen_random_uuid(), vehicle2_id, alpha_org_id, 'active');
        RAISE NOTICE 'Created assignment for Vehicle 2';
    END IF;
    
    IF vehicle3_id IS NOT NULL AND beta_org_id IS NOT NULL THEN
        INSERT INTO fleet.asset_assignments (id, asset_id, organization_id, status)
        VALUES (gen_random_uuid(), vehicle3_id, beta_org_id, 'active');
        RAISE NOTICE 'Created assignment for Vehicle 3';
    END IF;
    
    RAISE NOTICE '=== DATABASE SURGERY COMPLETED SUCCESSFULLY ===';
END $$;