<?php
require_once 'config/config.php';

echo "<h2>Debug: Internal Employees System</h2>\n";

// Mock session data for testing (remove this in production)
if (!isset($_SESSION['company_id'])) {
    $_SESSION['company_id'] = 1; // Use first company for testing
    $_SESSION['company_name'] = 'Test Company';
    $_SESSION['user_id'] = 1;
    echo "<div style='color: orange; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; margin: 10px 0;'>\n";
    echo "⚠ Using mock session data for testing. Company ID: 1\n";
    echo "</div>\n";
}

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // Check candidates table structure
    echo "<h3>1. Checking candidates table structure:</h3>\n";
    $stmt = $db->query("DESCRIBE candidates");
    $columns = $stmt->fetchAll();
    
    $required_columns = ['candidate_type', 'employee_code', 'company_id', 'hr_approval_status', 'hr_approval_date', 'hr_approval_notes', 'approved_by'];
    $existing_columns = array_column($columns, 'Field');
    
    echo "<table border='1' style='border-collapse: collapse; margin: 10px 0;'>\n";
    echo "<tr><th>Column</th><th>Type</th><th>Status</th></tr>\n";
    foreach ($required_columns as $col) {
        $exists = in_array($col, $existing_columns);
        $color = $exists ? 'background-color: #d4edda;' : 'background-color: #f8d7da;';
        $status = $exists ? '✓ EXISTS' : '✗ MISSING';
        echo "<tr style='$color'><td>$col</td><td>";
        if ($exists) {
            $column_info = array_filter($columns, function($c) use ($col) { return $c['Field'] === $col; });
            $column_info = reset($column_info);
            echo $column_info['Type'];
        } else {
            echo 'N/A';
        }
        echo "</td><td>$status</td></tr>\n";
    }
    echo "</table>\n";
    
    // Check if there are any companies
    echo "<h3>2. Checking companies:</h3>\n";
    $stmt = $db->query("SELECT COUNT(*) as count FROM companies");
    $company_count = $stmt->fetch()['count'];
    echo "<p>Total companies in database: <strong>$company_count</strong></p>\n";
    
    if ($company_count > 0) {
        $stmt = $db->query("SELECT id, name, status FROM companies LIMIT 5");
        $companies = $stmt->fetchAll();
        echo "<table border='1' style='border-collapse: collapse; margin: 10px 0;'>\n";
        echo "<tr><th>ID</th><th>Name</th><th>Status</th></tr>\n";
        foreach ($companies as $company) {
            $color = ($company['status'] === 'approved') ? 'background-color: #d4edda;' : 'background-color: #fff3cd;';
            echo "<tr style='$color'><td>{$company['id']}</td><td>{$company['name']}</td><td>{$company['status']}</td></tr>\n";
        }
        echo "</table>\n";
    }
    
    // Test the internal employees query
    echo "<h3>3. Testing internal employees query:</h3>\n";
    
    try {
        $company_id = $_SESSION['company_id'];
        
        // First, check if there are any internal candidates
        $stmt = $db->prepare("SELECT COUNT(*) as count FROM candidates WHERE candidate_type = 'internal'");
        $stmt->execute();
        $internal_count = $stmt->fetch()['count'];
        echo "<p>Total internal candidates: <strong>$internal_count</strong></p>\n";
        
        // Check internal candidates for specific company
        if (in_array('company_id', $existing_columns)) {
            $stmt = $db->prepare("SELECT COUNT(*) as count FROM candidates WHERE candidate_type = 'internal' AND company_id = ?");
            $stmt->execute([$company_id]);
            $company_internal_count = $stmt->fetch()['count'];
            echo "<p>Internal candidates for company $company_id: <strong>$company_internal_count</strong></p>\n";
            
            // Test the exact query from internal-employees.php
            $stmt = $db->prepare("
                SELECT 
                    c.*,
                    cu.name as approved_by_name,
                    (SELECT COUNT(*) FROM job_applications ja WHERE ja.candidate_id = c.id) as applications_count
                FROM candidates c 
                LEFT JOIN company_users cu ON c.approved_by = cu.id 
                WHERE c.candidate_type = 'internal' AND c.company_id = ?
                ORDER BY c.created_at DESC
                LIMIT 5
            ");
            $stmt->execute([$company_id]);
            $test_candidates = $stmt->fetchAll();
            
            echo "<p>✓ Query executed successfully. Found " . count($test_candidates) . " internal candidates.</p>\n";
            
            if (!empty($test_candidates)) {
                echo "<h4>Sample internal candidates:</h4>\n";
                echo "<table border='1' style='border-collapse: collapse; margin: 10px 0;'>\n";
                echo "<tr><th>ID</th><th>Name</th><th>Employee Code</th><th>Status</th><th>Company ID</th></tr>\n";
                foreach ($test_candidates as $candidate) {
                    echo "<tr>\n";
                    echo "<td>{$candidate['id']}</td>\n";
                    echo "<td>{$candidate['first_name']} {$candidate['last_name']}</td>\n";
                    echo "<td>{$candidate['employee_code']}</td>\n";
                    echo "<td>{$candidate['hr_approval_status']}</td>\n";
                    echo "<td>{$candidate['company_id']}</td>\n";
                    echo "</tr>\n";
                }
                echo "</table>\n";
            }
            
        } else {
            echo "<div style='color: red; padding: 10px; background: #f8d7da; border: 1px solid #f5c6cb;'>\n";
            echo "✗ company_id column is missing from candidates table. Internal employee system cannot work.\n";
            echo "</div>\n";
        }
        
    } catch (PDOException $e) {
        echo "<div style='color: red; padding: 10px; background: #f8d7da; border: 1px solid #f5c6cb;'>\n";
        echo "<strong>SQL Error in internal employees query:</strong><br>\n";
        echo "Error: " . $e->getMessage() . "<br>\n";
        echo "Code: " . $e->getCode() . "\n";
        echo "</div>\n";
    }
    
    // Test approval functionality
    echo "<h3>4. Testing approval functionality:</h3>\n";
    
    if (in_array('approved_by', $existing_columns)) {
        // Check if company_users table exists and has data
        try {
            $stmt = $db->query("SELECT COUNT(*) as count FROM company_users");
            $company_users_count = $stmt->fetch()['count'];
            echo "<p>Company users in database: <strong>$company_users_count</strong></p>\n";
            
            if ($company_users_count > 0) {
                $stmt = $db->prepare("SELECT COUNT(*) as count FROM company_users WHERE company_id = ?");
                $stmt->execute([$company_id]);
                $company_users_for_company = $stmt->fetch()['count'];
                echo "<p>Company users for company $company_id: <strong>$company_users_for_company</strong></p>\n";
            }
            
        } catch (PDOException $e) {
            echo "<div style='color: red;'>Error checking company_users table: " . $e->getMessage() . "</div>\n";
        }
    }
    
    echo "<hr>\n";
    echo "<h3>Summary and Recommendations:</h3>\n";
    
    $missing_columns = array_diff($required_columns, $existing_columns);
    
    if (empty($missing_columns)) {
        echo "<div style='color: green; padding: 15px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h4>✅ All required columns exist!</h4>\n";
        echo "<p>The internal employees system should work. If you're still getting errors, they might be due to:</p>\n";
        echo "<ul>\n";
        echo "<li>Session issues (make sure you're logged in as a company user)</li>\n";
        echo "<li>Permission issues</li>\n";
        echo "<li>Missing data in related tables</li>\n";
        echo "</ul>\n";
        echo "<p><a href='company/internal-employees.php' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>Try Internal Employees Page</a></p>\n";
        echo "</div>\n";
    } else {
        echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h4>❌ Missing columns found!</h4>\n";
        echo "<p>Missing columns: <strong>" . implode(', ', $missing_columns) . "</strong></p>\n";
        echo "<p>Run the migration script to add missing columns:</p>\n";
        echo "<p><a href='quick_fix_columns_v2.php' style='background: #dc3545; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>Fix Missing Columns</a></p>\n";
        echo "</div>\n";
    }
    
} catch (Exception $e) {
    echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
    echo "<strong>Fatal Error:</strong> " . $e->getMessage() . "<br>\n";
    echo "<strong>File:</strong> " . $e->getFile() . "<br>\n";
    echo "<strong>Line:</strong> " . $e->getLine() . "\n";
    echo "</div>\n";
}
?>