<?php
/**
 * UPI Payment Database Migration
 * Creates all necessary tables for UPI payment processing
 */

session_start();

$host = 'localhost';
$db = 'zemaraim_interior';
$user = 'zemaraim';
$password = '8wwxk]-X3Kx]CJ]5';

$conn = new mysqli($host, $user, $password, $db);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "=== UPI Payment Database Migration ===\n\n";

// Create UPI Transactions Table
echo "Creating upi_transactions table...\n";
$sql1 = "
CREATE TABLE IF NOT EXISTS upi_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_ref VARCHAR(100) UNIQUE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    upi_string LONGTEXT NOT NULL,
    qr_code_url LONGTEXT,
    upi_id VARCHAR(100),
    status ENUM('pending', 'verified', 'completed', 'failed', 'expired') DEFAULT 'pending',
    order_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_transaction_ref (transaction_ref),
    INDEX idx_status (status),
    INDEX idx_order_id (order_id)
)
";

if ($conn->query($sql1) === TRUE) {
    echo "✅ upi_transactions table created/exists\n";
} else {
    echo "❌ Error creating upi_transactions: " . $conn->error . "\n";
}

// Create UPI Configuration Table
echo "Creating upi_config table...\n";
$sql2 = "
CREATE TABLE IF NOT EXISTS upi_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `key` VARCHAR(100) UNIQUE NOT NULL,
    value VARCHAR(500) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
";

if ($conn->query($sql2) === TRUE) {
    echo "✅ upi_config table created/exists\n";
} else {
    echo "❌ Error creating upi_config: " . $conn->error . "\n";
}

// Insert default UPI configuration
echo "Inserting UPI configuration...\n";

$configs = [
    ['UPI_ID', '9876543210@okhdfcbank', 'Merchant UPI ID'],
    ['MERCHANT_NAME', 'Interior Design Store', 'Merchant Name'],
    ['QR_CODE_SIZE', '250x250', 'QR Code Size'],
    ['TRANSACTION_TIMEOUT', '600', 'Transaction Timeout in seconds'],
    ['ENABLE_UPI', 'true', 'Enable UPI Payment']
];

foreach ($configs as $config) {
    $key = $config[0];
    $value = $config[1];
    $desc = $config[2];
    
    // Check if config already exists
    $check = $conn->prepare("SELECT id FROM upi_config WHERE `key` = ?");
    $check->bind_param("s", $key);
    $check->execute();
    $result = $check->get_result();
    $exists = $result->num_rows > 0;
    $check->close();
    
    if (!$exists) {
        $stmt = $conn->prepare("INSERT INTO upi_config (`key`, value, description) VALUES (?, ?, ?)");
        $stmt->bind_param("sss", $key, $value, $desc);
        
        if ($stmt->execute()) {
            echo "  ✅ Added config: $key\n";
        } else {
            echo "  ❌ Failed to add config $key: " . $stmt->error . "\n";
        }
        $stmt->close();
    } else {
        echo "  ⚠️  Config already exists: $key\n";
    }
}

// Create Payment Logs Table
echo "\nCreating upi_payment_logs table...\n";
$sql3 = "
CREATE TABLE IF NOT EXISTS upi_payment_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    action VARCHAR(50),
    status VARCHAR(50),
    message TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES upi_transactions(id) ON DELETE CASCADE,
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_created_at (created_at)
)
";

if ($conn->query($sql3) === TRUE) {
    echo "✅ upi_payment_logs table created/exists\n";
} else {
    echo "❌ Error creating upi_payment_logs: " . $conn->error . "\n";
}

echo "\n=== Migration Complete ===\n\n";

// Display summary
echo "Summary:\n";
echo "- upi_transactions table: Stores all UPI transaction details\n";
echo "- upi_config table: Stores UPI configuration settings\n";
echo "- upi_payment_logs table: Stores payment transaction logs\n\n";

echo "Next Steps:\n";
echo "1. Update UPI_ID in upi_config table with your actual UPI ID\n";
echo "2. Test UPI payment by selecting UPI in checkout\n";
echo "3. Scan QR code or enter UPI ID\n";
echo "4. Click 'Confirm Payment Done' to complete\n\n";

$conn->close();

echo "✅ All tables created successfully!\n";
?>
