-- ============================================================
-- 002_supplier_wallet.sql
-- Supplier Point Wallet & Settlement System
-- Extends the existing Supplier Management System.
--
-- Design principles (per spec):
--   • Balances stored ONLY as POINTS (never raw money internally)
--   • NGN value computed dynamically from an admin-set exchange rate
--   • wallet_transactions is APPEND-ONLY / immutable (reversal-only)
--   • Every financial action is logged + auditable
--
-- This migration is idempotent — safe to run more than once.
-- ============================================================

-- ─── 1. Supplier role ─────────────────────────────────────
-- Suppliers authenticate against the `suppliers` table (like customers
-- do against `customers`). The role lets the JWT/middleware identify them.
INSERT INTO roles (name)
SELECT 'Supplier'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE name = 'Supplier');


-- ─── 2. Supplier auth columns ─────────────────────────────
-- Suppliers log in themselves, so they need credentials. We add these to
-- the existing `suppliers` table rather than creating a parallel one.
-- (Guards check information_schema so re-running is safe.)

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'suppliers' AND COLUMN_NAME = 'email'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE suppliers ADD COLUMN email VARCHAR(150) NULL UNIQUE AFTER phone',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'suppliers' AND COLUMN_NAME = 'password_hash'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE suppliers ADD COLUMN password_hash VARCHAR(255) NULL AFTER email',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'suppliers' AND COLUMN_NAME = 'last_login_at'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE suppliers ADD COLUMN last_login_at TIMESTAMP NULL DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ─── 3. Supplier wallets ──────────────────────────────────
-- One wallet per supplier. Balances are POINTS only.
--   available_points → spendable / withdrawable
--   locked_points    → reserved against pending withdrawals
CREATE TABLE IF NOT EXISTS supplier_wallets (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  supplier_id      INT NOT NULL UNIQUE,
  available_points DECIMAL(14,2) NOT NULL DEFAULT 0,
  locked_points    DECIMAL(14,2) NOT NULL DEFAULT 0,
  status           ENUM('active','frozen','suspended') DEFAULT 'active',
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT chk_available_non_negative CHECK (available_points >= 0),
  CONSTRAINT chk_locked_non_negative    CHECK (locked_points >= 0)
);


-- ─── 4. Wallet transactions (IMMUTABLE LEDGER) ────────────
-- Append-only. Rows are NEVER updated or deleted. A correction is a new
-- offsetting row of type 'reversal'. balance_after stores the running
-- available balance at the time of the entry for tamper-evidence.
CREATE TABLE IF NOT EXISTS wallet_transactions (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  wallet_id        INT NOT NULL,
  type             ENUM('credit','debit','withdrawal','reversal','adjustment') NOT NULL,
  points           DECIMAL(14,2) NOT NULL,          -- signed: + credit, - debit
  naira_equivalent DECIMAL(14,2) NOT NULL DEFAULT 0, -- snapshot at tx time (display only)
  exchange_rate    DECIMAL(10,4) NOT NULL DEFAULT 1, -- rate used for the snapshot
  balance_after    DECIMAL(14,2) NOT NULL,           -- available_points after this entry
  reference        VARCHAR(50) NULL,                 -- e.g. receipt_id, withdrawal request_id
  related_sale_id  INT NULL,                         -- links a credit to its sale
  status           ENUM('completed','pending','failed','reversed') DEFAULT 'completed',
  description      VARCHAR(255) NULL,
  created_by       INT NULL,                         -- staff/admin user id, NULL if system
  ip_address       VARCHAR(64) NULL,
  user_agent       VARCHAR(255) NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (wallet_id)       REFERENCES supplier_wallets(id),
  FOREIGN KEY (related_sale_id) REFERENCES sales(id),
  INDEX idx_wallet_created (wallet_id, created_at),
  INDEX idx_reference (reference)
);


-- ─── 5. Withdrawal requests ───────────────────────────────
-- A supplier requests to convert points → NGN to their verified bank
-- account. Points are LOCKED on request, deducted on approval, released
-- on rejection.
CREATE TABLE IF NOT EXISTS withdrawal_requests (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  request_id       VARCHAR(20) NOT NULL UNIQUE,      -- VFM-WD-000001
  supplier_id      INT NOT NULL,
  wallet_id        INT NOT NULL,
  points_requested DECIMAL(14,2) NOT NULL,
  exchange_rate    DECIMAL(10,4) NOT NULL DEFAULT 1, -- locked at request time
  naira_value      DECIMAL(14,2) NOT NULL,
  bank_name        VARCHAR(100) NULL,                -- snapshot of supplier bank at request
  account_number   VARCHAR(20) NULL,
  account_name     VARCHAR(100) NULL,
  status           ENUM('pending','approved','rejected','completed','failed') DEFAULT 'pending',
  review_notes     TEXT NULL,
  reviewed_by      INT NULL,
  requested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reviewed_at      TIMESTAMP NULL DEFAULT NULL,
  completed_at     TIMESTAMP NULL DEFAULT NULL,
  ip_address       VARCHAR(64) NULL,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  FOREIGN KEY (wallet_id)   REFERENCES supplier_wallets(id),
  FOREIGN KEY (reviewed_by) REFERENCES users(id),
  INDEX idx_supplier_status (supplier_id, status)
);


-- ─── 6. Wallet settings (admin-controlled) ────────────────
-- Single-row configuration. Exchange rate + risk controls live here so
-- they are never hard-coded and only admins can change them.
CREATE TABLE IF NOT EXISTS wallet_settings (
  id                    INT PRIMARY KEY AUTO_INCREMENT,
  exchange_rate         DECIMAL(10,4) NOT NULL DEFAULT 1.0000, -- 1 Point = X NGN
  min_withdrawal_points DECIMAL(14,2) NOT NULL DEFAULT 1000,
  withdrawal_hold_hours INT NOT NULL DEFAULT 24,                -- fraud-review delay
  updated_by            INT NULL,
  updated_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- Seed the single settings row once.
INSERT INTO wallet_settings (id, exchange_rate, min_withdrawal_points, withdrawal_hold_hours)
SELECT 1, 1.0000, 1000, 24
WHERE NOT EXISTS (SELECT 1 FROM wallet_settings WHERE id = 1);