-- ===========================================================================
-- Migration: billing & subscriptions (platform-level)
-- Date: 2026-07-28
--
-- Adds the SaaS billing layer on top of the platform-admin tier:
--   plans              — the pricing catalogue (price, cycle, trial, limits, SMS terms)
--   subscriptions      — one per farm; effective terms (seeded from a plan, overridable)
--   billing_invoices   — subscription / SMS / manual charges
--   billing_payments   — manually-recorded payments against those invoices
--   sms_credit_ledger  — prepaid SMS credit movement (top-ups + per-send usage)
--
-- SMS is billed either PREPAID (a credit balance on the subscription that
-- decrements per successful send and blocks at zero) or POSTPAID (usage metered
-- from sms_log and invoiced at each renewal). schema.sql carries all of this for
-- fresh installs. Idempotent (CREATE TABLE IF NOT EXISTS); safe to re-run.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS plans (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name          VARCHAR(80)   NOT NULL,
  description   VARCHAR(255)  NULL,
  price         DECIMAL(12,2) NOT NULL DEFAULT 0,      -- subscription price per cycle
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly',
  trial_days    INT UNSIGNED  NOT NULL DEFAULT 0,
  max_birds     INT UNSIGNED  NULL,                    -- NULL = unlimited
  max_users     INT UNSIGNED  NULL,                    -- NULL = unlimited
  sms_mode      ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid',
  sms_rate      DECIMAL(10,2) NOT NULL DEFAULT 0,      -- price charged to the farm per SMS
  sms_included  INT UNSIGNED  NOT NULL DEFAULT 0,      -- free SMS allowance per cycle (postpaid)
  is_active     TINYINT(1)    NOT NULL DEFAULT 1,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscriptions (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  plan_id       INT UNSIGNED  NULL,                    -- NULL once fully custom / plan deleted
  status        ENUM('trial','active','past_due','suspended','cancelled') NOT NULL DEFAULT 'trial',
  price         DECIMAL(12,2) NOT NULL DEFAULT 0,      -- effective price (override of plan)
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly',
  trial_ends_on DATE          NULL,
  period_start  DATE          NULL,
  period_end    DATE          NULL,                    -- next due / renewal date
  max_birds     INT UNSIGNED  NULL,
  max_users     INT UNSIGNED  NULL,
  sms_mode      ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid',
  sms_rate      DECIMAL(10,2) NOT NULL DEFAULT 0,
  sms_included  INT UNSIGNED  NOT NULL DEFAULT 0,
  sms_balance   INT           NOT NULL DEFAULT 0,      -- prepaid credit balance (SMS count); signed to tolerate a race
  notes         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_sub_farm (farm_id),
  KEY idx_sub_plan (plan_id),
  CONSTRAINT fk_sub_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS billing_invoices (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  invoice_no    VARCHAR(40)   NOT NULL,
  type          ENUM('subscription','sms','manual') NOT NULL DEFAULT 'subscription',
  description   VARCHAR(255)  NULL,
  period_start  DATE          NULL,
  period_end    DATE          NULL,
  amount        DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  status        ENUM('unpaid','paid','void') NOT NULL DEFAULT 'unpaid',
  issued_on     DATE          NOT NULL,
  due_on        DATE          NULL,
  paid_on       DATE          NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_binv (farm_id, invoice_no),
  KEY idx_binv_status (farm_id, status),
  CONSTRAINT fk_binv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS billing_payments (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  invoice_id    INT UNSIGNED  NULL,
  amount        DECIMAL(12,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NULL,
  reference     VARCHAR(80)   NULL,
  paid_on       DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  recorded_by   INT UNSIGNED  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bpay_farm (farm_id, paid_on),
  CONSTRAINT fk_bpay_farm    FOREIGN KEY (farm_id)    REFERENCES farms(id)            ON DELETE CASCADE,
  CONSTRAINT fk_bpay_invoice FOREIGN KEY (invoice_id) REFERENCES billing_invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sms_credit_ledger (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  delta         INT           NOT NULL,                -- +top-up / -usage / +/- adjustment
  balance_after INT           NOT NULL,
  reason        VARCHAR(30)   NOT NULL,                -- 'topup' | 'usage' | 'adjustment'
  sms_log_id    INT UNSIGNED  NULL,
  note          VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_smscredit_farm (farm_id, created_at),
  CONSTRAINT fk_smscredit_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- A couple of starter plans so the catalogue isn't empty on first open. Inserted
-- only if no plans exist yet, so re-running never duplicates them.
INSERT INTO plans (name, description, price, currency, billing_cycle, trial_days, max_birds, max_users, sms_mode, sms_rate, sms_included)
SELECT * FROM (SELECT
  'Starter' AS name, 'Small farm — core records & reports' AS description, 50000 AS price, 'UGX' AS currency,
  'monthly' AS billing_cycle, 14 AS trial_days, 2000 AS max_birds, 3 AS max_users,
  'postpaid' AS sms_mode, 45.00 AS sms_rate, 50 AS sms_included) t
WHERE NOT EXISTS (SELECT 1 FROM plans);

INSERT INTO plans (name, description, price, currency, billing_cycle, trial_days, max_birds, max_users, sms_mode, sms_rate, sms_included)
SELECT * FROM (SELECT
  'Pro' AS name, 'Growing farm — higher limits & SMS bundle' AS description, 150000 AS price, 'UGX' AS currency,
  'monthly' AS billing_cycle, 14 AS trial_days, NULL AS max_birds, 10 AS max_users,
  'postpaid' AS sms_mode, 40.00 AS sms_rate, 300 AS sms_included) t
WHERE (SELECT COUNT(*) FROM plans) = 1;
