<?php
// This script is intended to be included within admin_dashboard.php,
// ensuring $conn (database connection) and $_SESSION variables are available.

// Check if $conn exists (safety check, typically handled by dashboard)
if (!isset($conn)) {
    if (file_exists('config/db_connection.php')) {
        require_once 'config/db_connection.php';
    } else {
        echo "<div class='message error'>FATAL ERROR: Database connection configuration file not found.</div>";
        exit();
    }
}

$message = '';
$message_class = '';
$MAX_FILE_SIZE = 5 * 1024 * 1024; // 5 MB limit for PDF files
$search_query = $_GET['search_query'] ?? ''; // Initialize search query from GET
$search_results = []; // Array to hold search results
$all_applications = []; // Array to hold all applications
$message_fetch = ''; // Initialize error message
$posted_data = []; // Holds POSTed data for form stickiness

// --- JOB ROLES DEFINITION (NEW) ---
$job_roles = [
    'SOC Engineer', 
    'Blue Teamer', 
    'Pentester', 
    'Red Teamer', 
    'HR', 
    'Infra Expert', 
    'Manager', 
    'Forensic Expert', 
    'Threat Hunter', 
    'Malware Analyst'
];

// ----------------------------------------------------------------------
// 0. APPLICATION DELETION LOGIC (NEWLY ADDED)
// ----------------------------------------------------------------------
if (isset($_GET['delete_id'])) {
    $applicant_id = (int)$_GET['delete_id'];

    if ($applicant_id > 0 && isset($conn)) {
        // 1. Fetch file paths
        $stmt_fetch = $conn->prepare("SELECT cv_file_path, cover_letter_file_path, applicant_name FROM applicants_cv_bank WHERE applicant_id = ?");
        $stmt_fetch->bind_param("i", $applicant_id);
        $stmt_fetch->execute();
        $result = $stmt_fetch->get_result();
        $app_data = $result->fetch_assoc();
        $stmt_fetch->close();

        if ($app_data) {
            $cv_path = $app_data['cv_file_path'];
            $cl_path = $app_data['cover_letter_file_path'];
            $applicant_name_deleted = $app_data['applicant_name'];

            // 2. Delete database record
            $stmt_delete = $conn->prepare("DELETE FROM applicants_cv_bank WHERE applicant_id = ?");
            
            if ($stmt_delete && $stmt_delete->bind_param("i", $applicant_id) && $stmt_delete->execute()) {
                // 3. Delete physical files
                $deleted_files_count = 0;
                if ($cv_path && file_exists($cv_path) && unlink($cv_path)) {
                    $deleted_files_count++;
                }
                if ($cl_path && file_exists($cl_path) && unlink($cl_path)) {
                    $deleted_files_count++;
                }

                $message = "Success: Application for '{$applicant_name_deleted}' and associated files ({$deleted_files_count}) deleted.";
                $message_class = 'success';
            } else {
                $message = "Error: Database deletion failed for application ID {$applicant_id}: " . ($stmt_delete ? $stmt_delete->error : $conn->error);
                $message_class = 'error';
            }
        } else {
            $message = "Error: Application ID {$applicant_id} not found.";
            $message_class = 'error';
        }
    } else {
        $message = "Error: Invalid application ID or database connection missing.";
        $message_class = 'error';
    }
    // Note: No header redirect used here, relying on page refresh to clear GET params.
}

