SET NAMES utf8mb4;
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  role ENUM('admin','subadmin','master','distributor','retailer','api') NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  business_name VARCHAR(120) NULL,
  name VARCHAR(100) NOT NULL,
  mobile VARCHAR(15) NOT NULL,
  email VARCHAR(120) NOT NULL,
  whatsapp VARCHAR(15) NULL,
  password_hash VARCHAR(255) NOT NULL,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('new','active','blocked') NOT NULL DEFAULT 'new',
  permissions TEXT NULL,
  api_token CHAR(40) NULL, ip_whitelist VARCHAR(255) NULL,
  email_verified TINYINT(1) DEFAULT 0, mobile_verified TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(role), INDEX(parent_id), INDEX(mobile)
) ENGINE=InnoDB;
CREATE TABLE otp_codes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL, purpose VARCHAR(20) NOT NULL,
  code_hash CHAR(64) NOT NULL, attempts TINYINT DEFAULT 0,
  expires_at DATETIME NOT NULL, used TINYINT(1) DEFAULT 0,
  INDEX(user_id,purpose)
) ENGINE=InnoDB;
CREATE TABLE login_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY, ip VARCHAR(45), username VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX(ip,created_at)
) ENGINE=InnoDB;
CREATE TABLE operators (
  id INT AUTO_INCREMENT PRIMARY KEY, service VARCHAR(30) NOT NULL, name VARCHAR(60) NOT NULL,
  code VARCHAR(20) NOT NULL, commission DECIMAL(5,2) DEFAULT 0, active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;
CREATE TABLE api_providers (
  id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60), url VARCHAR(255),
  api_key VARCHAR(255), balance DECIMAL(14,2) DEFAULT 0, priority INT DEFAULT 1, active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;
CREATE TABLE recharges (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, txn_id VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL, operator_id INT NOT NULL, provider_id INT NULL,
  number VARCHAR(30) NOT NULL, amount DECIMAL(12,2) NOT NULL, commission DECIMAL(10,2) DEFAULT 0,
  status ENUM('pending','success','failed','refund') NOT NULL DEFAULT 'pending',
  provider_ref VARCHAR(60) NULL, message VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL,
  INDEX(user_id), INDEX(status), INDEX(created_at)
) ENGINE=InnoDB;
CREATE TABLE wallet_ledger (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL,
  type ENUM('credit','debit') NOT NULL, amount DECIMAL(14,2) NOT NULL,
  balance_after DECIMAL(14,2) NOT NULL, ref VARCHAR(40), remark VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX(user_id)
) ENGINE=InnoDB;
CREATE TABLE money_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL, method VARCHAR(30), utr VARCHAR(60), proof VARCHAR(255),
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  handled_by INT UNSIGNED NULL, remark VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX(status)
) ENGINE=InnoDB;
CREATE TABLE fund_transfers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, from_user INT UNSIGNED, to_user INT UNSIGNED,
  amount DECIMAL(14,2), remark VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE complaints (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED, recharge_id BIGINT UNSIGNED NULL,
  subject VARCHAR(150), message TEXT, status ENUM('open','resolved','rejected') DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE notices (
  id INT AUTO_INCREMENT PRIMARY KEY, kind ENUM('notice','notification') DEFAULT 'notice',
  title VARCHAR(150), body TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE settings (
  k VARCHAR(60) PRIMARY KEY, v TEXT, grp VARCHAR(30) NOT NULL DEFAULT 'general'
) ENGINE=InnoDB;
CREATE TABLE routes (
  id INT AUTO_INCREMENT PRIMARY KEY, kind VARCHAR(20), operator_id INT NULL, user_id INT NULL,
  provider_id INT NOT NULL, min_amount DECIMAL(10,2) NULL, max_amount DECIMAL(10,2) NULL,
  priority INT DEFAULT 1, active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;
INSERT INTO settings (k,v,grp) VALUES
('site_name','ADAPT WIND RECHARGE','website'),
('otp_email','1','otp'),('otp_sms','1','otp'),('otp_whatsapp','0','otp'),
('smtp_from','no-reply@example.com','email'),
('sms_gateway_url','https://sms.example.com/send?to={mobile}&msg={message}','sms'),
('whatsapp_gateway_url','https://wa.example.com/send?to={mobile}&msg={message}','whatsapp'),
('upi_id','','upi'),('upi_name','','upi');
