-- Per-town NPC count target: clamp(3, 6, 3 + floor((radius-7)/4)); add healers first, then rotating gear stalls.
-- New hub town 32 (Capital) with 10 NPCs; bidirectional roads to every town 1..31.

DO $$
DECLARE
  t RECORD;
  need int;
  have int;
  new_bid bigint;
  rot int := 0;
  vendor_types text[] := ARRAY['merchant', 'armorer', 'weapon', 'jeweler'];
  tx double precision;
  ty double precision;
  slot int;
BEGIN
  FOR t IN SELECT id, radius FROM public.towns WHERE id BETWEEN 1 AND 31 ORDER BY id
  LOOP
    have := (SELECT COUNT(*)::int FROM public.npcs WHERE town_id = t.id);
    need := LEAST(6, GREATEST(3, 3 + (GREATEST(0, FLOOR(t.radius)::int - 7) / 4)));

    WHILE have < need LOOP
      IF NOT EXISTS (SELECT 1 FROM public.npcs WHERE town_id = t.id AND type = 'healer') THEN
        tx := -16 + (have * 2.5);
        ty := 14;
        INSERT INTO public.town_buildings (town_id, building_type, offset_x, offset_y, facing, footprint_w, footprint_h, created_at)
        VALUES (t.id, 'house.healer', tx, ty, 'south', 2.5, 2, now())
        RETURNING id INTO new_bid;

        INSERT INTO public.npcs (town_id, name, name_key, type, offset_x, offset_y, created_at, building_id)
        VALUES (t.id, 'Roadside Medic', 'npc.roadside_medic_generic.v1', 'healer', tx, ty + 1.1, now(), new_bid);
      ELSE
        slot := (rot % 4) + 1;
        tx := -16 + (have * 2.8);
        ty := 16;
        INSERT INTO public.town_buildings (town_id, building_type, offset_x, offset_y, facing, footprint_w, footprint_h, created_at)
        VALUES (t.id,
          CASE slot
            WHEN 1 THEN 'house.merchant'
            WHEN 2 THEN 'house.armorer'
            WHEN 3 THEN 'house.weapon_smith'
            ELSE 'house.jeweler'
          END,
          tx, ty, 'south', 2.5, 2, now())
        RETURNING id INTO new_bid;

        INSERT INTO public.npcs (town_id, name, name_key, type, offset_x, offset_y, created_at, building_id)
        VALUES (t.id,
          'Stall Hand ' || have::text,
          'npc.stall_vendor_generic.v1',
          vendor_types[slot],
          tx, ty + 1.2, now(), new_bid);
        rot := rot + 1;
      END IF;

      have := have + 1;
    END LOOP;
  END LOOP;
END $$;

INSERT INTO public.towns (id, name, name_key, biome, world_x, world_y, radius, level_min, level_max, created_at)
SELECT
  32,
  'Capital',
  'town.capital.v1',
  'meadow',
  s.mx + 22000,
  s.my,
  26,
  1,
  60,
  now()
FROM (SELECT MAX(world_x) AS mx, AVG(world_y) AS my FROM public.towns WHERE id BETWEEN 1 AND 31) AS s;

DO $$
DECLARE
  i int;
  bid bigint;
  v_types text[] := ARRAY['merchant','armorer','weapon','jeweler','bounty_hunter','bounty_hunter','elder','healer','armorer','jeweler'];
  v_bld text[] := ARRAY['house.merchant','house.armorer','house.weapon_smith','house.jeweler','house.bounty_hunter','house.bounty_hunter','house.elder','house.healer','house.armorer','house.jeweler'];
  v_ox double precision[] := ARRAY[-12::double precision,-4,4,12,-12,-4,4,12,-8,8];
  v_oy double precision[] := ARRAY[-8::double precision,-8,-8,-8,0,0,0,0,8,8];
  v_name text;
  v_key text;
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO public.town_buildings (town_id, building_type, offset_x, offset_y, facing, footprint_w, footprint_h, created_at)
    VALUES (32, v_bld[i], v_ox[i], v_oy[i], 'south', 2.5, 2, now())
    RETURNING id INTO bid;

    v_name := CASE v_types[i]
      WHEN 'merchant' THEN 'Emporium Clerk'
      WHEN 'armorer' THEN 'Royal Armorer'
      WHEN 'weapon' THEN 'Arena Smith'
      WHEN 'jeweler' THEN 'Crown Jeweler'
      WHEN 'bounty_hunter' THEN 'Contract Agent'
      WHEN 'elder' THEN 'Seneschal'
      WHEN 'healer' THEN 'Cathedral Medic'
      ELSE 'Hall Attendant'
    END;
    v_key := CASE i
      WHEN 1 THEN 'npc.capital.merchant_clerk.v1'
      WHEN 2 THEN 'npc.capital.armorer.v1'
      WHEN 3 THEN 'npc.capital.smith.v1'
      WHEN 4 THEN 'npc.capital.jeweler.v1'
      WHEN 5 THEN 'npc.capital.bounty_agent_a.v1'
      WHEN 6 THEN 'npc.capital.bounty_agent_b.v1'
      WHEN 7 THEN 'npc.capital.elder.v1'
      WHEN 8 THEN 'npc.capital.healer.v1'
      WHEN 9 THEN 'npc.capital.second_armorer.v1'
      ELSE 'npc.capital.second_jeweler.v1'
    END;

    INSERT INTO public.npcs (town_id, name, name_key, type, offset_x, offset_y, created_at, building_id)
    VALUES (32, v_name, v_key, v_types[i], v_ox[i], v_oy[i] + 1.2, now(), bid);
  END LOOP;
END $$;

DO $$
DECLARE
  i int;
  rid bigint;
BEGIN
  SELECT COALESCE(MAX(id), 0) INTO rid FROM public.roads;
  FOR i IN 1..31 LOOP
    rid := rid + 1;
    INSERT INTO public.roads (id, from_town_id, to_town_id, distance) VALUES (rid, 32, i, 1000);
    rid := rid + 1;
    INSERT INTO public.roads (id, from_town_id, to_town_id, distance) VALUES (rid, i, 32, 1000);
  END LOOP;
END $$;

SELECT pg_catalog.setval('public.towns_id_seq', (SELECT COALESCE(MAX(id), 1) FROM public.towns), true);
SELECT pg_catalog.setval('public.town_buildings_id_seq', (SELECT COALESCE(MAX(id), 1) FROM public.town_buildings), true);
SELECT pg_catalog.setval('public.npcs_id_seq', (SELECT COALESCE(MAX(id), 1) FROM public.npcs), true);
SELECT pg_catalog.setval('public.roads_id_seq', (SELECT COALESCE(MAX(id), 1) FROM public.roads), true);
