-- REDRAY HOLDINGS database schema (MySQL 8+)
-- Run with: mysql -u root -p < schema.sql

CREATE DATABASE IF NOT EXISTS redray_holdings CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE redray_holdings;

-- ---------------------------------------------------------------------------
-- Site-wide settings (single row, id = 1)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
  id TINYINT PRIMARY KEY DEFAULT 1,
  admin_password_hash VARCHAR(255) NOT NULL,
  agent_fee DECIMAL(10,2) NOT NULL DEFAULT 20.00,
  data_api_endpoint VARCHAR(500) NOT NULL DEFAULT 'https://console.ckgodsway.com/dashboard/submit-numbers',
  CONSTRAINT single_row CHECK (id = 1)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Data bundle packages, per network
-- `price`       = what a normal (non-agent) customer pays.
-- `agent_price` = what YOU set as the agent cost/floor for this package —
--                 agents can never sell below this, and it's the exact
--                 number subtracted from their chosen selling price to work
--                 out their profit. Set independently from `price`; it is
--                 NOT a percentage of it.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS packages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  network ENUM('MTN','TELECEL','AIRTELTIGO') NOT NULL,
  size VARCHAR(50) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  agent_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  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,
  INDEX idx_network (network)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Agents (resellers)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS agents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) NOT NULL UNIQUE,
  store_name VARCHAR(120) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(20) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  wallet DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  active TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_slug (slug)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Agent's own selling price per package (defaults to that package's
-- agent_price — the floor — at signup, so they start at zero profit and
-- raise it themselves)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS agent_prices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  agent_id INT NOT NULL,
  package_id INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  UNIQUE KEY uniq_agent_package (agent_id, package_id),
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Transactions: data purchases AND agent activation payments
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(100) NOT NULL UNIQUE,
  type ENUM('purchase','agent_registration') NOT NULL,
  network ENUM('MTN','TELECEL','AIRTELTIGO') NULL,
  package_id INT NULL,
  package_size VARCHAR(50) NULL,
  amount DECIMAL(10,2) NOT NULL,
  beneficiary_number VARCHAR(20) NULL,
  payment_number VARCHAR(20) NULL,
  customer_email VARCHAR(190) NULL,
  status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
  channel ENUM('direct','agent') NOT NULL DEFAULT 'direct',
  agent_id INT NULL,
  agent_profit DECIMAL(10,2) NULL,
  -- delivery_locked: a short-lived flag set while a delivery/retry request
  -- is actually in flight to ckgodsway, so two admin clicks (or a slow
  -- double-click) can never both submit a purchase for the same order.
  api_ok TINYINT(1) NULL,
  api_message TEXT NULL,
  api_raw_response JSON NULL,
  delivery_locked TINYINT(1) NOT NULL DEFAULT 0,
  -- delivery_status mirrors ckgodsway's own order-status wording exactly:
  -- INITIATED / PENDING / PROCESSING / SUCCESSFUL / FAILED / CANCELLED.
  -- REJECTED is our own value for when ckgodsway wouldn't even accept the
  -- purchase request in the first place (e.g. insufficient balance) — no
  -- order ever existed on their side, so it's distinct from their FAILED.
  delivery_status ENUM('REJECTED','INITIATED','PENDING','PROCESSING','SUCCESSFUL','FAILED','CANCELLED') NULL,
  paystack_raw JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_agent (agent_id),
  INDEX idx_reference (reference)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Withdrawal requests raised by agents against their wallet
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS withdrawals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  agent_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('pending','paid','rejected') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Login sessions for admin (single implicit account) and agents
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
  token VARCHAR(64) PRIMARY KEY,
  role ENUM('admin','agent') NOT NULL,
  agent_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seed data: default settings + starter packages
-- The `settings` row (including a hashed default admin password of "admin123")
-- is created automatically by the app on first boot — see server/index.js ->
-- ensureSeed(). Do NOT insert a settings row here; the app checks "does a
-- settings row exist yet" to decide whether to seed it.

INSERT IGNORE INTO packages (network, size, price, agent_price) VALUES
  ('MTN','1GB',4.50,4.00), ('MTN','2GB',9.00,8.00), ('MTN','3GB',13.50,12.00), ('MTN','5GB',20.50,18.50), ('MTN','10GB',38.00,34.50),
  ('TELECEL','1GB',5.00,4.50), ('TELECEL','2GB',9.50,8.50), ('TELECEL','5GB',21.50,19.50), ('TELECEL','10GB',40.00,36.50),
  ('AIRTELTIGO','1GB',4.50,4.00), ('AIRTELTIGO','2GB',8.50,7.50), ('AIRTELTIGO','5GB',19.50,17.50), ('AIRTELTIGO','10GB',37.00,33.50);