<?php
// Include your database connection configuration
include('db_config.php');
header('Access-Control-Allow-Origin: *');

header("Access-Control-Allow-Headers: Content-Type");
// Include your database connection code here (e.g., using PDO)

// Set headers to allow cross-origin requests (CORS)
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

// Check if the HTTP request method is GET
if ($_SERVER["REQUEST_METHOD"] === "GET") {
    // Check if the 'invoice_no' parameter is provided in the URL
    if (isset($_GET["invoice_no"])) {
        // Sanitize the input (prevent SQL injection)
        $invoiceNo = htmlspecialchars($_GET["invoice_no"]);

        // Query to retrieve invoice details based on the invoice number
        $sql = "SELECT b.bill_id, b.customer_name, b.customer_phone, b.customer_address, b.billing_date, bp.product_name, bp.serial_no_or_imei, bp.amount, bp.tax, bp.product_hsn
                FROM billing b
                JOIN billing_products bp ON b.bill_id = bp.bill_id
                WHERE b.bill_id = :invoice_no";

        try {
            // Prepare the SQL statement
            $stmt = $pdo->prepare($sql);

            // Bind the parameter
            $stmt->bindParam(":invoice_no", $invoiceNo, PDO::PARAM_INT);

            // Execute the query
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Check if any results were found
            if ($result) {
                // Initialize an array to store the transformed data
                $transformedData = array();
                $customerInfo=$result[0];
                // Iterate through the original data
                foreach ($result as $item) {
                    // Create a new item with the desired structure
                    $transformedItem = array(
                        "customer_name"=>$item['customer_name'],
                        "customer_phone"=>$item['customer_phone'],
                        "customer_address"=>$item['customer_address'],
                        "date"=>$item['billing_date'],
                        "hsn"=> $item['product_hsn'],
                        "productName" => $item["product_name"],
                        "serial" => $item["serial_no_or_imei"],
                        "sellingPrice" => floatval($item["amount"]), // Replace with the actual amount value
                        "tax" => floatval($item["tax"]), // Replace with the actual tax value
                    );
        
                    // Add the transformed item to the array
                    $transformedData[] = $transformedItem;
                }
               
                // Prepare the response
                $response = array(
                    "success" => true,
                    "message" => "Invoice details retrieved successfully",
                    "customer_name" => $customerInfo["customer_name"],
                    "customer_phone" => $customerInfo["customer_phone"],
                    "customer_address" => $customerInfo["customer_address"],
                    "billing_date" => $customerInfo["billing_date"],
                    "data" => $transformedData
                );
            } else {
                // No results found for the given invoice number
                $response = array(
                    "success" => false,
                    "message" => "Invoice not found"
                );
            }

            // Send the response as JSON
            echo json_encode($response);
        } catch (PDOException $e) {
            // Handle database error
            $response = array(
                "success" => false,
                "message" => "Database error: " . $e->getMessage()
            );
            echo json_encode($response);
        }
    } else if (isset($_GET["last"])) {
        // Query to retrieve the last 10 orders
        $sql = "SELECT * FROM billing ORDER BY bill_id DESC LIMIT 10";
    
        try {
            // Prepare the SQL statement
            $stmt = $pdo->prepare($sql);
    
            // Execute the query
            $stmt->execute();
    
            // Fetch the results
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
            // Check if any results were found
            if ($result) {
                // Initialize an array to store the transformed data
                $transformedData = array();
    
                // Iterate through the results
                foreach ($result as $item) {
                    // Create a new item with the desired structure
                    $transformedItem = array(
                        "bill_id" => $item['bill_id'],
                        "billed_by" => $item['billed_by'],
                        "customer_name" => $item['customer_name'],
                        "customer_phone" => $item['customer_phone'],
                        "customer_address" => $item['customer_address'],
                        "billing_date" => $item['billing_date'],
                        "total_amount" => floatval($item['total_amount']), // Replace with the actual total amount value
                        "total_tax" => floatval($item['total_tax']), // Replace with the actual total tax value
                    );
    
                    // Add the transformed item to the array
                    $transformedData[] = $transformedItem;
                }
    
                // Prepare the response
                $response = array(
                    "success" => true,
                    "message" => "Last 10 orders retrieved successfully",
                    "data" => $transformedData
                );
            } else {
                // No results found
                $response = array(
                    "success" => false,
                    "message" => "No orders found"
                );
            }
    
            // Send the response as JSON
            echo json_encode($response);
        } catch (PDOException $e) {
            // Handle database error
            $response = array(
                "success" => false,
                "message" => "Database error: " . $e->getMessage()
            );
            echo json_encode($response);
        }
    }else if(isset($_GET['month']) && isset($_GET['year']) && isset($_GET['pie'])){
        $currentMonth = $_GET['month'];
        $currentYear = $_GET['year'];

        try {
            // Prepare the SQL statement to retrieve total sales for the current month
            $stmt = $pdo->prepare("SELECT billed_by, SUM(total_amount) AS total_sales 
            FROM billing 
            WHERE MONTH(billing_date) = ? AND YEAR(billing_date) = ?
            GROUP BY billed_by");
            
            // Bind parameters
            $stmt->bindParam(1, $currentMonth, PDO::PARAM_INT);
            $stmt->bindParam(2, $currentYear, PDO::PARAM_INT);
            
            // Execute the query
            $stmt->execute();
            
            // Fetch the result
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Initialize response array
            $response = array();
            
            // Check if there are any sales for the current month
            if ($results) {
                foreach ($results as $result) {
                    $billedBy = $result['billed_by'];
                    $totalSales = $result['total_sales'];
                    $response[] = array(
                        'billed_by' => $billedBy,
                        'total_sales' => $totalSales
                    );
                }
            } else {
                $response['message'] = "No sales recorded for this month.";
            }
            
            // Return response as JSON
            echo json_encode($response);
        } catch (PDOException $e) {
            // Handle database error
            $response['error'] = "Error: " . $e->getMessage();
            echo json_encode($response);
        }
        
    }else if(isset($_GET['month']) && isset($_GET['year']) ){
        $currentMonth = $_GET['month'];
        $currentYear = $_GET['year'];
    
        try {
            // Prepare the SQL statement to retrieve total sales for the current month
            $stmt = $pdo->prepare("SELECT billed_by, SUM(total_amount) AS total_sales 
                FROM billing 
                WHERE MONTH(billing_date) = ? AND YEAR(billing_date) = ?
                GROUP BY billed_by");
            
            // Bind parameters
            $stmt->bindParam(1, $currentMonth, PDO::PARAM_INT);
            $stmt->bindParam(2, $currentYear, PDO::PARAM_INT);
            
            // Execute the query
            $stmt->execute();
            
            // Fetch the result
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Initialize response array
            $response = array();
            
            // Check if there are any sales for the current month
            if ($results) {
                // Initialize array to store sales data for each salesperson
                $salesData = array();
                
                foreach ($results as $result) {
                    $billedBy = $result['billed_by'];
                    $totalSales = $result['total_sales'];
                    
                    // Store sales data for each salesperson
                    $salesData[$billedBy] = $totalSales;
                }
                
                // Add sales data to the response
                $response = array(
                    'success'=>true,
                    'month' => intval($currentMonth),
                    'year' => intval($currentYear),
                    'totalSales' => array_sum($salesData),
                    'salesMan' => array_map(function ($salesperson, $sales) {
                        return array($salesperson => floatval($sales));
                    }, array_keys($salesData), $salesData)
                );
            } else {
                $response['success'] = false;
                $response['message'] = "No sales recorded for this month.";
            }
            
            // Return response as JSON
            echo json_encode($response);
        } catch (PDOException $e) {
            // Handle database error
            $response['error'] = "Error: " . $e->getMessage();
            echo json_encode($response);
        }
        
    }else {
        // 'invoice_no' parameter not provided
        $response = array(
            "success" => false,
            "message" => "Invoice number not provided"
        );
        echo json_encode($response);
    }
} 
// Check if the request method is POST
elseif ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Read the raw JSON data from the request body
    $json_data = file_get_contents('php://input');
    
    // Decode the JSON data into an associative array
    $data = json_decode($json_data, true);

    // Extract data fields
    
    $customerName = $data['customer_name'];
    $customerPhone = $data['customer_phone'];
    $customerAddress = $data['customer_address'];
    $billingDate = $data['billing_date'];
    $totalAmount = $data['total_amount'];
    $totalTax = $data['total_tax'];
    $by=$data['user'];

    // Prepare and execute the SQL query to insert the new bill
    $stmt = $pdo->prepare("INSERT INTO billing (bill_id, customer_name, customer_phone, customer_address, billing_date, total_amount, total_tax, billed_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    
    // Bind the parameters
    $tim=strtotime('now');
    $stmt->bindParam(1,$tim);
    $stmt->bindParam(2, $customerName);
    $stmt->bindParam(3, $customerPhone);
    $stmt->bindParam(4, $customerAddress);
    $stmt->bindParam(5, $billingDate);
    $stmt->bindParam(6, $totalAmount);
    $stmt->bindParam(7, $totalTax);
    $stmt->bindParam(8, $by);

    // Execute the query
    if ($stmt->execute()) {
        // Bill added successfully
        echo json_encode(["message" => "Bill added successfully","success"=>true,"bill_id"=>$tim]);
    } else {
        // Failed to add bill
        echo json_encode(["error" => "Failed to add bill"]);
    }
} else {
    // Invalid request method
    http_response_code(200); // Method Not Allowed
    echo json_encode(["error" => "Invalid request method"]);
}
?>