// Helper function to handle file upload and validation
function handle_pdf_upload($file_key, $upload_dir) {
    global $message, $message_class, $MAX_FILE_SIZE;

    if (!isset($_FILES[$file_key]) || $_FILES[$file_key]['error'] == UPLOAD_ERR_NO_FILE) {
        return ['success' => false, 'path' => null, 'error' => "Missing file for '{$file_key}'. It is a required field."];
    }
    
    $file = $_FILES[$file_key];

    // 1. Check for upload errors
    if ($file['error'] !== UPLOAD_ERR_OK) {
        return ['success' => false, 'path' => null, 'error' => "Upload error for '{$file_key}': " . $file['error']];
    }

    // 2. Check file size (5MB limit)
    if ($file['size'] > $MAX_FILE_SIZE) {
        return ['success' => false, 'path' => null, 'error' => "File size exceeds the 5MB limit for '{$file_key}'."];
    }

    // 3. Check file extension and MIME type (PDF only)
    $file_info = pathinfo($file['name']);
    $file_ext = strtolower($file_info['extension'] ?? '');
    $mime_type = $file['type'] ?? '';

    $allowed_mime = 'application/pdf';

    if ($file_ext !== 'pdf' || $mime_type !== $allowed_mime) {
        return ['success' => false, 'path' => null, 'error' => "Invalid file type for '{$file_key}'. Only PDF files are allowed."];
    }
    
    // 4. Sanitize file name and create unique path
    $safe_file_name = preg_replace("/[^a-zA-Z0-9_\.]/", "_", basename($file['name']));
    $final_file_name = time() . "_" . uniqid() . "_" . $safe_file_name;
    $target_file = $upload_dir . $final_file_name;

    // 5. Move uploaded file
    // NOTE: In a real system, ensure the 'uploads/cv_bank/' directory exists and is writable.
    if (move_uploaded_file($file['tmp_name'], $target_file)) {
        return ['success' => true, 'path' => $target_file, 'error' => null];
    } else {
        return ['success' => false, 'path' => null, 'error' => "Error moving the uploaded file for '{$file_key}'. Check server permissions."];
    }
}


// --- 1. HANDLE CV BANK APPLICATION SUBMISSION ---
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['submit_application'])) {
    
    // Capture POST data for stickiness, even if upload fails
    $posted_data = $_POST;

    // Define the upload directory (relative to where the dashboard script is run)
    $upload_dir = 'uploads/cv_bank/';
    if (!is_dir($upload_dir)) {
        // Attempt to create the directory if it doesn't exist
        mkdir($upload_dir, 0777, true);
    }
    
    // --- Step 1: Handle File Uploads (CV and Cover Letter) ---
    $cv_upload_result = handle_pdf_upload('full_cv_file', $upload_dir);
    
    // Cover Letter is optional in this logic, but if provided, must be a PDF
    $cover_letter_upload_result = ['success' => true, 'path' => null, 'error' => null]; // Default to success/null path
    if (isset($_FILES['cover_letter_file']) && $_FILES['cover_letter_file']['error'] !== UPLOAD_ERR_NO_FILE) {
        $cover_letter_upload_result = handle_pdf_upload('cover_letter_file', $upload_dir);
    }

    // Check for upload errors
    if (!$cv_upload_result['success']) {
        $message = "CV Upload Error: " . $cv_upload_result['error'];
        $message_class = 'error';
    } elseif (!$cover_letter_upload_result['success']) {
        $message = "Cover Letter Upload Error: " . $cover_letter_upload_result['error'];
        $message_class = 'error';
    } else {
        
        // --- Step 2: Prepare Application Data for Database ---
        $applicant_name = trim($posted_data['applicant_name'] ?? '');
        $email = trim($posted_data['email'] ?? '');
        $phone_number = trim($posted_data['phone_number'] ?? '');
        $desired_job_role = trim($posted_data['desired_job_role'] ?? '');
        $area_of_specialty = trim($posted_data['area_of_specialty'] ?? '');
        $highest_degree_education = trim($posted_data['highest_degree_education'] ?? '');
        $certification = trim($posted_data['certification'] ?? '');
        
        // Mandatory fields check
        if (empty($applicant_name) || empty($email) || empty($desired_job_role)) {
            $message = "Error: Applicant Name, Email, and Desired Job Role are required fields.";
            $message_class = 'error';
            // Cleanup uploaded files if the rest of the form data is missing
            if ($cv_upload_result['path'] && file_exists($cv_upload_result['path'])) unlink($cv_upload_result['path']);
            if ($cover_letter_upload_result['path'] && file_exists($cover_letter_upload_result['path'])) unlink($cover_letter_upload_result['path']);
        } else {
            
            // Get the user ID of the admin performing the upload
            $uploaded_by_id = $_SESSION['user_id'] ?? 1; // Default to user_id 1 if not set

            // --- Step 3: Insert into Database (applicants_cv_bank) ---
            $sql_insert = "INSERT INTO applicants_cv_bank
                            (applicant_name, email, phone_number, desired_job_role, area_of_specialty,
                             highest_degree_education, certification, cv_file_path, cover_letter_file_path, uploaded_by)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                            
            if ($stmt = $conn->prepare($sql_insert)) {
                // 'sssssssssi' -> 9 strings and 1 integer
                $stmt->bind_param("sssssssssi",
                    $applicant_name, $email, $phone_number, $desired_job_role, $area_of_specialty,
                    $highest_degree_education, $certification, $cv_upload_result['path'], $cover_letter_upload_result['path'],
                    $uploaded_by_id
                );
                
                if ($stmt->execute()) {
                    $message = "Success: Application for '{$applicant_name}' submitted successfully.";
                    $message_class = 'success';
                    // Clear posted data on successful submission
                    $posted_data = [];
                } else {
                    $message = "Error submitting application: " . $stmt->error;
                    $message_class = 'error';
                    // Cleanup uploaded files on DB failure
                    if ($cv_upload_result['path'] && file_exists($cv_upload_result['path'])) unlink($cv_upload_result['path']);
                    if ($cover_letter_upload_result['path'] && file_exists($cover_letter_upload_result['path'])) unlink($cover_letter_upload_result['path']);
                }
                $stmt->close();
            } else {
                $message = "Error: Database preparation failed for application submission.";
                $message_class = 'error';
            }
        }
    }
}

