-- Expand npcs.type and town_buildings.building_type; split mixed quest_givers; rotate merchants into armorer/weapon/jeweler.

ALTER TABLE public.npcs DROP CONSTRAINT IF EXISTS npcs_type_check;
ALTER TABLE public.town_buildings DROP CONSTRAINT IF EXISTS town_buildings_building_type_check;

-- visit_town + kill_count on same NPC → new elder NPC owns visit_town quests only
DO $$
DECLARE
  r RECORD;
  new_bid bigint;
  new_nid bigint;
BEGIN
  FOR r IN
    SELECT n.id AS nid, n.town_id, n.offset_x AS ox, n.offset_y AS oy
    FROM public.npcs n
    WHERE n.type = 'quest_giver'
      AND EXISTS (SELECT 1 FROM public.quests q WHERE q.npc_id = n.id AND q.type = 'visit_town')
      AND EXISTS (SELECT 1 FROM public.quests q WHERE q.npc_id = n.id AND q.type = 'kill_count')
  LOOP
    INSERT INTO public.town_buildings (town_id, building_type, offset_x, offset_y, facing, footprint_w, footprint_h, created_at)
    VALUES (r.town_id, 'house.elder', r.ox + 11, r.oy + 0.5, '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 (r.town_id, 'Town Speaker', 'npc.town_speaker_generic.v1', 'elder', r.ox + 11, r.oy + 1.6, now(), new_bid)
    RETURNING id INTO new_nid;

    UPDATE public.quests SET npc_id = new_nid WHERE npc_id = r.nid AND type = 'visit_town';
  END LOOP;
END $$;

UPDATE public.npcs SET type = 'elder'
WHERE type = 'quest_giver'
  AND EXISTS (SELECT 1 FROM public.quests q WHERE q.npc_id = npcs.id AND q.type = 'visit_town')
  AND NOT EXISTS (SELECT 1 FROM public.quests q WHERE q.npc_id = npcs.id AND q.type = 'kill_count');

UPDATE public.npcs SET type = 'bounty_hunter' WHERE type = 'quest_giver';

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY town_id ORDER BY id) AS rn
  FROM public.npcs
  WHERE type = 'merchant'
)
UPDATE public.npcs n
SET type = CASE ((r.rn - 1) % 4)
             WHEN 0 THEN 'merchant'
             WHEN 1 THEN 'armorer'
             WHEN 2 THEN 'weapon'
             ELSE 'jeweler'
           END
FROM ranked r
WHERE n.id = r.id;

UPDATE public.town_buildings b
SET building_type = CASE n.type
  WHEN 'merchant' THEN 'house.merchant'
  WHEN 'armorer' THEN 'house.armorer'
  WHEN 'weapon' THEN 'house.weapon_smith'
  WHEN 'jeweler' THEN 'house.jeweler'
  WHEN 'bounty_hunter' THEN 'house.bounty_hunter'
  WHEN 'elder' THEN 'house.elder'
  ELSE b.building_type
END
FROM public.npcs n
WHERE n.building_id = b.id
  AND n.type IN ('merchant', 'armorer', 'weapon', 'jeweler', 'bounty_hunter', 'elder');

ALTER TABLE public.npcs ADD CONSTRAINT npcs_type_check CHECK (type = ANY (ARRAY[
  'merchant'::text, 'armorer'::text, 'weapon'::text, 'jeweler'::text,
  'bounty_hunter'::text, 'elder'::text, 'healer'::text
]));

ALTER TABLE public.town_buildings ADD CONSTRAINT town_buildings_building_type_check CHECK (building_type = ANY (ARRAY[
  'house.quest_giver'::text,
  'house.merchant'::text, 'house.armorer'::text, 'house.weapon_smith'::text, 'house.jeweler'::text,
  'house.bounty_hunter'::text, 'house.elder'::text,
  'house.healer'::text,
  'decoration.well'::text, 'decoration.stall'::text, 'decoration.signpost'::text
]));

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