-- ---------------------------------------------------------------------------
-- Migration: SMS notifications (mortality spike, feed reorder, sale receipt,
-- vaccination due/overdue) via Pahappa's EgoSMS gateway.
-- ---------------------------------------------------------------------------
-- schema.sql already carries this for FRESH installs. Run this ONCE against
-- an EXISTING database (paste into phpMyAdmin's SQL tab, or
-- `mysql -u <user> -p <db> < sql/migration-2026-07-sms-notifications.sql`).
-- Nothing here touches existing rows beyond adding a nullable column.
-- ---------------------------------------------------------------------------

ALTER TABLE farms
  ADD COLUMN phone VARCHAR(30) NULL AFTER timezone;

CREATE TABLE IF NOT EXISTS sms_log (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  kind          VARCHAR(30)   NOT NULL,
  ref_id        INT UNSIGNED  NULL,
  phone         VARCHAR(30)   NOT NULL,
  message       VARCHAR(400)  NOT NULL,
  status        VARCHAR(20)   NOT NULL,
  response      VARCHAR(255)  NULL,
  sent_at       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sms_farm (farm_id, sent_at),
  KEY idx_sms_dedupe (farm_id, kind, ref_id),
  CONSTRAINT fk_sms_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;