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

//echo "process_csv.php is being executed.";

include 'includes/dbconn.php';

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

// Get user_id and company_id from 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 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 the company name to be file-system friendly
$company_name = preg_replace('/[^A-Za-z0-9-]/', '', $company_name);

// Path to the CSV file
$csvFile = 'uploads/transactions - ' . $company_name . '-' . $company_id . '.csv';

// Check if the 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);

    // Fetch expense categories from the chart_of_accounts table
    $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;
        fclose($handle);
        exit;
    }

    // Create a file to store missing categories
    $missingCategoriesFile = fopen('COA-Cat.txt', 'w');
    if (!$missingCategoriesFile) {
        echo "Error creating missing categories file.";
        fclose($handle);
        exit;
    }

    // Read each line of the CSV file
    while (($data = fgetcsv($handle)) !== FALSE) {
        $date = DateTime::createFromFormat('m/d/Y', $data[1]);
        if (!$date) {
            echo "Error parsing date: " . $data[1];
            continue;
        }
        $date = $date->format('Y-m-d');

        $descriptionCSV = $data[2]; // Description from CSV column
        $categoryFromCSV = ucfirst(strtolower($data[3])); // Category from CSV column
        $amount = floatval($data[5]);
    
        // Convert negative amounts to positive
        $amount = abs($amount);
        
        // Debugging: Output the values before inserting
        // echo "Debug - Description: $descriptionCSV, Category: $categoryFromCSV, Amount: $amount\n";


        // Fetch category from chart_of_accounts based on $categoryFromCSV
        $stmtCategory = $conn->prepare("SELECT account_name FROM chart_of_accounts WHERE account_name = ?");
        $stmtCategory->bind_param("s", $categoryFromCSV);
        $stmtCategory->execute();
        $resultCategory = $stmtCategory->get_result();
        
        if ($rowCategory = $resultCategory->fetch_assoc()) {
            $category = $rowCategory['account_name'];
        } else {
            // Handle the case where the category doesn't exist in chart_of_accounts
            $missingCategoryMessage = "Category not found in chart_of_accounts: " . $categoryFromCSV . "\n";
            fwrite($missingCategoriesFile, $missingCategoryMessage);
            continue;
        }

        // Check if category is an expense and insert into the database
        if (in_array($category, $expenseCategories)) {
            // Define $captureDate if needed
            $captureDate = date('Y-m-d'); // Example: Use current date as capture date

            $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, $descriptionCSV, $date, $captureDate, $descriptionCSV, $amount, $category, $company_id)) {
                echo "Binding parameters failed: " . $stmt->error;
                continue;
            }


            if (!$stmt->execute()) {
                echo "Execute failed: " . $stmt->error;
                continue;
            }
        }
    }

    // Close the category statement
    $stmtCategory->close();

    fclose($handle);
    fclose($missingCategoriesFile);
} else {
    echo "Error opening file: $csvFile";
}

$conn->close();

// After the loop completes, display a single success message
echo "All records inserted successfully.\n";

// Redirect to exp_report.php
header("Location: exp_report.php");
exit;

$conn->close();
?>