-- Give farms.parent_id a real foreign key so deleting a head farm can't leave
-- its branches orphaned behind a dangling parent_id. ON DELETE SET NULL turns a
-- deleted head's branches into standalone farms instead.
--
-- Safe/idempotent-ish: adds an index + constraint the first time. If a branch
-- somehow already points at a non-existent head, clean that up first so the FK
-- can be created.

-- 1) Heal any pre-existing dangling pointers (parent_id -> a farm that's gone).
UPDATE farms c
LEFT JOIN farms p ON p.id = c.parent_id
SET c.parent_id = NULL
WHERE c.parent_id IS NOT NULL AND p.id IS NULL;

-- 2) Index parent_id (InnoDB needs it for the FK; harmless if it already exists).
ALTER TABLE farms ADD INDEX idx_farms_parent (parent_id);

-- 3) The self-referential foreign key.
ALTER TABLE farms
  ADD CONSTRAINT fk_farms_parent FOREIGN KEY (parent_id) REFERENCES farms(id) ON DELETE SET NULL;