-- ============================================================
-- SBM SmartLedger migration: opening-fee tracking, monthly SMS fee, custom income
-- ------------------------------------------------------------
-- Safe to re-run.
-- ============================================================

-- 1) Track whether an account's one-off opening fee has been collected yet.
SET @col := (SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name = 'account' AND column_name = 'opening_fee_charged');
SET @sql := IF(@col = 0,
  "ALTER TABLE account ADD COLUMN opening_fee_charged TINYINT(1) NOT NULL DEFAULT 0 AFTER balance_minor",
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Existing accounts that already have a positive balance are treated as having
-- settled their opening fee, so we don't retroactively bill old customers.
UPDATE account SET opening_fee_charged = 1 WHERE balance_minor > 0;

-- 2) Custom income sources (MoMo commission, school-fees commission, etc.).
--    Each maps to an income ledger account so it flows into the Revenue report.
CREATE TABLE IF NOT EXISTS income_source (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  name          VARCHAR(80) NOT NULL,
  ledger_code   VARCHAR(40) NOT NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_income_source_tenant (tenant_id),
  UNIQUE KEY uq_income_source (tenant_id, ledger_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;