-- =============================================
-- PAYMENT BOT - COMPLETE DATABASE SETUP
-- Database: beeyounge_hyperyolo
-- Domain: bot.beeyounge.store
-- Created: <?php echo date('Y-m-d H:i:s'); ?>
-- =============================================

-- 1. CREATE DATABASE
DROP DATABASE IF EXISTS beeyounge_hyperyolo;
CREATE DATABASE beeyounge_hyperyolo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE beeyounge_hyperyolo;

-- 2. CREATE ALL TABLES

-- Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    balance DECIMAL(15,2) DEFAULT 0.00,
    phone_number VARCHAR(20),
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_username (username),
    INDEX idx_created_at (created_at)
);

-- Transactions Table
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    type ENUM('deposit', 'withdrawal') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    transaction_id VARCHAR(255) UNIQUE NOT NULL,
    payment_method VARCHAR(50),
    upi_id VARCHAR(255),
    bank_details TEXT,
    admin_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(telegram_id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_transaction_id (transaction_id)
);

-- Payment Requests Table
CREATE TABLE payment_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    type ENUM('deposit', 'withdrawal') NOT NULL,
    upi_id VARCHAR(255),
    payment_method ENUM('UPI', 'Bank Transfer', 'QR Code') DEFAULT 'UPI',
    bank_details TEXT,
    qr_code_url VARCHAR(500),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_notes TEXT,
    approved_by INT,
    approved_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(telegram_id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Admin Settings Table
CREATE TABLE admin_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    upi_id VARCHAR(255) NOT NULL,
    qr_code_url VARCHAR(500),
    min_deposit DECIMAL(10,2) DEFAULT 10.00,
    max_deposit DECIMAL(10,2) DEFAULT 10000.00,
    min_withdrawal DECIMAL(10,2) DEFAULT 50.00,
    max_withdrawal DECIMAL(10,2) DEFAULT 5000.00,
    company_name VARCHAR(255) DEFAULT 'Payment Bot',
    support_contact VARCHAR(255),
    automatic_approval BOOLEAN DEFAULT FALSE,
    maintenance_mode BOOLEAN DEFAULT FALSE,
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Admin Login Logs Table
CREATE TABLE admin_login_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    success BOOLEAN DEFAULT TRUE,
    failure_reason VARCHAR(255),
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id),
    INDEX idx_login_time (login_time),
    INDEX idx_success (success)
);

-- Admin Activity Logs Table
CREATE TABLE admin_activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    activity_type VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at)
);

-- System Logs Table
CREATE TABLE system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_level ENUM('info', 'warning', 'error', 'debug') DEFAULT 'info',
    message TEXT NOT NULL,
    context TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_log_level (log_level),
    INDEX idx_created_at (created_at)
);

-- Default Admin Settings
INSERT INTO admin_settings (upi_id, min_deposit, max_deposit, min_withdrawal, max_withdrawal, company_name, support_contact) VALUES 
('your_upi@paytm', 10.00, 10000.00, 50.00, 5000.00, 'Payment Bot', 'support@bot.beeyounge.store');

-- Sample Users
INSERT INTO users (telegram_id, username, first_name, last_name, balance) VALUES 
(123456789, 'test_user', 'Test', 'User', 1000.00),
(987654321, 'demo_user', 'Demo', 'User', 500.00),
(111111111, 'john_doe', 'John', 'Doe', 2500.00),
(222222222, 'jane_smith', 'Jane', 'Smith', 1800.50);

-- Sample Transactions
INSERT INTO transactions (user_id, type, amount, status, transaction_id, payment_method) VALUES 
(123456789, 'deposit', 1000.00, 'completed', 'DEP_001', 'UPI'),
(123456789, 'withdrawal', 500.00, 'completed', 'WD_001', 'Bank Transfer'),
(987654321, 'deposit', 500.00, 'completed', 'DEP_002', 'UPI'),
(111111111, 'deposit', 2000.00, 'completed', 'DEP_003', 'QR Code');

-- Sample Payment Requests
INSERT INTO payment_requests (user_id, amount, type, payment_method, status) VALUES 
(123456789, 1500.00, 'deposit', 'UPI', 'pending'),
(987654321, 300.00, 'withdrawal', 'Bank Transfer', 'pending'),
(111111111, 1000.00, 'deposit', 'QR Code', 'approved');

-- 4. STORED PROCEDURES

DELIMITER $$

