-- ============================================================
-- Reward Platform :: Wallet + Payout Module
-- Database: MySQL 8+ / MariaDB 10.4+
-- 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;

-- ------------------------------------------------------------
-- 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;

