-- ===========================================================================
-- Migration: CRM / marketing
-- Date: 2026-07-30
--
-- Builds on the existing Customers directory:
--   * customers.email                 — so email campaigns have an address
--   * crm_campaigns / _recipients      — SMS/email blasts to a segment, sent via
--                                        the EgoSMS gateway / SMTP, with per-
--                                        recipient status
--   * crm_followups                    — scheduled follow-up tasks per customer
--   * crm_complaints                   — complaints/feedback, tracked to resolution
-- Idempotent.
-- ===========================================================================

ALTER TABLE customers
  ADD COLUMN IF NOT EXISTS email VARCHAR(160) NULL AFTER phone;

CREATE TABLE IF NOT EXISTS crm_campaigns (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id      INT UNSIGNED  NOT NULL,
  name         VARCHAR(120)  NOT NULL,
  channel      ENUM('sms','email') NOT NULL DEFAULT 'sms',
  audience     VARCHAR(30)   NOT NULL DEFAULT 'all',   -- all | recent
  subject      VARCHAR(160)  NULL,                     -- email only
  message      TEXT          NOT NULL,
  status       ENUM('draft','sent') NOT NULL DEFAULT 'draft',
  total        INT UNSIGNED  NOT NULL DEFAULT 0,
  sent_count   INT UNSIGNED  NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED  NOT NULL DEFAULT 0,
  sent_at      DATETIME      NULL,
  created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_crmc_farm (farm_id),
  CONSTRAINT fk_crmc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crm_campaign_recipients (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  campaign_id INT UNSIGNED  NOT NULL,
  farm_id     INT UNSIGNED  NOT NULL,
  customer_id INT UNSIGNED  NULL,
  name        VARCHAR(160)  NULL,
  contact     VARCHAR(160)  NULL,                      -- phone (SMS) or email
  status      ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending',
  detail      VARCHAR(255)  NULL,                      -- gateway/mailer message on failure
  sent_at     DATETIME      NULL,
  KEY idx_crmr_campaign (campaign_id),
  CONSTRAINT fk_crmr_campaign FOREIGN KEY (campaign_id) REFERENCES crm_campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_crmr_farm     FOREIGN KEY (farm_id)     REFERENCES farms(id)         ON DELETE CASCADE,
  CONSTRAINT fk_crmr_customer FOREIGN KEY (customer_id) REFERENCES customers(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crm_followups (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  customer_id   INT UNSIGNED NULL,
  customer_name VARCHAR(160) NULL,                     -- snapshot (for a one-off contact)
  title         VARCHAR(200) NOT NULL,
  due_on        DATE         NOT NULL,
  assigned_to   VARCHAR(120) NULL,
  status        ENUM('open','done') NOT NULL DEFAULT 'open',
  done_on       DATE         NULL,
  notes         VARCHAR(500) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_crmf_farm (farm_id, status, due_on),
  KEY idx_crmf_customer (customer_id),
  CONSTRAINT fk_crmf_farm     FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_crmf_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crm_complaints (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  customer_id   INT UNSIGNED NULL,
  customer_name VARCHAR(160) NULL,
  subject       VARCHAR(200) NOT NULL,
  severity      ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  description   VARCHAR(500) NULL,
  action_taken  VARCHAR(500) NULL,
  status        ENUM('open','resolved') NOT NULL DEFAULT 'open',
  occurred_on   DATE         NOT NULL,
  resolved_on   DATE         NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_crmx_farm (farm_id, status),
  KEY idx_crmx_customer (customer_id),
  CONSTRAINT fk_crmx_farm     FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_crmx_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;