-- ===========================================================================
-- Migration: online mobile-money payments (Yo! Uganda)
-- Date: 2026-07-28
--
-- Lets a farm pay its subscription invoices and buy prepaid SMS credit from its
-- own dashboard via a mobile-money prompt. Each attempt is a `payments` row (a
-- payment intent): created PENDING when the farm taps Pay, flipped to succeeded/
-- failed once Yo! confirms (via the IPN webhook AND/OR status polling). `applied`
-- guards against double-crediting — the money effect (mark invoice paid / add SMS
-- credits) runs exactly once. schema.sql carries this for fresh installs. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS payments (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  kind          ENUM('invoice','sms') NOT NULL,          -- pay an invoice, or buy prepaid SMS credit
  invoice_id    INT UNSIGNED  NULL,                       -- for kind='invoice'
  credits       INT UNSIGNED  NULL,                       -- for kind='sms' (number of SMS bought)
  amount        DECIMAL(12,2) NOT NULL,
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  phone         VARCHAR(30)   NOT NULL,                   -- payer's mobile-money number
  external_ref  VARCHAR(40)   NOT NULL,                   -- our idempotency key, sent to Yo! as PrivateTransactionReference
  provider_ref  VARCHAR(64)   NULL,                       -- Yo!'s own TransactionReference
  status        ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
  applied       TINYINT(1)    NOT NULL DEFAULT 0,         -- has the money effect been applied? (dedupe)
  message       VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payment_ref (external_ref),
  KEY idx_payment_farm (farm_id, created_at),
  KEY idx_payment_invoice (invoice_id),
  CONSTRAINT fk_payment_farm    FOREIGN KEY (farm_id)    REFERENCES farms(id)            ON DELETE CASCADE,
  CONSTRAINT fk_payment_invoice FOREIGN KEY (invoice_id) REFERENCES billing_invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB;