<?php
// Database setup runner for token system - Compatible with existing schema
include 'db.php';

try {
    echo "Starting token system database setup (compatible mode)...\n\n";
    
    // Check if tables exist and alter if needed
    echo "Checking and updating table structures...\n";
    
    // Add missing columns to budgets table if they don't exist
    try {
        $pdo->exec("ALTER TABLE budgets ADD COLUMN user_id int(11) DEFAULT NULL");
        echo "  ✓ Added user_id column to budgets table\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column') === false) {
            echo "  - budgets.user_id: " . $e->getMessage() . "\n";
        } else {
            echo "  ✓ budgets.user_id already exists\n";
        }
    }
    
    try {
        $pdo->exec("ALTER TABLE budgets ADD COLUMN created_by int(11) DEFAULT NULL");
        echo "  ✓ Added created_by column to budgets table\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column') === false) {
            echo "  - budgets.created_by: " . $e->getMessage() . "\n";
        } else {
            echo "  ✓ budgets.created_by already exists\n";
        }
    }
    
    // Add missing columns to user_group_memberships table if they don't exist
    try {
        $pdo->exec("ALTER TABLE user_group_memberships ADD COLUMN added_by int(11) DEFAULT NULL");
        echo "  ✓ Added added_by column to user_group_memberships table\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column') === false) {
            echo "  - user_group_memberships.added_by: " . $e->getMessage() . "\n";
        } else {
            echo "  ✓ user_group_memberships.added_by already exists\n";
        }
    }
    
    // Create token_actions table
    echo "Creating token_actions table...\n";
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `token_actions` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `action_name` varchar(100) NOT NULL,
          `action_description` text,
          `token_cost` decimal(8,2) NOT NULL DEFAULT '0.00',
          `category` varchar(50) DEFAULT 'general',
          `code_snippet` text,
          `is_active` tinyint(1) NOT NULL DEFAULT '1',
          `created_by` int(11) DEFAULT NULL,
          `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          UNIQUE KEY `unique_action_name` (`action_name`),
          KEY `idx_category` (`category`),
          KEY `idx_active` (`is_active`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    
    // Create token_transactions table
    echo "Creating token_transactions table...\n";
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `token_transactions` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `budget_id` int(11) NOT NULL,
          `action_id` int(11) DEFAULT NULL,
          `transaction_type` enum('debit','credit','purchase','refund','adjustment') NOT NULL,
          `amount` decimal(8,2) NOT NULL,
          `description` text,
          `reference_type` varchar(50) DEFAULT NULL,
          `reference_id` int(11) DEFAULT NULL,
          `balance_after` decimal(10,2) DEFAULT NULL,
          `created_by` int(11) DEFAULT NULL,
          `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          KEY `idx_budget_id` (`budget_id`),
          KEY `idx_action_id` (`action_id`),
          KEY `idx_transaction_type` (`transaction_type`),
          KEY `idx_reference` (`reference_type`, `reference_id`),
          KEY `idx_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    
    // Create token_packages table
    echo "Creating token_packages table...\n";
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `token_packages` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` varchar(100) NOT NULL,
          `description` text,
          `token_amount` decimal(8,2) NOT NULL,
          `price` decimal(8,2) NOT NULL,
          `bonus_tokens` decimal(8,2) DEFAULT '0.00',
          `sort_order` int(11) DEFAULT '0',
          `is_active` tinyint(1) NOT NULL DEFAULT '1',
          `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          KEY `idx_active` (`is_active`),
          KEY `idx_sort_order` (`sort_order`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    
    echo "\nInserting default data...\n";
    
    // Insert default token actions
    echo "Inserting default token actions...\n";
    $stmt = $pdo->prepare("
        INSERT IGNORE INTO `token_actions` (`action_name`, `action_description`, `token_cost`, `category`, `code_snippet`, `created_by`) VALUES
        (?, ?, ?, ?, ?, ?)
    ");
    
    $actions = [
        ['add_participant', 'Add a new participant to an event', 1.00, 'participants', 'TokenSystem::charge(\'add_participant\', $user_id, [\'event_id\' => $event_id]);', 1],
        ['create_event', 'Create a new event', 30.00, 'events', 'TokenSystem::charge(\'create_event\', $user_id, [\'event_id\' => $event_id]);', 1],
        ['add_judge', 'Add a judge to an event', 5.00, 'judges', 'TokenSystem::charge(\'add_judge\', $user_id, [\'event_id\' => $event_id]);', 1],
        ['generate_report', 'Generate and export reports', 10.00, 'reports', 'TokenSystem::charge(\'generate_report\', $user_id, [\'report_type\' => $report_type]);', 1],
        ['send_notification', 'Send notifications to participants/judges', 2.00, 'communications', 'TokenSystem::charge(\'send_notification\', $user_id, [\'notification_id\' => $notification_id]);', 1],
        ['export_data', 'Export event data', 5.00, 'reports', 'TokenSystem::charge(\'export_data\', $user_id, [\'export_type\' => $export_type]);', 1],
        ['premium_feature', 'Access premium features', 50.00, 'premium', 'TokenSystem::charge(\'premium_feature\', $user_id, [\'feature_id\' => $feature_id]);', 1],
        ['bulk_import', 'Import participants in bulk', 15.00, 'participants', 'TokenSystem::charge(\'bulk_import\', $user_id, [\'import_id\' => $import_id]);', 1],
        ['advanced_scoring', 'Use advanced scoring features', 25.00, 'scoring', 'TokenSystem::charge(\'advanced_scoring\', $user_id, [\'scoring_id\' => $scoring_id]);', 1],
        ['custom_reports', 'Generate custom reports', 20.00, 'reports', 'TokenSystem::charge(\'custom_reports\', $user_id, [\'report_id\' => $report_id]);', 1]
    ];
    
    foreach ($actions as $action) {
        $stmt->execute($action);
    }
    
    // Insert default token packages
    echo "Inserting default token packages...\n";
    $stmt = $pdo->prepare("
        INSERT IGNORE INTO `token_packages` (`id`, `name`, `description`, `token_amount`, `price`, `bonus_tokens`, `sort_order`) VALUES
        (?, ?, ?, ?, ?, ?, ?)
    ");
    
    $packages = [
        [1, 'Starter Pack', 'Perfect for small events', 100.00, 9.99, 10.00, 1],
        [2, 'Professional', 'Great for regular organizers', 500.00, 39.99, 75.00, 2],
        [3, 'Enterprise', 'For large organizations', 1500.00, 99.99, 300.00, 3],
        [4, 'Mega Pack', 'Maximum value package', 5000.00, 299.99, 1000.00, 4]
    ];
    
    foreach ($packages as $package) {
        $stmt->execute($package);
    }
    
    // Create/update default admin group
    echo "Setting up default admin group...\n";
    $stmt = $pdo->prepare("
        INSERT INTO `user_groups` (`id`, `name`, `description`, `color`, `created_by`) VALUES
        (?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
        `name` = VALUES(`name`),
        `description` = VALUES(`description`),
        `color` = VALUES(`color`)
    ");
    $stmt->execute([1, 'Administrators', 'System administrators with full access', '#dc3545', 1]);
    
    // Add admin users to admin group (only works if added_by column exists)
    echo "Adding admin users to admin group...\n";
    try {
        $stmt = $pdo->prepare("
            INSERT IGNORE INTO `user_group_memberships` (`user_id`, `group_id`, `role_in_group`, `added_by`) 
            SELECT u.id, 1, 'owner', 1 
            FROM users u 
            WHERE u.role IN ('super_admin', 'admin') 
            AND NOT EXISTS (
                SELECT 1 FROM user_group_memberships ugm 
                WHERE ugm.user_id = u.id AND ugm.group_id = 1
            )
        ");
        $stmt->execute();
        echo "  ✓ Admin users added to admin group\n";
    } catch (PDOException $e) {
        // Fallback without added_by column
        $stmt = $pdo->prepare("
            INSERT IGNORE INTO `user_group_memberships` (`user_id`, `group_id`, `role_in_group`) 
            SELECT u.id, 1, 'owner'
            FROM users u 
            WHERE u.role IN ('super_admin', 'admin') 
            AND NOT EXISTS (
                SELECT 1 FROM user_group_memberships ugm 
                WHERE ugm.user_id = u.id AND ugm.group_id = 1
            )
        ");
        $stmt->execute();
        echo "  ✓ Admin users added to admin group (fallback mode)\n";
    }
    
    // Create/update default budget for admin group
    echo "Setting up default budget for admin group...\n";
    try {
        $stmt = $pdo->prepare("
            INSERT INTO `budgets` (`id`, `group_id`, `balance`, `total_spent`, `is_active`, `created_by`) VALUES
            (?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
            `balance` = GREATEST(`balance`, VALUES(`balance`)),
            `is_active` = 1
        ");
        $stmt->execute([1, 1, 1000.00, 0.00, 1, 1]);
        echo "  ✓ Default budget created/updated\n";
    } catch (PDOException $e) {
        // Fallback without created_by column
        $stmt = $pdo->prepare("
            INSERT INTO `budgets` (`id`, `group_id`, `balance`, `total_spent`, `is_active`) VALUES
            (?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
            `balance` = GREATEST(`balance`, VALUES(`balance`)),
            `is_active` = 1
        ");
        $stmt->execute([1, 1, 1000.00, 0.00, 1]);
        echo "  ✓ Default budget created/updated (fallback mode)\n";
    }
    
    echo "\nSetup completed successfully!\n\n";
    
    // Verify tables and show counts
    echo "Verification Results:\n";
    $tables = ['user_groups', 'user_group_memberships', 'budgets', 'token_actions', 'token_transactions', 'token_packages'];
    
    foreach ($tables as $table) {
        try {
            $stmt = $pdo->query("SELECT COUNT(*) as count FROM $table");
            $count = $stmt->fetch()['count'];
            echo "  ✓ $table: $count records\n";
        } catch (PDOException $e) {
            echo "  ✗ $table: ERROR - " . $e->getMessage() . "\n";
        }
    }
    
    // Show sample data
    echo "\nSample Data:\n";
    
    // Show token actions
    $stmt = $pdo->query("SELECT action_name, token_cost, category FROM token_actions LIMIT 5");
    echo "Token Actions:\n";
    while ($row = $stmt->fetch()) {
        echo "  - {$row['action_name']}: {$row['token_cost']} tokens ({$row['category']})\n";
    }
    
    // Show budgets
    $stmt = $pdo->query("SELECT id, group_id, user_id, balance FROM budgets LIMIT 3");
    echo "Budgets:\n";
    while ($row = $stmt->fetch()) {
        $type = $row['group_id'] ? "Group #{$row['group_id']}" : "User #{$row['user_id']}";
        echo "  - Budget #{$row['id']}: {$row['balance']} tokens ($type)\n";
    }
    
    echo "\nToken system database setup completed successfully!\n";
    echo "You can now use the Token Management panel to manage your system.\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
    echo "Please check your database connection and permissions.\n";
}
?>