// ----------------------------------------------------------------------
// 2. HANDLE SEARCH & FETCH APPLICATIONS FOR DISPLAY
// ----------------------------------------------------------------------

if (isset($conn)) {
    // --- A. Handle Search (Populate $search_results if query is active) ---
    if (!empty($search_query)) {
        $search_term = "%" . trim($search_query) . "%";
        
        $sql_search = "SELECT applicant_id, applicant_name, desired_job_role, application_date, area_of_specialty, cv_file_path, cover_letter_file_path 
                       FROM applicants_cv_bank 
                       WHERE applicant_name LIKE ? OR desired_job_role LIKE ? 
                       ORDER BY application_date DESC";
        
        if ($stmt_search = $conn->prepare($sql_search)) {
            $stmt_search->bind_param("ss", $search_term, $search_term);
            $stmt_search->execute();
            $result_search = $stmt_search->get_result();
            while ($row = $result_search->fetch_assoc()) {
                $search_results[] = $row;
            }
            $stmt_search->close();
        }
    }
    
    // --- B. Fetch All Applications (The original logic, always runs) ---
    $sql_fetch = "SELECT applicant_id, applicant_name, desired_job_role, application_date, area_of_specialty, cv_file_path, cover_letter_file_path 
                  FROM applicants_cv_bank 
                  ORDER BY application_date DESC";
    $result = $conn->query($sql_fetch);

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $all_applications[] = $row;
        }
    } else {
        $message_fetch = "Error: Could not retrieve applications. Please run the SQL query to create the 'applicants_cv_bank' table.";
    }
}
?>

<!-- ==================================================================== -->
<!-- CV BANK MANAGEMENT HTML SECTION -->
<!-- ==================================================================== -->

