-- Admin approval of agent onboarding, plan length on record, decision audit trail, agent notifications.

-- Existing customers were onboarded before approvals existed: they stay approved.
ALTER TABLE customers
  ADD COLUMN approval_status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'approved' AFTER account_next_retry_at,
  ADD COLUMN approval_decided_at DATETIME NULL AFTER approval_status,
  ADD COLUMN approval_decided_by INT UNSIGNED NULL AFTER approval_decided_at,
  ADD COLUMN rejection_reason VARCHAR(500) NULL AFTER approval_decided_by,
  ADD COLUMN plan_periods SMALLINT UNSIGNED NULL AFTER frequency,
  ADD KEY ix_customers_approval (approval_status, created_at),
  ADD CONSTRAINT fk_customers_decided_by FOREIGN KEY (approval_decided_by) REFERENCES admins (id);

-- New rows must say how they were approved; the safe default is "pending".
ALTER TABLE customers ALTER COLUMN approval_status SET DEFAULT 'pending';

-- A rejected application gives its device back to the agent's stock, so it no longer holds one.
ALTER TABLE customers DROP FOREIGN KEY fk_customers_device;
ALTER TABLE customers MODIFY device_id INT UNSIGNED NULL;
ALTER TABLE customers ADD CONSTRAINT fk_customers_device FOREIGN KEY (device_id) REFERENCES devices (id);

-- Record the agreed number of installments for existing plans (whole-kobo arithmetic, as the app does).
UPDATE customers
  SET plan_periods = CAST(ROUND((total_cost - initial_deposit) * 100) AS UNSIGNED) DIV CAST(ROUND(installment_amount * 100) AS UNSIGNED)
    + (CAST(ROUND((total_cost - initial_deposit) * 100) AS UNSIGNED) MOD CAST(ROUND(installment_amount * 100) AS UNSIGNED) > 0)
  WHERE plan_periods IS NULL AND installment_amount > 0;

-- Append-only history of every application: who submitted, edited, approved or rejected it, when and why.
CREATE TABLE customer_approval_events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  action ENUM('submitted', 'edited', 'approved', 'rejected') NOT NULL,
  actor_type ENUM('admin', 'agent', 'system') NOT NULL,
  actor_id INT UNSIGNED NULL,
  -- Name at the time, so the record stays readable if the account is renamed later
  actor_name VARCHAR(120) NOT NULL,
  -- Rejection reason, or the admin's note on an approval
  reason VARCHAR(500) NULL,
  -- JSON list of { field, from, to } for edits (a BVN/NIN is only ever shown by its last 4 digits)
  changes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_approval_events_customer (customer_id, id),
  CONSTRAINT fk_approval_events_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- In-app notifications (e.g. telling an agent their application was approved or rejected)
CREATE TABLE notifications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient_type ENUM('admin', 'agent') NOT NULL,
  recipient_id INT UNSIGNED NOT NULL,
  kind VARCHAR(40) NOT NULL,
  tone ENUM('success', 'danger', 'warning', 'orange', 'info') NOT NULL DEFAULT 'info',
  title VARCHAR(160) NOT NULL,
  body VARCHAR(500) NULL,
  link VARCHAR(255) NULL,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_notifications_recipient (recipient_type, recipient_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;