-- PayKlip Database Schema (MySQL)

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  phone VARCHAR(20) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  wallet_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  role ENUM('admin','user') NOT NULL DEFAULT 'user',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  api_key VARCHAR(64) NOT NULL UNIQUE,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS operators (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  code VARCHAR(20) NOT NULL UNIQUE,
  active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS meter_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS electricity_providers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(20) NOT NULL UNIQUE,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS banks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(20) NOT NULL UNIQUE,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS data_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  operator_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  data_amount VARCHAR(40) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('airtime','data','electricity','wallet_topup','wallet_withdraw') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
  reference VARCHAR(120) NOT NULL UNIQUE,
  payment_method ENUM('Wallet','Card') NOT NULL,
  network VARCHAR(20) NULL,
  phone_number VARCHAR(20) NULL,
  data_plan VARCHAR(40) NULL,
  disco VARCHAR(120) NULL,
  meter_type ENUM('Prepaid','Postpaid') NULL,
  meter_number VARCHAR(20) NULL,
  token VARCHAR(32) NULL,
  bank_account VARCHAR(20) NULL,
  payment_reference VARCHAR(120) NULL,
  meta JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Seed with basic operators and meter types
INSERT INTO operators (name, code, active) VALUES
('MTN','MTN',1),('Glo','GLO',1),('Airtel','AIRTEL',1),('9mobile','9MOBILE',1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO meter_types (name, description, active) VALUES
('Prepaid','Prepaid electricity meter',1),('Postpaid','Postpaid electricity meter',1)
ON DUPLICATE KEY UPDATE description=VALUES(description);

-- Seed electricity providers (discos)
INSERT INTO electricity_providers (name, code, active) VALUES
('Ikeja Electric', 'IKEDC', 1),
('Eko Electric', 'EKEDC', 1),
('Abuja Electric', 'AEDC', 1),
('Ibadan Electric', 'IBEDC', 1),
('Enugu Electric', 'EEDC', 1),
('Port Harcourt Electric', 'PHEDC', 1),
('Kano Electric', 'KEDCO', 1),
('Kaduna Electric', 'KAEDCO', 1),
('Jos Electric', 'JED', 1),
('Benin Electric', 'BEDC', 1),
('Owerri Electric', 'OEDC', 1),
('Yola Electric', 'YEDC', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name);