<?php
// Assuming you have established a database connection
// Include necessary files and start session
require_once '../config.php';
session_start();
//var_dump($_SESSION);

// Check if the user is logged in as a system admin
$connection = mysqli_connect($db_host, $db_user, $db_password, $db_name);

// Set the character set to UTF-8 to avoid collation conflicts
mysqli_set_charset($connection, "utf8mb4");

if (!isset($_SESSION['userType']) || $_SESSION['userType'] !== 'member') {
    header('Location: ../indexX.php');
    exit;
}


// Check if preview data is available in session
if (!isset($_SESSION['previewData'])) {
    header('Location: bulk_upload.php');
    exit;
}

//retrieve induction date 
$induct_date = $_GET["induct_date"];


$previewData = $_SESSION['previewData'];
$nomineeCount = count($previewData);
//echo 'nomineeCount '.$nomineeCount;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Perform the defined tasks upon submission
    performTasks($previewData, $connection, $nomineeCount);

    // Clear the preview data from session
    unset($_SESSION['previewData']);

    // Redirect to the success page or previous page
    header('Location: success.php');
    exit;
}

// Function to perform the defined tasks upon submission
function performTasks($previewData, $connection, $nomineeCount) {
    // Connect to the database
    //require_once '../config.php';
	
	$induct_date = $_POST['induct_date'];

    // Retrieve the user ID
    $userId = $_SESSION['id'];
    //echo 'myid is '.$userId.'<p>'.count($previewData);
    $uploadtimestamp = date('YmdHis');
    $batchID = $uploadtimestamp;

    // Retrieve user information and credits
    $query = "SELECT zone, credits FROM users WHERE id = ?";
    $stmt = mysqli_prepare($connection, $query);
    mysqli_stmt_bind_param($stmt, "i", $userId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    $zoneMinistryCentre = "";
    $credit = 0;

    if ($result) {
        $row = mysqli_fetch_assoc($result);
        $zoneMinistryCentre = $row['zone'];
        $credit = $row['credits'];
    }

    // Check if the user has sufficient credits
    if ($credit > 0) {
        // Prepare the INSERT statement
        $insertQuery = "INSERT INTO NomineeData (nominee_code, title, nominee_name, zone_ministry_centre, group_name, church_name,date_joined_ministry,date_water_baptized,date_graduated_foundation,has_cell_nominee_training,user_id,batchid,inductionDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)";
        $stmt = mysqli_prepare($connection, $insertQuery);

        foreach ($previewData as $row) {
            // Generate a unique nominee code
            $nomineeCode = generateNomineeCode($connection);

            // Convert UTF-8 data to Latin1 to match database collation
            $convertedRow = array();
            foreach ($row as $value) {
                $convertedRow[] = mb_convert_encoding($value, 'ISO-8859-1', 'UTF-8');
            }
            
            // Convert other string parameters as well
            $convertedZoneMinistryCentre = mb_convert_encoding($zoneMinistryCentre, 'ISO-8859-1', 'UTF-8');
            $convertedNomineeCode = mb_convert_encoding($nomineeCode, 'ISO-8859-1', 'UTF-8');
            $convertedBatchID = mb_convert_encoding($batchID, 'ISO-8859-1', 'UTF-8');
            $convertedInductDate = mb_convert_encoding($induct_date, 'ISO-8859-1', 'UTF-8');

            // Bind parameters and execute the statement
            mysqli_stmt_bind_param($stmt, "sssssssssssss", $convertedNomineeCode, $convertedRow[0], $convertedRow[1], $convertedZoneMinistryCentre, $convertedRow[2], $convertedRow[3], $convertedRow[4], $convertedRow[5], $convertedRow[6], $convertedRow[7], $userId, $convertedBatchID, $convertedInductDate);
            $insertResult = mysqli_stmt_execute($stmt);

            if (!$insertResult) {
              //  echo "Error: " . mysqli_error($connection);
              echo "An Error Occurred! ";
            }
        }

        // Deduct credits from the user
        $updateQuery = "UPDATE users SET credits = credits - ? WHERE id = ?";
        $stmt = mysqli_prepare($connection, $updateQuery);
        //mysqli_stmt_bind_param($stmt, "ii", count($previewData), $userId);
        mysqli_stmt_bind_param($stmt, "ii", $nomineeCount, $userId);
        $updateResult = mysqli_stmt_execute($stmt);

        if (!$updateResult) {
            echo "Error: " . mysqli_error($connection);
            //echo "An Error Occurred! ";
        }

        // Close the database connection
        mysqli_close($connection);
    } else {
        echo "Insufficient credits.";
    }
}


// Helper function to sanitize input
function sanitizeInput($input) {
    // Check if the input is an array
    if (is_array($input)) {
        // Sanitize each element of the array
        foreach ($input as &$value) {
            $value = sanitizeInput($value);
        }
        unset($value); // unset the reference to the last element
    } else {
        // Escape special characters in the input string
        $input = mysqli_real_escape_string($connection, $input);

        // You can apply additional sanitization or validation logic here if needed
        // For example, you can use regular expressions or specific filters

        // Return the sanitized input
        return $input;
    }
}

// Function to process the uploaded CSV file and return the data for preview
function processCSVFile($csvFile) {
    $previewData = array();

    // Open the CSV file for reading
    if (($handle = fopen($csvFile, 'r')) !== false) {
        // Read the CSV file line by line
        while (($data = fgetcsv($handle)) !== false) {
            // Sanitize and store the row data
            $row = array();
            foreach ($data as $value) {
                $row[] = sanitizeInput($value);
            }
            $previewData[] = $row;
        }

        fclose($handle);
    }

    return $previewData;
}

// Function to generate the nominee code
function generateNomineeCode($connection) {
    $query = "SELECT MAX(nominee_id) AS max_id FROM NomineeData";
    $result = mysqli_query($connection, $query);
    $row = mysqli_fetch_assoc($result);
    $maxId = $row['max_id'];

    $newId = $maxId + 1;
    $formattedId = str_pad($newId, 7, '0', STR_PAD_LEFT);

    return "LW/LCM/IN/" . $formattedId;
}

include('../headerspecial.php');

?>

<!DOCTYPE html>
<html>
<head>
    <title>Data Preview</title>
</head>
<body>

<h2>Nominees Preview</h2>
<p> Please verify your data in the preview below before submission.</p>
<strong>Induction Date:</strong> <?php echo $induct_date;?><p>
    <form method="post">
	<input type="hidden" name="induct_date" value="<?php echo $induct_date; ?>">
        <table class="datatable">
            <thead>
                <tr>
                    <th>Title</th>
                    <th>Nominee Name</th>
                    <th>Group Name</th>
                    <th>Church Name</th>
                    <!-- Add more columns as needed -->
                </tr>
            </thead>
            <tbody>
                <?php foreach ($previewData as $row): ?>
                    <tr>
                        <td><?php echo $row[0]; ?></td>
                        <td><?php echo $row[1]; ?></td>
                        <td><?php echo $row[2]; ?></td>
                        <td><?php echo $row[3]; ?></td>
                        <!-- Display more columns as needed -->
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        <input type="submit" value="Submit">
    </form>
	
	<script>
$(document).ready(function() {
  $('.datatable').DataTable({
  "paging": true,
  "searching": true
});
});
</script>

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.css">

<script type="text/javascript" src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.js"></script>

</body>
</html>
