<?php
// Include your database connection code
require '../../database/shikanisha.kts.php';

// Process the received dates
$startDate = $_POST['startDate'];
$endDate = $_POST['endDate'];

// Fetch the sum of total_cost from swimo table based on selected dates
$queryTotalCost = "SELECT SUM(total_cost) AS totalCostSum FROM swimo WHERE date_column BETWEEN '$startDate' AND '$endDate'";
$resultTotalCost = $conn->query($queryTotalCost);

// Check if the query was successful
if ($resultTotalCost) {
    $rowTotalCost = $resultTotalCost->fetch_assoc();
    $totalCostSum = $rowTotalCost['totalCostSum'];
}

// Fetch the sum of amount_received from transaction_receipts table based on selected dates
$queryAmountPaid = "SELECT SUM(amount_received) AS amountPaidSum FROM transaction_receipts WHERE seller_date BETWEEN '$startDate' AND '$endDate'";
$resultAmountPaid = $conn->query($queryAmountPaid);

// Check if the query was successful
if ($resultAmountPaid) {
    $rowAmountPaid = $resultAmountPaid->fetch_assoc();
    $amountPaidSum = $rowAmountPaid['amountPaidSum'];
}

// Calculate debt based on the difference between total and amount_received
$queryDebt = "SELECT (SUM(total) - SUM(amount_received)) AS debt FROM transaction_receipts WHERE seller_date BETWEEN '$startDate' AND '$endDate'";
$resultDebt = $conn->query($queryDebt);

// Check if the query was successful
if ($resultDebt) {
    $rowDebt = $resultDebt->fetch_assoc();
    $debt = $rowDebt['debt'];
}

// Fetch accounts payable from vendor_debts table
$queryAccountsPayable = "SELECT SUM(amount_owed) AS accountsPayable FROM vendor_debts";
$resultAccountsPayable = $conn->query($queryAccountsPayable);

// Check if the query was successful
if ($resultAccountsPayable) {
    $rowAccountsPayable = $resultAccountsPayable->fetch_assoc();
    $accountsPayable = $rowAccountsPayable['accountsPayable'];
}

// Fetch total profit from sales, products, and transaction_receipts tables
$queryTotalProfit = "SELECT SUM((s.selling_price - p.buying_price) * s.quantity) AS totalProfit
                     FROM sales s
                     JOIN products p ON s.product_id = p.product_id
                     JOIN transaction_receipts t ON s.trans_id = t.trans_id
                     WHERE t.seller_date BETWEEN '$startDate' AND '$endDate'";
$resultTotalProfit = $conn->query($queryTotalProfit);

// Check if the query was successful
if ($resultTotalProfit) {
    $rowTotalProfit = $resultTotalProfit->fetch_assoc();
    $totalProfit = $rowTotalProfit['totalProfit'];
}

// Query to get the sum of subtotal rows between the specified dates from room_sales table
$queryRoomSales = "SELECT SUM(rs.subtotal) AS total_subtotal
                   FROM room_sales rs
                   JOIN transaction_receipts tr ON rs.trans_id = tr.trans_id
                   WHERE tr.seller_date BETWEEN '$startDate' AND '$endDate'";
$resultRoomSales = $conn->query($queryRoomSales);

// Check if the query was successful
if ($resultRoomSales) {
    $rowRoomSales = $resultRoomSales->fetch_assoc();
    $total_subtotal = $rowRoomSales['total_subtotal'];
    
    // Calculate profit (30% of total_subtotal)
    $profit = $total_subtotal * 0.3;
}

// Close the database connection
$conn->close();

 echo '<div class="text-center mb-4">';
echo '        <h2 class="mb-0">Balance Sheet</h2>';
echo '        <hr class="my-4">';
echo '    </div>';

echo '    <div class="row">';
echo '        <div class="col-md-6">';
echo '            <!-- Assets -->';
echo '            <h3>Assets</h3>';
echo '            <table class="table">';
echo '                <tbody>';
echo '                    <tr>';
echo '                        <th scope="row">Cash & Cash Equivalents</th>';
echo '                        <td>' . ($totalCostSum + $amountPaidSum) . '</td>';
echo '                    </tr>';
echo '                    <tr>';
echo '                        <th scope="row">Accounts Receivable</th>';
echo '                        <td>' . $debt . '</td>';
// Add other Assets items as needed
echo '                </tbody>';
echo '            </table>';
echo '        </div>';

echo '        <div class="col-md-6">';
echo '            <!-- Liabilities  -->';
echo '            <h3>Liabilities</h3>';
echo '            <table class="table">';
echo '                <tbody>';
echo '                    <tr>';
echo '                        <th scope="row">Accounts Payable</th>';
echo '                        <td>' . $accountsPayable . '</td>';
echo '                    </tr>';
// Add other Liabilities and Equity items as needed
echo '                </tbody>';
echo '            </table>';

echo '            <h3>Equity</h3>';
echo '            <table class="table">';
echo '                <tbody>';
echo '                    <tr>';
echo '                        <th scope="row">Gross Profit</th>';
echo '                        <td>' . ($totalProfit + $profit) .  '</td>';
echo '                    </tr>';
// Add other Liabilities and Equity items as needed
echo '                </tbody>';
echo '            </table>';
echo '        </div>';

echo '    </div>';

?>