-- Approve Payment Request
CREATE PROCEDURE ApprovePaymentRequest(
    IN p_request_id INT,
    IN p_admin_id INT,
    IN p_notes TEXT
)
BEGIN
    DECLARE v_user_id BIGINT;
    DECLARE v_amount DECIMAL(15,2);
    DECLARE v_type ENUM('deposit', 'withdrawal');
    DECLARE v_payment_method VARCHAR(50);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    SELECT user_id, amount, type, payment_method INTO v_user_id, v_amount, v_type, v_payment_method
    FROM payment_requests WHERE id = p_request_id AND status = 'pending';
    
    IF v_user_id IS NOT NULL THEN
        IF v_type = 'deposit' THEN
            UPDATE users SET balance = balance + v_amount WHERE telegram_id = v_user_id;
            INSERT INTO transactions (user_id, type, amount, status, transaction_id, payment_method)
            VALUES (v_user_id, 'deposit', v_amount, 'completed', CONCAT('DEP_', UUID()), v_payment_method);
        ELSE
            INSERT INTO transactions (user_id, type, amount, status, transaction_id, payment_method)
            VALUES (v_user_id, 'withdrawal', v_amount, 'completed', CONCAT('WD_', UUID()), v_payment_method);
        END IF;
        
        UPDATE payment_requests SET status = 'approved', approved_by = p_admin_id, approved_at = NOW(), admin_notes = p_notes
        WHERE id = p_request_id;
        
        INSERT INTO admin_activity_logs (admin_id, activity_type, description, ip_address)
        VALUES (p_admin_id, 'payment_approved', CONCAT('Approved ', v_type, ' of ₹', v_amount, ' for user ', v_user_id), '127.0.0.1');
        
        SELECT 'Payment approved successfully' as result;
    ELSE
        SELECT 'Error: Payment request not found' as result;
    END IF;
    
    COMMIT;
END$$

-- Reject Payment Request
CREATE PROCEDURE RejectPaymentRequest(
    IN p_request_id INT,
    IN p_admin_id INT,
    IN p_notes TEXT
)
BEGIN
    DECLARE v_user_id BIGINT;
    DECLARE v_amount DECIMAL(15,2);
    DECLARE v_type ENUM('deposit', 'withdrawal');
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    SELECT user_id, amount, type INTO v_user_id, v_amount, v_type
    FROM payment_requests WHERE id = p_request_id AND status = 'pending';
    
    IF v_user_id IS NOT NULL THEN
        IF v_type = 'withdrawal' THEN
            UPDATE users SET balance = balance + v_amount WHERE telegram_id = v_user_id;
        END IF;
        
        INSERT INTO transactions (user_id, type, amount, status, transaction_id, payment_method)
        VALUES (v_user_id, v_type, v_amount, 'failed', CONCAT('FAIL_', UUID()), 'Rejected');
        
        UPDATE payment_requests SET status = 'rejected', approved_by = p_admin_id, approved_at = NOW(), admin_notes = p_notes
        WHERE id = p_request_id;
        
        INSERT INTO admin_activity_logs (admin_id, activity_type, description, ip_address)
        VALUES (p_admin_id, 'payment_rejected', CONCAT('Rejected ', v_type, ' of ₹', v_amount, ' for user ', v_user_id), '127.0.0.1');
        
        SELECT 'Payment rejected successfully' as result;
    ELSE
        SELECT 'Error: Payment request not found' as result;
    END IF;
    
    COMMIT;
END$$
END$$

-- Get User Report
CREATE PROCEDURE GetUserReport(IN p_user_id BIGINT)
BEGIN
    SELECT telegram_id, username, CONCAT(first_name, ' ', COALESCE(last_name, '')) as full_name, balance, created_at
    FROM users WHERE telegram_id = p_user_id;
    
    SELECT transaction_id, type, amount, status, payment_method, created_at
    FROM transactions WHERE user_id = p_user_id ORDER BY created_at DESC LIMIT 10;
    
    SELECT id, amount, type, payment_method, status, created_at
    FROM payment_requests WHERE user_id = p_user_id ORDER BY created_at DESC LIMIT 10;
END$$

DELIMITER ;

-- User Balance Report
SELECT 
    u.telegram_id,
    u.username,
    u.first_name,
    u.balance,
    COUNT(t.id) as total_txns,
    MAX(t.created_at) as last_activity
FROM users u
LEFT JOIN transactions t ON u.telegram_id = t.user_id
GROUP BY u.telegram_id
ORDER BY u.balance DESC;

-- Pending Requests Report
SELECT 
    pr.id,
    u.username,
    pr.amount,
    pr.type,
    pr.payment_method,
    pr.created_at
FROM payment_requests pr
JOIN users u ON pr.user_id = u.telegram_id
WHERE pr.status = 'pending'
ORDER BY pr.created_at ASC;

-- 6. FINAL VERIFICATION

SELECT '=== DATABASE SETUP COMPLETE ===' as status;

SHOW TABLES;

CALL GetDashboardStats();

SELECT '=== SETUP FINISHED SUCCESSFULLY ===' as final_status;