-- ===========================================================================
-- Migration: Access requests (self-registration by approval)
-- Date: 2026-07-30
--
-- A public "Request access" form captures a prospective farm's details and
-- creates a PENDING request. A platform admin reviews it and, on approval,
-- provisions the farm + owner (the owner gets a password-reset link by email).
-- No self-serve farm creation. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS access_requests (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_name    VARCHAR(160) NOT NULL,
  contact_name VARCHAR(120) NULL,
  email        VARCHAR(160) NOT NULL,
  phone        VARCHAR(40)  NULL,
  note         VARCHAR(500) NULL,
  status       ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  farm_id      INT UNSIGNED NULL,          -- the farm created on approval
  reviewed_by  VARCHAR(160) NULL,
  reviewed_at  TIMESTAMP    NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_accreq_status (status, created_at),
  CONSTRAINT fk_accreq_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE SET NULL
) ENGINE=InnoDB;