-- Operations: payment reversals, customer payment reminders, admin team management, activity log.

-- Payments are never edited or deleted. A wrong manual payment is cancelled by a reversal: a
-- negative entry pointing at the original, so totals net out and both stay in the history.
ALTER TABLE payments
  MODIFY method ENUM('deposit', 'cash', 'bank_transfer', 'monnify_transfer', 'monnify_card', 'reversal') NOT NULL,
  ADD COLUMN reverses_payment_id INT UNSIGNED NULL AFTER note,
  ADD UNIQUE KEY uq_payments_reverses (reverses_payment_id),
  ADD CONSTRAINT fk_payments_reverses FOREIGN KEY (reverses_payment_id) REFERENCES payments (id);

-- One row per reminder sent (or attempted); the unique key stops the same reminder going out twice.
CREATE TABLE payment_reminders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  kind ENUM('due_tomorrow', 'due_today', 'missed', 'overdue', 'manual') NOT NULL,
  -- The installment due date it's about (for manual reminders: the day it was sent)
  due_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  channels VARCHAR(40) NOT NULL DEFAULT '',
  status ENUM('sending', 'sent', 'failed') NOT NULL DEFAULT 'sending',
  error VARCHAR(255) NULL,
  sent_by_type ENUM('system', 'admin', 'agent') NOT NULL DEFAULT 'system',
  sent_by_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reminders_once (customer_id, kind, due_date),
  KEY ix_reminders_created (created_at),
  CONSTRAINT fk_reminders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- New admins get a temporary password and must change it at first sign-in, like agents.
ALTER TABLE admins
  ADD COLUMN must_change_password TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active,
  ADD COLUMN created_by INT UNSIGNED NULL AFTER must_change_password;

-- Append-only record of sensitive actions: who did what, to what, when, and from where.
CREATE TABLE activity_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type ENUM('admin', 'agent', 'customer', 'system', 'anonymous') NOT NULL,
  actor_id INT UNSIGNED NULL,
  actor_name VARCHAR(190) NOT NULL,
  action VARCHAR(60) NOT NULL,
  entity_type VARCHAR(30) NULL,
  entity_id VARCHAR(40) NULL,
  summary VARCHAR(500) NOT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_activity_created (created_at),
  KEY ix_activity_entity (entity_type, entity_id),
  KEY ix_activity_actor (actor_type, actor_id),
  KEY ix_activity_action (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;