<?php
// Database setup runner for token system
include 'db.php';

try {
    // Read the setup SQL file
    $sql_content = file_get_contents('setup_token_system.sql');
    
    if (!$sql_content) {
        die("Error: Could not read setup_token_system.sql file\n");
    }
    
    // Remove comments and split into individual statements
    $sql_lines = explode("\n", $sql_content);
    $sql_statements = [];
    $current_statement = '';
    
    foreach ($sql_lines as $line) {
        $line = trim($line);
        
        // Skip empty lines and comments
        if (empty($line) || strpos($line, '--') === 0) {
            continue;
        }
        
        $current_statement .= $line . ' ';
        
        // If line ends with semicolon, it's end of statement
        if (substr($line, -1) === ';') {
            $sql_statements[] = trim($current_statement);
            $current_statement = '';
        }
    }
    
    echo "Starting token system database setup...\n\n";
    
    // Execute each statement
    $executed = 0;
    foreach ($sql_statements as $statement) {
        if (empty($statement)) continue;
        
        try {
            $result = $pdo->exec($statement);
            $executed++;
            
            // Check if this is a SELECT statement (for the final message)
            if (stripos($statement, 'SELECT') === 0) {
                $stmt = $pdo->query($statement);
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                if ($row) {
                    echo "Setup Results:\n";
                    foreach ($row as $key => $value) {
                        echo "  $key: $value\n";
                    }
                    echo "\n";
                }
            }
            
        } catch (PDOException $e) {
            echo "Warning executing statement: " . $e->getMessage() . "\n";
            echo "Statement: " . substr($statement, 0, 100) . "...\n\n";
        }
    }
    
    echo "Database setup completed!\n";
    echo "Executed $executed SQL statements.\n\n";
    
    // Verify tables were created
    echo "Verifying table creation:\n";
    $tables = ['user_groups', 'user_group_memberships', 'budgets', 'token_actions', 'token_transactions', 'token_packages'];
    
    foreach ($tables as $table) {
        try {
            $stmt = $pdo->query("SHOW TABLES LIKE '$table'");
            if ($stmt->rowCount() > 0) {
                $count_stmt = $pdo->query("SELECT COUNT(*) as count FROM $table");
                $count = $count_stmt->fetch()['count'];
                echo "  ✓ $table: $count records\n";
            } else {
                echo "  ✗ $table: NOT FOUND\n";
            }
        } catch (PDOException $e) {
            echo "  ✗ $table: ERROR - " . $e->getMessage() . "\n";
        }
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>