<style>
    /* Styling specific to this section, reusing form-card from dashboard */
    .section-header {
        margin-bottom: 25px;
        color: #2c3e50;
    }
    .data-container {
        margin-top: 30px;
    }
    .message {
        padding: 10px;
        margin-bottom: 20px;
        border-radius: 4px;
        font-weight: bold;
    }
    .message.success {
        background-color: #d4edda;
        color: #155724;
        border: 1px solid #c3e6cb;
    }
    .message.error {
        background-color: #f8d7da;
        color: #721c24;
        border: 1px solid #f5c6cb;
    }
    .grid-form {
        display: grid;
        grid-template-columns: 1fr 1fr;
        gap: 20px;
    }
    .grid-form .full-width {
        grid-column: 1 / -1;
    }
    .grid-form label {
        margin-bottom: 0;
    }
    .grid-form input, .grid-form select {
        width: 100%;
        box-sizing: border-box;
        padding: 8px; /* Added padding for uniformity with inputs */
        border: 1px solid #ccc; /* Added border for uniformity with inputs */
        border-radius: 4px; /* Added border-radius */
    }
    /* New Search Bar Styling */
    .search-bar {
        margin-bottom: 20px;
        padding: 15px;
        border: 1px solid #ccc;
        border-radius: 8px;
        background: #f9f9f9;
        display: flex;
        gap: 10px;
        align-items: center;
    }
    .search-bar input[type="text"] {
        flex-grow: 1;
        padding: 8px;
        border-radius: 4px;
        border: 1px solid #bdc3c7;
    }
    .search-bar button {
        padding: 8px 15px;
        background-color: #3498db;
        color: white;
        border: none;
        border-radius: 4px;
        cursor: pointer;
    }
    .search-bar button:hover {
        background-color: #2980b9;
    }
    .btn-delete { 
        background-color: #e74c3c; 
        color: white; 
        border: none; 
        border-radius: 4px; 
        cursor: pointer;
    }
    /* Separate list styling */
    .search-results-container {
        border: 2px solid #3498db;
        padding: 15px;
        border-radius: 8px;
        margin-bottom: 20px;
    }

    /* Client List Table Styling (NEW) */
    .client-list-table {
        margin-top: 15px;
        width: 100%;
        border-collapse: collapse;
    }
    .client-list-table th, .client-list-table td {
        padding: 10px;
        border: 1px solid #ecf0f1;
        text-align: left;
    }
    .client-list-table thead th {
        background-color: #7f8c8d;
        color: white;
    }
    .client-list-table tr:nth-child(even) {
        background-color: #f7f7f7;
    }
</style>

<div class="form-card">
    <h2 class="section-header">Submit New CV Application</h2>
    
    <?php if ($message): ?>
        <div class="message <?php echo $message_class; ?>">
            <?php echo htmlspecialchars($message); ?>
        </div>
    <?php endif; ?>

    <form method="POST" action="admin_dashboard.php?page=manage_cv_bank" enctype="multipart/form-data">
        <input type="hidden" name="submit_application" value="1">
        
        <div class="grid-form">
            <!-- Basic Details -->
            <div>
                <label for="applicant_name">Applicant's Name <span style="color:red;">*</span></label>
                <input type="text" id="applicant_name" name="applicant_name" value="<?php echo htmlspecialchars($posted_data['applicant_name'] ?? ''); ?>" required>
            </div>
            
            <!-- DESIRED JOB ROLE (CHANGED TO DROPDOWN) -->
            <div>
                <label for="desired_job_role">Desired Job Role <span style="color:red;">*</span></label>
                <select id="desired_job_role" name="desired_job_role" required>
                    <option value="">-- Select Job Role --</option>
                    <?php 
                    $sticky_job_role = $posted_data['desired_job_role'] ?? '';
                    foreach ($job_roles as $role): 
                        $selected = ($sticky_job_role === $role) ? 'selected' : '';
                    ?>
                        <option value="<?php echo htmlspecialchars($role); ?>" <?php echo $selected; ?>>
                            <?php echo htmlspecialchars($role); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <!-- Contact Information -->
            <div>
                <label for="email">Email <span style="color:red;">*</span></label>
                <input type="email" id="email" name="email" value="<?php echo htmlspecialchars($posted_data['email'] ?? ''); ?>" required>
            </div>
            <div>
                <label for="phone_number">Phone Number</label>
                <input type="text" id="phone_number" name="phone_number" value="<?php echo htmlspecialchars($posted_data['phone_number'] ?? ''); ?>">
            </div>

            <!-- Education & Specialty -->
            <div>
                <label for="area_of_specialty">Area of Specialty</label>
                <input type="text" id="area_of_specialty" name="area_of_specialty" value="<?php echo htmlspecialchars($posted_data['area_of_specialty'] ?? ''); ?>">
            </div>
            <div>
                <label for="highest_degree_education">Highest Degree of Education</label>
                <select id="highest_degree_education" name="highest_degree_education">
                    <option value="">-- Select Degree --</option>
                    <?php 
                    $sticky_degree = $posted_data['highest_degree_education'] ?? '';
                    $degrees = ['High School', "Associate's Degree", "Bachelor's Degree", "Master's Degree", 'Doctorate/PhD'];
                    foreach ($degrees as $degree):
                        $value = str_replace(["'s Degree", '/PhD'], '', $degree); // Use cleaner value for POST
                        $selected = ($sticky_degree === $value) ? 'selected' : '';
                    ?>
                        <option value="<?php echo htmlspecialchars($value); ?>" <?php echo $selected; ?>>
                            <?php echo htmlspecialchars($degree); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="full-width">
                <label for="certification">Certifications (e.g., CISSP, PMP)</label>
                <input type="text" id="certification" name="certification" value="<?php echo htmlspecialchars($posted_data['certification'] ?? ''); ?>">
            </div>

            <!-- File Uploads (PDF Only) -->
            <div>
                <label for="full_cv_file">Full CV (PDF Only, Max 5MB) <span style="color:red;">*</span></label>
                <input type="file" id="full_cv_file" name="full_cv_file" accept=".pdf" required>
            </div>
            <div>
                <label for="cover_letter_file">Cover Letter (PDF Only, Optional, Max 5MB)</label>
                <input type="file" id="cover_letter_file" name="cover_letter_file" accept=".pdf">
            </div>

            <div class="full-width" style="text-align: center; margin-top: 10px;">
                <button type="submit" class="btn-primary" style="padding: 10px 30px;">Submit Application</button>
            </div>
        </div>
    </form>
