<?php
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);

session_start();
header('Content-Type: application/json');

// Step 1: Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    ob_clean();
    echo json_encode(['status' => 'error', 'message' => 'আপনি লগইন অবস্থায় নেই']);
    exit;
}

// Step 2: Use Singleton Database instance to get the connection
require_once "../includes/Database.php";
$database = Database::getInstance(); // Ensure we are getting the singleton instance
$conn = $database->getConnection();  // This gets the database connection

// Step 3: Check if the connection is valid
if (!$conn) {
    echo json_encode(['status' => 'error', 'message' => 'Database connection is not available.']);
    exit;
}

$user_id = $_SESSION['user_id'];
$user_email = $_SESSION['email'] ?? null;

// Step 4: Get email from session or database if not in session
if (!$user_email) {
    error_log("Fetching user email from database for user_id: $user_id");
    $userStmt = $conn->prepare("SELECT email FROM users WHERE id = ?");
    $userStmt->bind_param("i", $user_id);
    $userStmt->execute();
    $userResult = $userStmt->get_result();
    
    if ($userResult->num_rows > 0) {
        $userData = $userResult->fetch_assoc();
        $user_email = $userData['email'];
    }
    $userStmt->close();
}

// Step 5: Fetch user balance
$sql = "SELECT balance FROM users WHERE id = ?";
$stmtUser = $conn->prepare($sql);
$stmtUser->bind_param("i", $user_id);
$stmtUser->execute();
$resultUser = $stmtUser->get_result();

if ($resultUser->num_rows > 0) {
    $userData = $resultUser->fetch_assoc();
    $user_balance = $userData['balance'];
} else {
    echo json_encode(['status' => 'error', 'message' => 'User not found.']);
    exit();
}
$stmtUser->close();

// Step 6: Fetch API configuration for TIN download
$api_config = $database->fetchRow("SELECT api_url, api_key, price FROM api_setup WHERE api_name = 'tin_download'");

if (!$api_config) {
    echo json_encode(['status' => 'error', 'message' => 'Configuration for TIN download not found.']);
    exit();
}

$deductionAmount = $api_config['price']; // Amount to deduct from balance
$api_url = $api_config['api_url']; // API URL from the database
$api_key = $api_config['api_key']; // API key to use for the external request

// Step 7: Check if balance is sufficient
if ($user_balance < $deductionAmount) {
    echo json_encode(['status' => 'error', 'message' => 'আপনার পর্যাপ্ত ব্যাল্যান্স নাই']);
    exit();
}

// Step 8: Check if TIN is provided in the POST request
if (!isset($_POST['tin']) || empty($_POST['tin'])) {
    echo json_encode(['status' => 'error', 'message' => 'TIN is required']);
    exit();
}

$tin = $_POST['tin'];
//var_dump($tin);
// Step 9: Construct the full API URL with the TIN and API key
$api_url =  $api_url. '?key=' . $api_key . '&pass='. $api_key .'&etin=' . $tin; //"https://investigateservices.com/api/tin.php"
//var_dump($api_url);
// Step 10: Fetch data from external API using cURL
error_log("Making cURL request to: $api_url");
$ch = curl_init($api_url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);  // Increase timeout to 30 seconds
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // Disable SSL verification (for debugging)
$response = curl_exec($ch);
$curlError = curl_error($ch);
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);

// Step 11: Debugging: Log the response and the error
error_log("API Response: " . $response);
error_log("cURL Error: " . $curlError);
error_log("HTTP Code: " . $httpCode);

// Step 12: Check for cURL errors
if ($curlError) {
    echo json_encode(['status' => 'error', 'message' => 'cURL error: ' . $curlError]);
    exit();
}

// Step 13: Check for HTTP status code other than 200
if ($httpCode !== 200) {
    echo json_encode(['status' => 'error', 'message' => 'API returned HTTP ' . $httpCode]);
    exit();
}

// Step 14: Decode the response
$data = json_decode($response, true);
//var_dump($response);
// Debugging: Log the decoded response
error_log("Decoded API Response: " . print_r($data, true));

