<?php
session_start(); // Start the session

include 'includes/dbconn.php'; // Include your database connection file

$recordInserted = false;  // Initialize the variable

ini_set('display_errors', 1);
error_reporting(E_ALL);

// Get user_id and company_id from the session
$user_id = $_SESSION['user_id'] ?? null;
$company_id = $_SESSION['company_id'] ?? null;

// Check if user_id and company_id are valid
if ($user_id === null || $company_id === null) {
    echo "User ID or Company ID is not set in the session.";
    exit;
}

// Fetch expense categories from the chart_of_accounts table and capitalize the first letter
$expenseCategories = [];
$result = $conn->query("SELECT account_name FROM chart_of_accounts WHERE account_type = 'Expense'");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $expenseCategories[] = ucfirst(strtolower($row['account_name'])); // Capitalize first letter
    }
    $result->free();
} else {
    echo "Error fetching categories: " . $conn->error;
}

// Fetch the company name using the company_id
$stmt = $conn->prepare("SELECT name FROM companies WHERE company_id = ?");
$stmt->bind_param("i", $company_id);
$stmt->execute();
$result = $stmt->get_result();
$company_name = '';

if ($row = $result->fetch_assoc()) {
    $company_name = $row['name'];
}
$stmt->close();

if (empty($company_name)) {
    echo "Company name not found.";
    exit;
}

// Sanitize and format the company name to create a valid filename (remove spaces and special characters)
$filename_company_name = preg_replace('/\s+/', '', $company_name); // Remove spaces
$filename_company_name = preg_replace('/[^A-Za-z0-9]/', '', $filename_company_name); // Remove special characters

// Path to the CSV file with the formatted company name
$csvFile = 'uploads/transactions - ' . $filename_company_name . '-' . $company_id . '.csv';

// Check if the CSV file exists
if (!file_exists($csvFile)) {
    echo "CSV file does not exist: $csvFile";
    exit;
}

// Open the file for reading
if (($handle = fopen($csvFile, 'r')) !== FALSE) {
    // Skip the header row
    fgetcsv($handle);

    // Read each line of the CSV file
    while (($data = fgetcsv($handle)) !== FALSE) {
        $date = DateTime::createFromFormat('d/m/Y', $data[1])->format('Y-m-d');
        $description = $data[2];
        $category = ucfirst(strtolower($data[3])); 
         $amount = abs((float)$data[5]); // Convert amount to absolute value

        $captureDate = date('Y-m-d'); // Current date as capture date

        // Check if category is an expense and insert into database
        if (in_array($category, $expenseCategories)) {
            $stmt = $conn->prepare("INSERT INTO expenses (user_id, expense_name, expense_date, capture_date, description, amount, category, company_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
            if (!$stmt) {
                echo "Prepare failed: " . $conn->error;
                continue;
            }

            if (!$stmt->bind_param("issssdsi", $user_id, $category, $date, $captureDate, $description, $amount, $category, $company_id)) {
                echo "Binding parameters failed: " . $stmt->error;
                continue;
            }
            
            if (!$stmt->execute()) {
                echo "Execute failed: " . $stmt->error;
                continue;
            } else {
                //echo "Record inserted successfully.\n";
                echo '<p>Records inserted successfully. Redirecting to dashboard in 3 seconds...</p>';
                echo '<script type="text/javascript">';
                echo 'setTimeout(function() {';
                echo '    window.location.href = "dashboard.php";';
                echo '}, 3000);'; // 3000 milliseconds = 3 seconds
                echo '</script>';
            }
        }
    }
    fclose($handle);
} else {
    echo "Error opening file: $csvFile";
}



$conn->close();
?>
