-- ============================================
-- UPI Automatic Deposit System - Migration
-- Run this SQL on your binary_options database
-- ============================================

-- 1. Add auto-verification columns to upi_payments
ALTER TABLE `upi_payments`
  ADD COLUMN IF NOT EXISTS `auto_verified` TINYINT(1) DEFAULT 0 AFTER `confirmed_by`,
  ADD COLUMN IF NOT EXISTS `verification_notes` TEXT DEFAULT NULL AFTER `auto_verified`,
  ADD COLUMN IF NOT EXISTS `ip_address` VARCHAR(45) DEFAULT NULL AFTER `verification_notes`,
  ADD COLUMN IF NOT EXISTS `device_info` VARCHAR(500) DEFAULT NULL AFTER `ip_address`;

-- 2. Add auto-verification columns to deposits
ALTER TABLE `deposits`
  ADD COLUMN IF NOT EXISTS `utr_number` VARCHAR(50) DEFAULT NULL AFTER `transaction_id`,
  ADD COLUMN IF NOT EXISTS `auto_verified` TINYINT(1) DEFAULT 0 AFTER `processed_at`,
  ADD COLUMN IF NOT EXISTS `device_info` VARCHAR(500) DEFAULT NULL AFTER `ip_address`;

-- 3. Create admin_logs table
CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `admin_id` INT(11) NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `target_type` VARCHAR(50) DEFAULT NULL COMMENT 'deposit, withdrawal, user, setting',
  `target_id` INT(11) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `old_value` TEXT DEFAULT NULL,
  `new_value` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  PRIMARY KEY (`id`),
  KEY `admin_id` (`admin_id`),
  KEY `action` (`action`),
  KEY `target_type` (`target_type`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 4. Create api_tokens table for mobile app auth
CREATE TABLE IF NOT EXISTS `api_tokens` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `token` VARCHAR(128) NOT NULL,
  `device_name` VARCHAR(100) DEFAULT NULL,
  `device_id` VARCHAR(255) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `expires_at` DATETIME NOT NULL,
  `last_used_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`),
  KEY `expires_at` (`expires_at`),
  CONSTRAINT `api_token_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 5. Add index for faster UTR duplicate checks
ALTER TABLE `deposits` ADD INDEX IF NOT EXISTS `idx_utr_number` (`utr_number`);
ALTER TABLE `upi_payments` ADD INDEX IF NOT EXISTS `idx_auto_verified` (`auto_verified`);
