-- Bonza MLM - MySQL schema
-- Run once: mysql -u root -p bonza < schema.sql  (create DB first: CREATE DATABASE bonza;)

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `number` VARCHAR(32) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `date_of_birth` DATE NULL,
  `referral_id` VARCHAR(16) NOT NULL,
  `referred_by` INT UNSIGNED NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `role` VARCHAR(20) NOT NULL DEFAULT 'user',
  `wallet_balance` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `min_withdraw_balance` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `profile_image` LONGTEXT NULL,
  `kyc_status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `kyc_aadhar_number` VARCHAR(32) NULL,
  `kyc_aadhar_image` LONGTEXT NULL,
  `kyc_pan_number` VARCHAR(32) NULL,
  `kyc_pan_image` LONGTEXT NULL,
  `kyc_bank_account_number` VARCHAR(64) NULL,
  `kyc_bank_ifsc` VARCHAR(32) NULL,
  `kyc_bank_name` VARCHAR(255) NULL,
  `kyc_account_holder_name` VARCHAR(255) NULL,
  `kyc_submitted_at` DATETIME NULL,
  `registration_transaction_id` VARCHAR(255) NULL,
  `registration_payment_screenshot` LONGTEXT NULL,
  `registration_submitted_at` DATETIME NULL,
  `approved_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `number` (`number`),
  UNIQUE KEY `referral_id` (`referral_id`),
  KEY `referred_by` (`referred_by`),
  KEY `status` (`status`),
  KEY `role` (`role`),
  KEY `kyc_status` (`kyc_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `qr_code` LONGTEXT NULL,
  `bank_name` VARCHAR(255) NULL,
  `bank_account_number` VARCHAR(64) NULL,
  `bank_ifsc` VARCHAR(32) NULL,
  `account_holder_name` VARCHAR(255) NULL,
  `min_withdraw_balance` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`id`, `min_withdraw_balance`) VALUES (1, 0) ON DUPLICATE KEY UPDATE `id`=`id`;

CREATE TABLE IF NOT EXISTS `wallet_transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `type` VARCHAR(32) NOT NULL,
  `amount` DECIMAL(14,2) NOT NULL,
  `balance_after` DECIMAL(14,2) NULL,
  `description` VARCHAR(255) NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'completed',
  `processed_at` DATETIME NULL,
  `from_user_id` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `type_status` (`type`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoices` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(14,2) NOT NULL,
  `type` VARCHAR(32) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