</div>

<!-- ==================================================================== -->
<!-- APPLICATION LIST DISPLAY SECTION -->
<!-- ==================================================================== -->

<div class="form-card data-container">
    <h2 class="section-header">CV Bank Applications</h2>
    
    <!-- NEW: Search Bar -->
    <form method="GET" action="admin_dashboard.php" class="search-bar">
        <input type="hidden" name="page" value="manage_cv_bank">
        <input type="text" name="search_query" placeholder="Search by Applicant Name or Job Role..." value="<?php echo htmlspecialchars($search_query); ?>">
        <button type="submit">Search</button>
        <?php if (!empty($search_query)): ?>
            <a href="admin_dashboard.php?page=manage_cv_bank" style="padding: 8px 15px; background-color: #e74c3c; color: white; border-radius: 4px; text-decoration: none;">Clear Search</a>
        <?php endif; ?>
    </form>

    <?php if (isset($message_fetch)): ?>
        <div class="message error"><?php echo htmlspecialchars($message_fetch); ?></div>
    <?php endif; ?>

    <!-- Conditional Display: Show Search Results FIRST -->
    <?php if (!empty($search_query) && !empty($search_results)): ?>
        <div class="search-results-container">
            <h3 style="margin-top: 0; color: #3498db;">Search Results (<?php echo count($search_results); ?> Found)</h3>
            <?php display_application_table($search_results); ?>
        </div>
        <h3 style="margin-top: 30px; border-bottom: 1px solid #eee; padding-bottom: 5px;">Full CV Bank List</h3>
    <?php elseif (!empty($search_query) && empty($search_results)): ?>
        <p class="message error">No results found for "<?php echo htmlspecialchars($search_query); ?>". Displaying full list below.</p>
        <h3 style="margin-top: 30px; border-bottom: 1px solid #eee; padding-bottom: 5px;">Full CV Bank List</h3>
    <?php endif; ?>

    <!-- Default Display: Full List -->
    <?php if (empty($search_query) || !empty($search_results) || (!empty($search_query) && empty($search_results))): ?>
        <?php if (!empty($all_applications)): ?>
            <?php display_application_table($all_applications); ?>
        <?php else: ?>
            <p>No applications found in the CV bank.</p>
        <?php endif; ?>
    <?php endif; ?>

</div>

