-- USDT Payment System Database Schema
-- Based on TetherU Payment Gateway API

-- USDT Orders Table (for deposits)
CREATE TABLE IF NOT EXISTS usdt_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id VARCHAR(100) UNIQUE NOT NULL,
    payment_id VARCHAR(100),
    user_id INT NOT NULL,
    type ENUM('deposit', 'withdrawal') DEFAULT 'deposit',
    amount DECIMAL(15,6) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USDT',
    network VARCHAR(10) DEFAULT 'TRC20',
    status ENUM('pending', 'confirmed', 'failed', 'cancelled', 'expired') DEFAULT 'pending',
    payment_address VARCHAR(100),
    qr_code TEXT,
    transaction_hash VARCHAR(100),
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_payment_id (payment_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- USDT Withdrawals Table
CREATE TABLE IF NOT EXISTS usdt_withdrawals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    withdrawal_id VARCHAR(100) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(15,6) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USDT',
    network VARCHAR(10) DEFAULT 'TRC20',
    address VARCHAR(100) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    transaction_hash VARCHAR(100),
    admin_notes TEXT,
    processed_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_withdrawal_id (withdrawal_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- USDT Transactions Log Table
CREATE TABLE IF NOT EXISTS usdt_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id VARCHAR(100) UNIQUE NOT NULL,
    user_id INT,
    type ENUM('deposit', 'withdrawal', 'refund') NOT NULL,
    amount DECIMAL(15,6) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USDT',
    network VARCHAR(10) DEFAULT 'TRC20',
    status ENUM('pending', 'confirmed', 'failed', 'cancelled') DEFAULT 'pending',
    transaction_hash VARCHAR(100),
    from_address VARCHAR(100),
    to_address VARCHAR(100),
    block_number BIGINT,
    gas_used BIGINT,
    gas_price DECIMAL(20,8),
    fee DECIMAL(15,6),
    confirmation_count INT DEFAULT 0,
    required_confirmations INT DEFAULT 3,
    webhook_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_user_id (user_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_transaction_hash (transaction_hash),
    INDEX idx_created_at (created_at)
);

-- USDT Payment Settings Table
CREATE TABLE IF NOT EXISTS usdt_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default USDT settings
INSERT INTO usdt_settings (setting_key, setting_value, description) VALUES
('min_deposit', '10', 'Minimum deposit amount in USDT'),
('max_deposit', '10000', 'Maximum deposit amount in USDT'),
('min_withdrawal', '5', 'Minimum withdrawal amount in USDT'),
('max_withdrawal', '5000', 'Maximum withdrawal amount in USDT'),
('withdrawal_fee', '1', 'Withdrawal fee in USDT'),
('confirmation_required', '3', 'Number of blockchain confirmations required'),
('auto_approve_withdrawals', '0', 'Auto approve withdrawals (0=manual, 1=auto)'),
('supported_networks', 'TRC20,ERC20,BEP20', 'Supported USDT networks'),
('default_network', 'TRC20', 'Default USDT network'),
('api_enabled', '1', 'USDT API enabled (0=disabled, 1=enabled)')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- Add USDT balance columns to users table if they don't exist
-- Note: IF NOT EXISTS is not supported in all MySQL versions for ALTER TABLE
-- We'll check and add columns manually

-- Create indexes for better performance (only if columns exist)
-- CREATE INDEX IF NOT EXISTS idx_users_usdt_balance ON users(usdt_balance);
-- CREATE INDEX IF NOT EXISTS idx_users_usdt_address ON users(usdt_address);