// Step 15: Check if data is retrieved successfully
if (isset($data['TIN']) && !empty($data['TIN']) && isset($data['nameEnglish']) && !empty($data['nameEnglish'])) {
    // Now we have validated that the required fields are present
    $data = $data; // Data fetched from the API
    
    // Step 16: Check and format the date to 'YYYY-MM-DD' before inserting
    $date = DateTime::createFromFormat('d/m/Y', $data['date']); // Assuming the format is 'DD/MM/YYYY'
    if ($date) {
        $formattedDate = $date->format('Y-m-d'); // Convert to 'YYYY-MM-DD'
    } else {
        $formattedDate = '0000-00-00'; // Handle invalid date if necessary
    }

    // Step 17: Handle NULL values safely for officeAddress and phone
    $officeAddress = $data['officeAddress'] ?? NULL;  // Set to NULL if not available
    $phone = $data['phone'] ?? NULL;  // Set to NULL if not available

    // Step 18: Prepare SQL query to insert data into tin_data table
    $stmt = $conn->prepare("
        INSERT INTO tin_data (
            user_id, tin, nameEnglish, fatherNameEn, motherNameEn, taxZone, taxCircle, date, 
            status, previousTIN, presentAddress, permanentAddress, QR, created_at, officeAddress, phone
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?)
    ");

    // Bind parameters properly by reference
    $stmt->bind_param(
        "issssssssssssss", 
        $user_id, 
        $data['TIN'], 
        $data['nameEnglish'], 
        $data['fatherNameEn'], 
        $data['motherNameEn'], 
        $data['taxZone'], 
        $data['taxCircle'], 
        $formattedDate,  // Pass the formatted date here
        $data['status'], 
        $data['previousTIN'], 
        $data['presentAddress'], 
        $data['permanentAddress'], 
        $data['QR'], 
        $officeAddress,  // Pass officeAddress here
        $phone            // Pass phone here
    );

    if ($stmt->execute()) {
        // Step 19: Deduct balance from user
        $newBalance = $user_balance - $deductionAmount;
        $updateBalanceSql = "UPDATE users SET balance = ? WHERE id = ?";
        $stmtUpdate = $conn->prepare($updateBalanceSql);
        $stmtUpdate->bind_param("di", $newBalance, $user_id);
        $stmtUpdate->execute();
        $stmtUpdate->close();

        // Step 20: Insert work history
        $work_type = "অটো টিন";
        $about_order = "auto_tin";
        $order_time = date("Y-m-d H:i:s"); // Current time

        // Insert into `work_history` table
        $historyStmt = $conn->prepare("
            INSERT INTO work_history (
                user_id, order_id, work_type, work_rate, previous_balance, current_balance, status, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, 'completed', ?)
        ");
        
        // Generate a unique order ID
        $year_part = date('y'); // Last 2 digits of year
        $day_part = sprintf('%02d', date('z') % 100); // Day of year (0-365), take last 
        $random_part = sprintf('%02d', mt_rand(0, 99)); // Random 2 digits
        $order_id = $year_part . $day_part . $random_part;

        // Bind parameters for work history insert
        $historyStmt->bind_param(
            "issddds", 
            $user_id, 
            $order_id, 
            $work_type, 
            $deductionAmount,  // Deduction amount
            $user_balance,     // Previous balance
            $newBalance,       // New balance after deduction
            $order_time        // Created time
        );
        
        // Execute the work history insert
        if ($historyStmt->execute()) {
            echo json_encode(['status' => 'success', 'message' => 'Data inserted into work history successfully.']);
        } else {
            echo json_encode(['status' => 'error', 'message' => 'Error inserting work history: ' . $historyStmt->error]);
        }
        
        $historyStmt->close();

    } else {
        echo json_encode(['status' => 'error', 'message' => 'Error inserting data: ' . $stmt->error]);
    }

    $stmt->close();
} else {
    echo json_encode(['status' => 'error', 'message' => 'Invalid or incomplete data received from the external API.']);
}

exit();
