-- Nexgen Energy core schema.
-- Compatible with MySQL 5.7+/8 and MariaDB 10.4+. Money is DECIMAL(14,2) in naira.

CREATE TABLE admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(100) NOT NULL,
  role ENUM('super_admin', 'admin') NOT NULL DEFAULT 'admin',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_admins_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(120) NOT NULL,
  model VARCHAR(60) NOT NULL,
  category VARCHAR(60) NOT NULL DEFAULT 'General',
  price DECIMAL(14,2) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_products_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE agents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(190) NULL,
  location VARCHAR(80) NOT NULL,
  address VARCHAR(255) NOT NULL DEFAULT '',
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  commission_rate DECIMAL(5,2) NOT NULL DEFAULT 5.00,
  password_hash VARCHAR(100) NOT NULL,
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  joined_at DATE NOT NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_agents_code (code),
  UNIQUE KEY uq_agents_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NULL,
  product_id INT UNSIGNED NOT NULL,
  serial_number VARCHAR(100) NOT NULL,
  batch_code VARCHAR(30) NOT NULL,
  date_received DATE NOT NULL,
  status ENUM('in_store', 'assigned', 'sold', 'faulty') NOT NULL DEFAULT 'in_store',
  agent_id INT UNSIGNED NULL,
  assigned_at DATE NULL,
  sold_at DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_devices_code (code),
  UNIQUE KEY uq_devices_serial (serial_number),
  KEY ix_devices_status (status),
  KEY ix_devices_product_status (product_id, status),
  KEY ix_devices_agent_status (agent_id, status),
  CONSTRAINT fk_devices_product FOREIGN KEY (product_id) REFERENCES products (id),
  CONSTRAINT fk_devices_agent FOREIGN KEY (agent_id) REFERENCES agents (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NULL,
  -- Our unique reference for the customer's Monnify reserved account
  account_reference VARCHAR(64) NOT NULL,
  account_number VARCHAR(20) NULL,
  bank_name VARCHAR(80) NULL,
  bank_code VARCHAR(10) NULL,
  account_name VARCHAR(160) NULL,
  account_status ENUM('pending', 'active', 'failed') NOT NULL DEFAULT 'pending',
  account_error VARCHAR(255) NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(190) NULL,
  location VARCHAR(80) NOT NULL,
  address VARCHAR(255) NOT NULL,
  -- BVN/NIN is passed to Monnify but never stored; only the type and last 4 digits are kept
  kyc_type ENUM('bvn', 'nin') NULL,
  kyc_last4 CHAR(4) NULL,
  agent_id INT UNSIGNED NOT NULL,
  device_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  total_cost DECIMAL(14,2) NOT NULL,
  initial_deposit DECIMAL(14,2) NOT NULL,
  installment_amount DECIMAL(14,2) NOT NULL,
  frequency ENUM('daily', 'weekly', 'monthly') NOT NULL,
  start_date DATE NOT NULL,
  created_by_type ENUM('admin', 'agent', 'system') NOT NULL DEFAULT 'agent',
  created_by_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customers_code (code),
  UNIQUE KEY uq_customers_account_reference (account_reference),
  UNIQUE KEY uq_customers_account_number (account_number),
  UNIQUE KEY uq_customers_device (device_id),
  KEY ix_customers_agent (agent_id),
  KEY ix_customers_phone (phone),
  CONSTRAINT fk_customers_agent FOREIGN KEY (agent_id) REFERENCES agents (id),
  CONSTRAINT fk_customers_device FOREIGN KEY (device_id) REFERENCES devices (id),
  CONSTRAINT fk_customers_product FOREIGN KEY (product_id) REFERENCES products (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  paid_on DATE NOT NULL,
  method ENUM('deposit', 'cash', 'bank_transfer', 'monnify_transfer', 'monnify_card') NOT NULL,
  -- Unique so the same Monnify transaction / receipt can never be recorded twice
  reference VARCHAR(120) NOT NULL,
  recorded_by_type ENUM('admin', 'agent', 'customer', 'system') NOT NULL,
  recorded_by_id INT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payments_reference (reference),
  KEY ix_payments_customer_date (customer_id, paid_on),
  KEY ix_payments_date (paid_on),
  CONSTRAINT fk_payments_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_movements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type ENUM('received', 'assigned', 'returned', 'sold', 'adjusted') NOT NULL,
  movement_date DATE NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  agent_id INT UNSIGNED NULL,
  customer_id INT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_by_type ENUM('admin', 'agent', 'system') NOT NULL DEFAULT 'system',
  created_by_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_movements_date (movement_date),
  KEY ix_movements_agent (agent_id),
  CONSTRAINT fk_movements_product FOREIGN KEY (product_id) REFERENCES products (id),
  CONSTRAINT fk_movements_agent FOREIGN KEY (agent_id) REFERENCES agents (id),
  CONSTRAINT fk_movements_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_movement_devices (
  movement_id INT UNSIGNED NOT NULL,
  device_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (movement_id, device_id),
  KEY ix_smd_device (device_id),
  CONSTRAINT fk_smd_movement FOREIGN KEY (movement_id) REFERENCES stock_movements (id) ON DELETE CASCADE,
  CONSTRAINT fk_smd_device FOREIGN KEY (device_id) REFERENCES devices (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE otp_codes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  code_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  consumed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_otp_customer (customer_id, created_at),
  CONSTRAINT fk_otp_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per Monnify checkout (card / one-off transfer) started from the customer portal
CREATE TABLE payment_intents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_reference VARCHAR(64) NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  status ENUM('pending', 'paid', 'failed', 'expired') NOT NULL DEFAULT 'pending',
  checkout_url VARCHAR(500) NULL,
  transaction_reference VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_intents_reference (payment_reference),
  KEY ix_intents_customer (customer_id),
  CONSTRAINT fk_intents_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit log of every webhook Monnify sends us
CREATE TABLE monnify_events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(60) NULL,
  transaction_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_events_txn (transaction_reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;