-- Head farm + branches, and per-user access to multiple farms.
--   * farms.parent_id — a branch points at its head farm (NULL = head/standalone).
--                       Each branch is still a full, independently-scoped farm.
--   * user_farms       — grants a user access to a farm WITH a role there, so one
--                        person can be assigned to several farms and switch.
-- Every existing user is seeded a membership to their current (home) farm + role,
-- so nothing changes for them until they're granted access to more farms.
--
-- Additive and idempotent — safe to run before the feature code ships, and safe
-- to re-run.

ALTER TABLE farms ADD COLUMN IF NOT EXISTS parent_id INT UNSIGNED NULL AFTER name;

CREATE TABLE IF NOT EXISTS user_farms (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  farm_id    INT UNSIGNED NOT NULL,
  role_id    INT UNSIGNED NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_farm (user_id, farm_id),
  KEY idx_uf_user (user_id),
  KEY idx_uf_farm (farm_id),
  CONSTRAINT fk_uf_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_uf_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_uf_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seed each existing user's home farm as a membership (keeping their current role).
INSERT INTO user_farms (user_id, farm_id, role_id)
SELECT u.id, u.farm_id, u.role_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM user_farms uf WHERE uf.user_id = u.id AND uf.farm_id = u.farm_id
);