<?php 
// Helper function to render the CV table (to avoid code duplication)
function display_application_table($applications) {
?>
    <table class="data-table">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Job Role</th>
                <th>Specialty</th>
                <th>Application Date</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($applications as $app): ?>
                <tr>
                    <td><?php echo htmlspecialchars($app['applicant_id']); ?></td>
                    <td><?php echo htmlspecialchars($app['applicant_name']); ?></td>
                    <td><?php echo htmlspecialchars($app['desired_job_role']); ?></td>
                    <td><?php echo htmlspecialchars($app['area_of_specialty'] ?? 'N/A'); ?></td>
                    <td><?php echo htmlspecialchars(substr($app['application_date'], 0, 10)); ?></td>
                    <td>
                        <!-- View CV Link -->
                        <a href="<?php echo htmlspecialchars($app['cv_file_path']); ?>" target="_blank" class="btn-primary" style="padding: 5px 10px; font-size: 12px; background-color: #2ecc71;">View CV</a>
                        
                        <!-- View Cover Letter Link (Only if path exists) -->
                        <?php if (!empty($app['cover_letter_file_path'])): ?>
                            <a href="<?php echo htmlspecialchars($app['cover_letter_file_path']); ?>" target="_blank" class="btn-primary" style="padding: 5px 10px; font-size: 12px; background-color: #3498db;">View CL</a>
                        <?php endif; ?>

                        <!-- FUNCTIONAL DELETE BUTTON (A HREF for GET request) -->
                        <?php 
                        $delete_url = "admin_dashboard.php?page=manage_cv_bank&delete_id=" . $app['applicant_id'];
                        ?>
                        <a href="<?php echo $delete_url; ?>" 
                           onclick="return confirm('Are you sure you want to permanently delete the application for <?php echo htmlspecialchars($app['applicant_name']); ?>?');"
                           class="btn-delete" style="padding: 5px 10px; font-size: 12px; margin-left: 5px;">Delete</a>
                    </td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
<?php
}
?>

<!-- ==================================================================== -->
<!-- CLIENT PORTFOLIO MANAGEMENT SECTION (Requested Content Integration) -->
<!-- ==================================================================== -->

<?php
// PHP Logic for Client Management
$client_message = '';
$user_id = $_SESSION['user_id'] ?? 1; // Default to ID 1 if session ID is missing
$all_clients = [];

// 1. Handle the form submission for creating a new client
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['create_client'])) {
    
    // Sanitize and prepare core client data
    $company_name = $conn->real_escape_string($_POST['company_name']);
    $address = $conn->real_escape_string($_POST['address']);
    $company_email = $conn->real_escape_string($_POST['company_email']);
    $website = $conn->real_escape_string($_POST['website']);
    
    // Sanitize and prepare representative data
    $rep_name = $conn->real_escape_string($_POST['rep_name']);
    $rep_designation = $conn->real_escape_string($_POST['rep_designation']);
    $rep_email = $conn->real_escape_string($_POST['rep_email']);
    
    // --- PHONE NUMBER VALIDATION (FIX 1) ---
    $rep_phone_number_raw = trim($_POST['rep_phone_number']);
    $rep_phone_number = $conn->real_escape_string($rep_phone_number_raw);

    $is_phone_valid = empty($rep_phone_number_raw) || preg_match('/^[0-9\s\+\-]+$/', $rep_phone_number_raw);
    
    // Basic validation
    if (empty($company_name) || empty($rep_name)) {
        $client_message = "<p style='color:red;'>ERROR: Company Name and Representative Name are required fields.</p>";
    } elseif (!$is_phone_valid) {
        $client_message = "<p style='color:red;'>ERROR: Representative Phone Number must contain only numbers, spaces, hyphens, or a '+' sign.</p>";
    } else {
        
        // Prepare the SQL INSERT statement for the clients table
        $sql = "INSERT INTO clients (company_name, address, company_email, website, 
                                     rep_name, rep_designation, rep_email, rep_phone_number) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $conn->prepare($sql);
        
        if ($stmt === false) {
             $client_message = "<p style='color:red;'>Database prepare error: " . $conn->error . "</p>";
        } else {
            // Bind parameters (8 strings: ssssssss)
            $stmt->bind_param("ssssssss", 
                $company_name, $address, $company_email, $website, 
                $rep_name, $rep_designation, $rep_email, $rep_phone_number
            );
        
            if ($stmt->execute()) {
                $client_message = "<p style='color:green;'>Client <b>{$company_name}</b> successfully created and added to the portfolio.</p>";
            } else {
                $client_message = "<p style='color:red;'>Database insert failed: " . $stmt->error . "</p>";
            }
            $stmt->close();
        }
    }
}

