<?php
/**
 * PhonePe Database Tables Checker
 * This script checks if PhonePe tables exist and shows what's missing
 * 
 * Usage: Run this script in browser or command line
 * URL: https://astro.siddharthnahar.in/astro_admin/check_phonepe_tables.php
 */

// Database configuration
$host = 'localhost';
$dbname = 'sidd_astro';
$username = 'sidd_astro'; // Change this to your DB username
$password = 'sidd_astro'; // Change this to your DB password

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h2>🔍 PhonePe Database Tables Check</h2>";
    echo "<hr>";
    
    // Check if we're running in browser or CLI
    $isBrowser = isset($_SERVER['HTTP_HOST']);
    
    if ($isBrowser) {
        echo "<style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            .success { color: green; background: #e8f5e8; padding: 10px; border-radius: 5px; margin: 5px 0; }
            .error { color: red; background: #ffe8e8; padding: 10px; border-radius: 5px; margin: 5px 0; }
            .warning { color: orange; background: #fff8e8; padding: 10px; border-radius: 5px; margin: 5px 0; }
            .info { color: blue; background: #e8f0ff; padding: 10px; border-radius: 5px; margin: 5px 0; }
            table { border-collapse: collapse; width: 100%; margin: 10px 0; }
            th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
            th { background-color: #f2f2f2; }
            code { background: #f4f4f4; padding: 2px 4px; border-radius: 3px; }
        </style>";
    }
    
    $missingTables = [];
    $missingColumns = [];
    $existingTables = [];
    
    // 1. Check if phonepe_orders table exists
    echo $isBrowser ? "<h3>📋 Table Check Results:</h3>" : "\n=== TABLE CHECK RESULTS ===\n";
    
    $tables = ['phonepe_orders', 'payments'];
    
    foreach ($tables as $table) {
        $stmt = $pdo->query("SHOW TABLES LIKE '$table'");
        $exists = $stmt->rowCount() > 0;
        
        if ($exists) {
            $existingTables[] = $table;
            echo $isBrowser ? "<div class='success'>✅ Table '$table' exists</div>" : "✅ Table '$table' exists\n";
        } else {
            $missingTables[] = $table;
            echo $isBrowser ? "<div class='error'>❌ Table '$table' is missing</div>" : "❌ Table '$table' is missing\n";
        }
    }
    
    // 2. Check payments table columns
    if (in_array('payments', $existingTables)) {
        echo $isBrowser ? "<h3>🔧 Payments Table Columns Check:</h3>" : "\n=== PAYMENTS TABLE COLUMNS CHECK ===\n";
        
        $requiredColumns = [
            'phonepe_order_id' => 'VARCHAR(255)',
            'phonepe_token' => 'TEXT',
            'phonepe_merchant_id' => 'VARCHAR(100)',
            'phonepe_environment' => 'ENUM'
        ];
        
        $stmt = $pdo->query("SHOW COLUMNS FROM payments");
        $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        foreach ($requiredColumns as $column => $type) {
            if (in_array($column, $existingColumns)) {
                echo $isBrowser ? "<div class='success'>✅ Column '$column' exists in payments table</div>" : "✅ Column '$column' exists in payments table\n";
            } else {
                $missingColumns[] = $column;
                echo $isBrowser ? "<div class='error'>❌ Column '$column' is missing in payments table</div>" : "❌ Column '$column' is missing in payments table\n";
            }
        }
    }
    
    // 3. Check phonepe_orders table structure
    if (in_array('phonepe_orders', $existingTables)) {
        echo $isBrowser ? "<h3>🔧 PhonePe Orders Table Structure:</h3>" : "\n=== PHONEPE ORDERS TABLE STRUCTURE ===\n";
        
        $stmt = $pdo->query("SHOW COLUMNS FROM phonepe_orders");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if ($isBrowser) {
            echo "<table><tr><th>Column</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
            foreach ($columns as $column) {
                echo "<tr>";
                echo "<td>" . htmlspecialchars($column['Field']) . "</td>";
                echo "<td>" . htmlspecialchars($column['Type']) . "</td>";
                echo "<td>" . htmlspecialchars($column['Null']) . "</td>";
                echo "<td>" . htmlspecialchars($column['Key']) . "</td>";
                echo "<td>" . htmlspecialchars($column['Default']) . "</td>";
                echo "<td>" . htmlspecialchars($column['Extra']) . "</td>";
                echo "</tr>";
            }
            echo "</table>";
        } else {
            foreach ($columns as $column) {
                echo "Column: {$column['Field']} | Type: {$column['Type']} | Null: {$column['Null']} | Key: {$column['Key']}\n";
            }
        }
    }
    
    // 4. Generate SQL commands for missing items
    if (!empty($missingTables) || !empty($missingColumns)) {
        echo $isBrowser ? "<h3>🛠️ Required SQL Commands:</h3>" : "\n=== REQUIRED SQL COMMANDS ===\n";
        
        $sqlCommands = [];
        
        // Create phonepe_orders table if missing
        if (in_array('phonepe_orders', $missingTables)) {
            $sqlCommands[] = "-- Create phonepe_orders table
CREATE TABLE IF NOT EXISTS `phonepe_orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `order_id` VARCHAR(255) NOT NULL UNIQUE COMMENT 'PhonePe Order ID',
  `user_id` VARCHAR(100) NOT NULL COMMENT 'User/Customer ID',
  `amount` DECIMAL(10,2) NOT NULL COMMENT 'Order amount',
  `token` TEXT NOT NULL COMMENT 'PhonePe order token',
  `merchant_id` VARCHAR(100) NOT NULL COMMENT 'PhonePe Merchant ID',
  `status` ENUM('PENDING', 'SUCCESS', 'FAILED', 'CANCELLED') DEFAULT 'PENDING' COMMENT 'Payment status',
  `environment` ENUM('SANDBOX', 'PRODUCTION') DEFAULT 'PRODUCTION' COMMENT 'PhonePe environment',
  `phonepe_response` TEXT COMMENT 'Full response from PhonePe API',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='PhonePe order details';";
        }
        
        // Add missing columns to payments table
        if (!empty($missingColumns)) {
            if (in_array('phonepe_order_id', $missingColumns)) {
                $sqlCommands[] = "ALTER TABLE `payments` ADD COLUMN `phonepe_order_id` VARCHAR(255) NULL COMMENT 'Reference to phonepe_orders.order_id' AFTER `phonepe_response`;";
            }
            if (in_array('phonepe_token', $missingColumns)) {
                $sqlCommands[] = "ALTER TABLE `payments` ADD COLUMN `phonepe_token` TEXT NULL COMMENT 'PhonePe order token' AFTER `phonepe_order_id`;";
            }
            if (in_array('phonepe_merchant_id', $missingColumns)) {
                $sqlCommands[] = "ALTER TABLE `payments` ADD COLUMN `phonepe_merchant_id` VARCHAR(100) NULL COMMENT 'PhonePe Merchant ID' AFTER `phonepe_token`;";
            }
            if (in_array('phonepe_environment', $missingColumns)) {
                $sqlCommands[] = "ALTER TABLE `payments` ADD COLUMN `phonepe_environment` ENUM('SANDBOX', 'PRODUCTION') DEFAULT 'PRODUCTION' COMMENT 'PhonePe environment' AFTER `phonepe_merchant_id`;";
            }
            
            // Add index
            $sqlCommands[] = "ALTER TABLE `payments` ADD INDEX `idx_phonepe_order_id` (`phonepe_order_id`);";
        }
        
        // Display SQL commands
        foreach ($sqlCommands as $sql) {
            if ($isBrowser) {
                echo "<div class='info'><strong>SQL Command:</strong><br><code>" . nl2br(htmlspecialchars($sql)) . "</code></div>";
            } else {
                echo "\n" . $sql . "\n";
            }
        }
        
        // Save to file option
        if ($isBrowser) {
            $allSql = implode("\n\n", $sqlCommands);
            echo "<div class='warning'><strong>💾 Save SQL Commands:</strong><br>";
            echo "<textarea rows='10' cols='80' readonly>" . htmlspecialchars($allSql) . "</textarea><br>";
            echo "<small>Copy the above SQL and run it in phpMyAdmin or MySQL command line</small></div>";
        }
    }
    
    // 5. Summary
    echo $isBrowser ? "<h3>📊 Summary:</h3>" : "\n=== SUMMARY ===\n";
    
    if (empty($missingTables) && empty($missingColumns)) {
        echo $isBrowser ? "<div class='success'><strong>🎉 All PhonePe tables and columns are properly configured!</strong></div>" : "🎉 All PhonePe tables and columns are properly configured!\n";
    } else {
        echo $isBrowser ? "<div class='warning'><strong>⚠️ Missing items found:</strong><br>" : "⚠️ Missing items found:\n";
        if (!empty($missingTables)) {
            echo $isBrowser ? "• Missing tables: " . implode(', ', $missingTables) . "<br>" : "• Missing tables: " . implode(', ', $missingTables) . "\n";
        }
        if (!empty($missingColumns)) {
            echo $isBrowser ? "• Missing columns: " . implode(', ', $missingColumns) . "<br>" : "• Missing columns: " . implode(', ', $missingColumns) . "\n";
        }
        echo $isBrowser ? "Run the SQL commands above to fix these issues.</div>" : "Run the SQL commands above to fix these issues.\n";
    }
    
    // 6. Test data count
    echo $isBrowser ? "<h3>📈 Current Data:</h3>" : "\n=== CURRENT DATA ===\n";
    
    if (in_array('payments', $existingTables)) {
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM payments WHERE payment_method = 'PHONEPE'");
        $phonepePayments = $stmt->fetch()['count'];
        echo $isBrowser ? "<div class='info'>PhonePe payments in database: <strong>$phonepePayments</strong></div>" : "PhonePe payments in database: $phonepePayments\n";
    }
    
    if (in_array('phonepe_orders', $existingTables)) {
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM phonepe_orders");
        $phonepeOrders = $stmt->fetch()['count'];
        echo $isBrowser ? "<div class='info'>PhonePe orders in database: <strong>$phonepeOrders</strong></div>" : "PhonePe orders in database: $phonepeOrders\n";
    }
    
} catch (PDOException $e) {
    $error = "Database connection failed: " . $e->getMessage();
    echo $isBrowser ? "<div class='error'>❌ $error</div>" : "❌ $error\n";
    echo $isBrowser ? "<div class='info'>Please check your database configuration in this script.</div>" : "Please check your database configuration in this script.\n";
}

echo $isBrowser ? "<hr><small>Script completed at " . date('Y-m-d H:i:s') . "</small>" : "\nScript completed at " . date('Y-m-d H:i:s') . "\n";
?>
