<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "rahisient";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    // Retrieve data for each day of the week
    $sql = "SELECT DAYNAME(seller_date) AS day, 
            SUM(total) AS total_sales, 
            SUM(amount_received) AS total_received, 
            SUM(total - amount_received) AS total_debt, 
            SUM(CASE WHEN payment_method = 'Cash' THEN amount_received ELSE 0 END) AS cash_total, 
            SUM(CASE WHEN payment_method = 'Mpesa' THEN amount_received ELSE 0 END) AS mpesa_total, 
            SUM(CASE WHEN payment_method = 'Hfc' THEN amount_received ELSE 0 END) AS hfc_total, 
            SUM(CASE WHEN payment_method = 'Equity' THEN amount_received ELSE 0 END) AS equity_total 
            FROM transaction_receipts 
            WHERE seller_date >= DATE(NOW()) - INTERVAL 1 WEEK
            GROUP BY DAYNAME(seller_date)";

    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // Prepare data for the chart
        $labels = array();
        $cash_data = array();
        $mpesa_data = array();
        $hfc_data = array();
        $equity_data = array();
    
        while($row = $result->fetch_assoc()) {
            array_push($labels, $row["day"]);
            array_push($cash_data, $row["cash_total"]);
            array_push($mpesa_data, $row["mpesa_total"]);
            array_push($hfc_data, $row["hfc_total"]);
            array_push($equity_data, $row["equity_total"]);
        }
       
    } else {
        echo "0 results";
    }
    $conn->close();
?>
