-- Paystack as the payment gateway, alongside Monnify.
--
-- New customers get a Paystack dedicated account number and the customer portal's checkout runs
-- on Paystack (PAYMENT_PROVIDER=paystack). Accounts Monnify already issued keep working: their
-- transfers still arrive through the Monnify webhook, matched by account_reference as before.

-- Which gateway issued the account number shown to the customer (NULL: none yet).
ALTER TABLE customers
  ADD COLUMN account_provider ENUM('monnify', 'paystack') NULL AFTER account_reference,
  ADD COLUMN paystack_customer_code VARCHAR(40) NULL AFTER account_provider,
  ADD COLUMN paystack_customer_id BIGINT UNSIGNED NULL AFTER paystack_customer_code,
  -- The customer's own bank account for Paystack's verification (PAYSTACK_VERIFY_CUSTOMERS),
  -- encrypted, and wiped together with the BVN once the account number is issued.
  ADD COLUMN bank_details_encrypted VARCHAR(255) NULL AFTER kyc_hash,
  -- A Monnify account number replaced by a Paystack one; transfers into it are still credited.
  ADD COLUMN legacy_account_number VARCHAR(20) NULL AFTER account_name,
  ADD COLUMN legacy_bank_name VARCHAR(80) NULL AFTER legacy_account_number,
  -- Moving an existing customer to a Paystack account number (verification happens in the background)
  ADD COLUMN account_move_pending TINYINT(1) NOT NULL DEFAULT 0 AFTER account_next_retry_at,
  ADD COLUMN account_move_error VARCHAR(255) NULL AFTER account_move_pending,
  ADD UNIQUE KEY uq_customers_paystack_code (paystack_customer_code);

UPDATE customers SET account_provider = 'monnify' WHERE account_number IS NOT NULL;

ALTER TABLE payments
  MODIFY method ENUM('deposit', 'cash', 'bank_transfer', 'monnify_transfer', 'monnify_card', 'reversal', 'credit_transfer',
                     'paystack_transfer', 'paystack_card', 'paystack_other') NOT NULL;

-- Which gateway a portal checkout went through.
ALTER TABLE payment_intents ADD COLUMN provider ENUM('monnify', 'paystack') NOT NULL DEFAULT 'monnify' AFTER loan_id;

-- Audit log of every webhook Paystack sends us.
CREATE TABLE paystack_events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(60) NULL,
  reference VARCHAR(120) NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  outcome VARCHAR(40) NOT NULL,
  message VARCHAR(255) NULL,
  payload LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_paystack_events_reference (reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;