// 2. Fetch all clients for display (FIX 2)
if (isset($conn)) {
    $sql_clients = "SELECT * FROM clients ORDER BY client_id DESC";
    $result_clients = $conn->query($sql_clients);
    
    if ($result_clients) {
        while ($row = $result_clients->fetch_assoc()) {
            $all_clients[] = $row;
        }
    } else {
        $client_message .= "<p style='color:red;'>ERROR: Could not fetch client list. Ensure the 'clients' table exists.</p>";
    }
}
?>

<div class="form-card" style="margin-top: 50px;">
    <h2 class="section-header">Client Portfolio Management</h2>
    <h3>1. Create New Client Portfolio Entry</h3>
    <p>Use this form to add a new client and their core portfolio details.</p>
    
    <?php echo $client_message; // Display success or error messages ?>

    <form action="admin_dashboard.php?page=manage_clients" method="post">
        
        <!-- --- Client Details Section --- -->
        <h4>Company Information</h4>
        
        <label for="company_name">Company Name (Required):</label>
        <input type="text" name="company_name" id="company_name" required>
        
        <label for="address">Address:</label>
        <input type="text" name="address" id="address">
        
        <label for="company_email">Company Email:</label>
        <input type="email" name="company_email" id="company_email">
        
        <label for="website">Website:</label>
        <input type="text" name="website" id="website">

        <!-- --- Representative Details Section --- -->
        <h4 style="margin-top: 30px;">Key Representative Information</h4>
        
        <label for="rep_name">Representative's Name (Required):</label>
        <input type="text" name="rep_name" id="rep_name" required>
        
        <label for="rep_designation">Designation:</label>
        <input type="text" name="rep_designation" id="rep_designation">
        
        <label for="rep_email">Representative Email:</label>
        <input type="email" name="rep_email" id="rep_email">
        
        <label for="rep_phone_number">Representative Phone Number (Numbers, +, -, spaces only):</label>
        <!-- --- PHONE NUMBER CLIENT-SIDE VALIDATION (FIX 1) --- -->
        <input type="tel" name="rep_phone_number" id="rep_phone_number" pattern="[0-9\s\+\-]+" title="Phone number must contain only numbers, spaces, hyphens, or a '+' sign.">
        
        <button type="submit" name="create_client">Create Client Portfolio</button>
    </form>
</div>

<!-- -------------------------------------------------------------------- -->
<!-- CLIENT LIST DISPLAY SECTION (FIX 2) -->
<!-- -------------------------------------------------------------------- -->

<div class="form-card data-container">
    <h2 class="section-header">2. All Clients in Portfolio (<?php echo count($all_clients); ?>)</h2>
    
    <?php if (!empty($all_clients)): ?>
        <?php display_client_table($all_clients); ?>
    <?php else: ?>
        <p>No clients have been added to the portfolio yet.</p>
    <?php endif; ?>
</div>

<?php
// Helper function to display the client table (FIX 2)
function display_client_table($clients) {
?>
    <table class="client-list-table">
        <thead>
            <tr>
                <th>ID</th>
                <th>Company Name</th>
                <th>Representative</th>
                <th>Rep. Contact</th>
                <th>Website</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($clients as $client): ?>
                <tr>
                    <td><?php echo htmlspecialchars($client['client_id']); ?></td>
                    <td>
                        <b><?php echo htmlspecialchars($client['company_name']); ?></b>
                        <br><small><?php echo htmlspecialchars($client['address'] ?? ''); ?></small>
                    </td>
                    <td>
                        <?php echo htmlspecialchars($client['rep_name']); ?>
                        <br><small><?php echo htmlspecialchars($client['rep_designation'] ?? ''); ?></small>
                    </td>
                    <td>
                        <small>Email: <?php echo htmlspecialchars($client['rep_email'] ?? 'N/A'); ?></small>
                        <br><small>Phone: <?php echo htmlspecialchars($client['rep_phone_number'] ?? 'N/A'); ?></small>
                    </td>
                    <td><a href="<?php echo htmlspecialchars($client['website']); ?>" target="_blank"><?php echo htmlspecialchars($client['website']); ?></a></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
<?php
}
?>
