-- seed_client_processes.sql
-- Distribuye procesos de InQuality a los demás clientes para testing.
-- Duplica entre 5 y 10 procesos aleatorios por org (deep copy: proceso + actividades + gateways + resource assignments).
-- Los procesos de InQuality NO se modifican ni eliminan.
-- Ejecutar desde el SQL Editor de Supabase (o psql con service_role).

DO $$
DECLARE
  inquality_org_id uuid;
  client_org       record;
  source_proc      record;
  new_proc_id      uuid;
  source_act       record;
  new_act_id       uuid;
  proc_count       int := 0;
BEGIN
  -- Obtener InQuality (is_provider = true)
  SELECT id INTO inquality_org_id
  FROM organizations
  WHERE is_provider = true
  LIMIT 1;

  IF inquality_org_id IS NULL THEN
    RAISE EXCEPTION 'No se encontró una organización con is_provider = true';
  END IF;

  RAISE NOTICE 'InQuality org_id: %', inquality_org_id;

  -- Para cada org cliente
  FOR client_org IN
    SELECT id, name
    FROM organizations
    WHERE is_provider = false
    ORDER BY name
  LOOP
    proc_count := 0;
    RAISE NOTICE '→ Distribuyendo a: %', client_org.name;

    -- Seleccionar entre 5 y 10 procesos aleatorios de InQuality
    FOR source_proc IN
      SELECT *
      FROM processes
      WHERE org_id = inquality_org_id
      ORDER BY RANDOM()
      LIMIT 5 + FLOOR(RANDOM() * 6)::int   -- 5..10 inclusive
    LOOP
      new_proc_id := gen_random_uuid();

      -- 1. Duplicar proceso
      INSERT INTO processes (
        id, name, client, bpmn_xml, currency,
        overhead_percentage, annual_frequency, analysis_horizon_years,
        automation_investment, group_id, area_id, org_id, tags,
        owner_id, created_by, updated_by, updated_at
      ) VALUES (
        new_proc_id,
        source_proc.name,
        source_proc.client,
        source_proc.bpmn_xml,
        source_proc.currency,
        source_proc.overhead_percentage,
        source_proc.annual_frequency,
        source_proc.analysis_horizon_years,
        source_proc.automation_investment,
        NULL,             -- group_id: columna legacy, no asignar
        NULL,             -- area_id: sin área; asignar manualmente si aplica
        client_org.id,    -- org destino
        source_proc.tags,
        source_proc.owner_id,
        source_proc.created_by,
        source_proc.updated_by,
        NOW()
      );

      -- 2. Duplicar actividades + sus resource assignments
      FOR source_act IN
        SELECT * FROM activities WHERE process_id = source_proc.id
      LOOP
        new_act_id := gen_random_uuid();

        INSERT INTO activities (
          id, process_id, bpmn_element_id, name, type,
          direct_cost_fixed, execution_time_minutes,
          automatable, automated_cost_fixed, automated_time_minutes,
          created_by, updated_by, created_at, updated_at
        ) VALUES (
          new_act_id,
          new_proc_id,
          source_act.bpmn_element_id,
          source_act.name,
          source_act.type,
          source_act.direct_cost_fixed,
          source_act.execution_time_minutes,
          source_act.automatable,
          source_act.automated_cost_fixed,
          source_act.automated_time_minutes,
          source_act.created_by,
          source_act.updated_by,
          NOW(),
          NOW()
        );

        -- Copiar resource assignments para esta actividad (si los hay)
        INSERT INTO activity_resource_assignments (
          id, activity_id, resource_id, utilization_minutes, units, scenario
        )
        SELECT
          gen_random_uuid(),
          new_act_id,
          resource_id,
          utilization_minutes,
          units,
          scenario
        FROM activity_resource_assignments
        WHERE activity_id = source_act.id;

      END LOOP;

      -- 3. Duplicar gateways
      INSERT INTO gateways (
        id, process_id, bpmn_element_id, gateway_type, branches,
        created_at, updated_at
      )
      SELECT
        gen_random_uuid(),
        new_proc_id,
        bpmn_element_id,
        gateway_type,
        branches,
        NOW(),
        NOW()
      FROM gateways
      WHERE process_id = source_proc.id;

      proc_count := proc_count + 1;
      RAISE NOTICE '    ✓ %', source_proc.name;
    END LOOP;

    RAISE NOTICE '  → % procesos duplicados para %', proc_count, client_org.name;
  END LOOP;

  RAISE NOTICE 'Script completado.';
END $$;