-- ============================================================
-- Reward Platform :: Wallet + Payout Module
-- Database: MySQL 8+ / MariaDB 10.4+
-- Charset: utf8mb4
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- Minimal users table (assumes the full platform has a richer
-- version of this; only the columns this module depends on
-- are declared here so this file can run standalone).
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `email` VARCHAR(190) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('super_admin','admin','editor','moderator','support','finance','marketing','author','premium_member','user') NOT NULL DEFAULT 'user',
  `kyc_status` ENUM('not_submitted','pending','verified','rejected') NOT NULL DEFAULT 'not_submitted',
  `status` ENUM('active','suspended','banned') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Wallets: one row per user, three logical balances
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallets` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `reward_balance` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `cash_balance` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `pending_balance` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_wallet_user` (`user_id`),
  CONSTRAINT `fk_wallet_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Wallet transaction ledger (append-only, source of truth)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallet_transactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `wallet_type` ENUM('reward','cash','pending') NOT NULL,
  `direction` ENUM('credit','debit') NOT NULL,
  `amount` DECIMAL(18,4) NOT NULL,
  `balance_after` DECIMAL(18,4) NOT NULL,
  `source` VARCHAR(60) NOT NULL COMMENT 'e.g. daily_login, blog_approved, withdrawal, conversion',
  `reference_type` VARCHAR(60) NULL COMMENT 'e.g. withdrawal, order, post',
  `reference_id` BIGINT UNSIGNED NULL,
  `description` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_wt_user` (`user_id`),
  KEY `idx_wt_source` (`source`),
  CONSTRAINT `fk_wt_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Payout methods (admin configurable, no source-code edits)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payout_methods` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` ENUM('bank_transfer','upi','usdt_trc20','usdt_bep20','usdt_erc20') NOT NULL UNIQUE,
  `display_name` VARCHAR(100) NOT NULL,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `min_amount` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `max_amount` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `fee_type` ENUM('fixed','percent') NOT NULL DEFAULT 'fixed',
  `fee_value` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `daily_limit` DECIMAL(18,4) NULL,
  `weekly_limit` DECIMAL(18,4) NULL,
  `monthly_limit` DECIMAL(18,4) NULL,
  `auto_approve` TINYINT(1) NOT NULL DEFAULT 0,
  `requires_kyc` TINYINT(1) NOT NULL DEFAULT 0,
  `api_provider` VARCHAR(100) NULL,
  `api_url` VARCHAR(255) NULL,
  `api_key` VARCHAR(255) NULL,
  `api_secret` VARCHAR(255) NULL,
  `extra_config` JSON NULL COMMENT 'method-specific settings, e.g. required fields',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Saved payout destinations per user (bank / UPI / USDT address)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_payout_accounts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `method_id` INT UNSIGNED NOT NULL,
  `label` VARCHAR(100) NULL,
  `account_details` JSON NOT NULL COMMENT 'bank: {account_no,ifsc,holder,bank_name}; upi: {vpa}; usdt: {address,network}',
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_upa_user` (`user_id`),
  CONSTRAINT `fk_upa_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_upa_method` FOREIGN KEY (`method_id`) REFERENCES `payout_methods`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Withdrawal requests
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `method_id` INT UNSIGNED NOT NULL,
  `payout_account_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(18,4) NOT NULL,
  `fee` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `net_amount` DECIMAL(18,4) NOT NULL,
  `status` ENUM('pending','processing','completed','rejected','failed') NOT NULL DEFAULT 'pending',
  `admin_notes` VARCHAR(500) NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_at` DATETIME NULL,
  `processed_by` INT UNSIGNED NULL,
  KEY `idx_wd_user` (`user_id`),
  KEY `idx_wd_status` (`status`),
  CONSTRAINT `fk_wd_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wd_method` FOREIGN KEY (`method_id`) REFERENCES `payout_methods`(`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_wd_account` FOREIGN KEY (`payout_account_id`) REFERENCES `user_payout_accounts`(`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_wd_admin` FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Payout audit log (every status transition, manual or API)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payout_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `withdrawal_id` BIGINT UNSIGNED NOT NULL,
  `action` VARCHAR(60) NOT NULL COMMENT 'requested, approved, rejected, processing, completed, failed, note',
  `actor_type` ENUM('system','admin','api') NOT NULL DEFAULT 'system',
  `actor_id` INT UNSIGNED NULL,
  `message` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_pl_withdrawal` (`withdrawal_id`),
  CONSTRAINT `fk_pl_withdrawal` FOREIGN KEY (`withdrawal_id`) REFERENCES `withdrawals`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Optional KYC documents (only relevant if a method requires_kyc)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `kyc_documents` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `doc_type` VARCHAR(60) NOT NULL,
  `doc_path` VARCHAR(255) NOT NULL,
  `status` ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` INT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_kyc_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- In-app notifications (used by payout status changes)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `type` VARCHAR(60) NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `message` VARCHAR(500) NOT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_notif_user` (`user_id`),
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Seed default payout methods (disabled by default; admin
-- turns them on and fills in limits/fees from the panel)
-- ------------------------------------------------------------
INSERT INTO `payout_methods` (`code`,`display_name`,`is_enabled`,`min_amount`,`max_amount`,`fee_type`,`fee_value`,`daily_limit`,`weekly_limit`,`monthly_limit`,`auto_approve`,`requires_kyc`,`extra_config`)
VALUES
('bank_transfer','Bank Transfer',0,500.0000,50000.0000,'fixed',10.0000,50000,150000,500000,0,1,
  JSON_OBJECT('fields', JSON_ARRAY('account_no','ifsc','holder','bank_name'))),
('upi','UPI',0,100.0000,25000.0000,'percent',1.0000,25000,100000,300000,0,0,
  JSON_OBJECT('fields', JSON_ARRAY('vpa'))),
('usdt_trc20','USDT (TRC20)',0,10.0000,5000.0000,'fixed',1.0000,5000,20000,60000,0,1,
  JSON_OBJECT('fields', JSON_ARRAY('address'), 'network','TRC20')),
('usdt_bep20','USDT (BEP20)',0,10.0000,5000.0000,'fixed',0.5000,5000,20000,60000,0,1,
  JSON_OBJECT('fields', JSON_ARRAY('address'), 'network','BEP20')),
('usdt_erc20','USDT (ERC20)',0,20.0000,5000.0000,'fixed',5.0000,5000,20000,60000,0,1,
  JSON_OBJECT('fields', JSON_ARRAY('address'), 'network','ERC20'))
ON DUPLICATE KEY UPDATE `display_name` = VALUES(`display_name`);

SET FOREIGN_KEY_CHECKS = 1;