-- ===========================================================================
-- Migration: support live chat
-- Date: 2026-07-28
--
-- A single ongoing support conversation per farm. Farm users chat from a
-- floating widget; the platform operator answers every farm's thread from the
-- Platform → Support dashboard. "Live" is achieved by short-interval polling
-- (this stack has no WebSocket support), so there is nothing real-time to host.
--
-- Unread is derived, not stored per message: each side keeps a "last read"
-- message id on the thread, and unread = messages from the OTHER side newer
-- than that id. schema.sql carries these for fresh installs. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS support_threads (
  id                 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id            INT UNSIGNED NOT NULL,
  status             ENUM('open','closed') NOT NULL DEFAULT 'open',
  last_message_at    TIMESTAMP    NULL,
  admin_last_read_id INT UNSIGNED NOT NULL DEFAULT 0,   -- newest message the operator has seen
  farm_last_read_id  INT UNSIGNED NOT NULL DEFAULT 0,   -- newest message the farm has seen
  created_at         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_support_farm (farm_id),
  CONSTRAINT fk_support_thread_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_messages (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  thread_id     INT UNSIGNED  NOT NULL,
  farm_id       INT UNSIGNED  NOT NULL,
  sender        ENUM('farm','admin') NOT NULL,
  user_id       INT UNSIGNED  NULL,          -- who sent it (nullable so the log survives a user delete)
  user_name     VARCHAR(120)  NULL,          -- snapshot of the sender's name at send time
  body          VARCHAR(2000) NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_support_msg_thread (thread_id, id),
  KEY idx_support_msg_farm (farm_id, id),
  CONSTRAINT fk_support_msg_thread FOREIGN KEY (thread_id) REFERENCES support_threads(id) ON DELETE CASCADE,
  CONSTRAINT fk_support_msg_farm   FOREIGN KEY (farm_id)   REFERENCES farms(id)            ON DELETE CASCADE
) ENGINE=InnoDB;