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

// Define start and end dates
$start_date = $_POST['startDate'];
$end_date = $_POST['endDate'];

// Initialize revenue variables
$swimoRevenue = 0;
$accommodationRevenue = 0;
$transactionRevenue = 0;
$totalRevenue = 0;

// Calculate Revenue from Swimo
$querySwimoRevenue = "SELECT SUM(total_cost) AS totalRevenue FROM swimo WHERE date_column BETWEEN ? AND ?";
$stmtSwimoRevenue = $conn->prepare($querySwimoRevenue);
$stmtSwimoRevenue->bind_param("ss", $start_date, $end_date);
$stmtSwimoRevenue->execute();
$resultSwimoRevenue = $stmtSwimoRevenue->get_result();
if ($resultSwimoRevenue->num_rows > 0) {
    $rowSwimoRevenue = $resultSwimoRevenue->fetch_assoc();
    $swimoRevenue = $rowSwimoRevenue['totalRevenue'];
}

// Calculate Revenue from Accommodation
$queryAccommodationRevenue = "SELECT SUM(rs.subtotal) AS totalRevenue 
                              FROM room_sales rs 
                              JOIN transaction_receipts tr ON rs.trans_id = tr.trans_id 
                              WHERE tr.seller_date BETWEEN ? AND ?";
$stmtAccommodationRevenue = $conn->prepare($queryAccommodationRevenue);
$stmtAccommodationRevenue->bind_param("ss", $start_date, $end_date);
$stmtAccommodationRevenue->execute();
$resultAccommodationRevenue = $stmtAccommodationRevenue->get_result();
if ($resultAccommodationRevenue->num_rows > 0) {
    $rowAccommodationRevenue = $resultAccommodationRevenue->fetch_assoc();
    $accommodationRevenue = $rowAccommodationRevenue['totalRevenue'];
}

// Calculate Revenue from Transaction Receipts (General Sales)
$queryTransactionRevenue = "SELECT SUM(total) AS totalRevenue
                            FROM transaction_receipts
                            WHERE seller_date BETWEEN ? AND ?";
$stmtTransactionRevenue = $conn->prepare($queryTransactionRevenue);
$stmtTransactionRevenue->bind_param("ss", $start_date, $end_date);
$stmtTransactionRevenue->execute();
$resultTransactionRevenue = $stmtTransactionRevenue->get_result();
if ($resultTransactionRevenue->num_rows > 0) {
    $rowTransactionRevenue = $resultTransactionRevenue->fetch_assoc();
    $transactionRevenue = $rowTransactionRevenue['totalRevenue'];
}

// Calculate Total Revenue
$totalRevenue = $swimoRevenue + $transactionRevenue;
$Gs = $transactionRevenue -  $accommodationRevenue;

// Initialize expense variables
$operationExpenses = 0;
$wages = 0;
$costOfGoodsSold=0;
// Calculate Operation Expenses
$queryOperationExpenses = "SELECT SUM(expense_amount) AS total_expenses
                           FROM expenses
                           WHERE date_incurred BETWEEN ? AND ?";
$stmtOperationExpenses = $conn->prepare($queryOperationExpenses);
$stmtOperationExpenses->bind_param("ss", $start_date, $end_date);
$stmtOperationExpenses->execute();
$resultOperationExpenses = $stmtOperationExpenses->get_result();
if ($resultOperationExpenses->num_rows > 0) {
    $rowOperationExpenses = $resultOperationExpenses->fetch_assoc();
    $operationExpenses = $rowOperationExpenses['total_expenses'];
}

// Calculate Wages (Salary Expenses)
$queryWages = "SELECT SUM(grossPay) AS total_gross_pay
               FROM table_payslip
               WHERE date BETWEEN ? AND ?";
$stmtWages = $conn->prepare($queryWages);
$stmtWages->bind_param("ss", $start_date, $end_date);
$stmtWages->execute();
$resultWages = $stmtWages->get_result();
if ($resultWages->num_rows > 0) {
    $rowWages = $resultWages->fetch_assoc();
    $wages = $rowWages['total_gross_pay'];
}

$queryCOGS = "SELECT SUM(total) AS totalCOGS FROM invoice WHERE date BETWEEN ? AND ?";
$stmtCOGS = $conn->prepare($queryCOGS);
$stmtCOGS->bind_param("ss", $start_date, $end_date);
$stmtCOGS->execute();
$resultCOGS = $stmtCOGS->get_result();
$rowCOGS = $resultCOGS->fetch_assoc();
$costOfGoodsSold = $rowCOGS['totalCOGS'];


// Calculate Gross Profit
$grossProfit = $totalRevenue - ($wages + $costOfGoodsSold);
$totalExpenses = ($operationExpenses + $wages + $costOfGoodsSold);

// Calculate Net Profit
$netProfit = $grossProfit - $operationExpenses;

// Output the Income Statement
echo '<div class="text-center mb-4">';
echo '        <h2 class="mb-0">Income Statement</h2>';
echo '        <hr class="my-4">';
echo '    </div>';

echo '    <div class="row">';
echo '        <div class="col-md-12">';
echo '            <!-- Assets -->';
echo '            <h3>Revenues</h3>';
echo '            <table class="table">';
echo '                <tbody>';
echo '                    <tr>';
echo '                        <th scope="row">General Sales</th>';
echo '                        <td>' . $Gs . '</td>';
echo '                        <td></td>'; 
echo '                    </tr>';
echo '                    <tr>';
echo '                        <th scope="row">Accommodation Sales</th>';
echo '                         <td>' . $accommodationRevenue . '</td>';
echo '                        <td></td>'; 
echo '                    </tr>';
echo '                    <tr>';
echo '                        <th scope="row">Swimming Sales</th>';
echo '                         <td>' . $swimoRevenue . '</td>';
echo '                        <td></td>'; 
echo '                    </tr>';
echo '                    <tr>';
echo '                        <th scope="row" style="color: red;"><b>Total Revenue</b></th>';
echo '                        <td style="color: red;"></td>'; // Empty cell with red text
echo '                        <td style="color: red;"><b>' . $totalRevenue . '</b></td>';
echo '                    </tr>';
echo '                </tbody>';
echo '            </table>';

echo '            <!-- Liabilities  -->';
echo '            <h3>Expenses</h3>';
echo '            <table class="table">';
echo '                <tbody>';
echo '                    <tr>';
echo '                        <th scope="row">Cost Of Goods Sold(COGS)</th>';
echo '                        <td>' . $costOfGoodsSold . '</td>';
echo '                        <td></td>';
echo '                    </tr>';
echo '                    <tr>';
echo '                        <th scope="row">Operation Expenses</th>';
echo '                        <td>' . $operationExpenses . '</td>';
echo '                        <td></td>';
echo '                    </tr>';
echo '                    <tr>';
echo '                        <th scope="row">Wages (Salary Expenses)</th>';
echo '                        <td>' . $wages . '</td>';
echo '                        <td></td>';
echo '                    </tr>';
echo '                    <tr>';
echo '                        <th style="color: red;" scope="row">Total Expenses</th>';
echo '                        <td></td>';
echo '                        <td style="color: red;"><b>'. $totalExpenses .'</b></td>';
echo '                    </tr>';
echo '                </tbody>';
echo '            </table>';

echo '            <h3>Gross Profit</h3>';
echo '            <table class="table">';
echo '                <tbody>';
echo '                    <tr>';
echo '                        <th style="color: green;" scope="row">Gross Profit</th>';
echo '                        <td></td>';
echo '                        <td style="color: green;"><b>' . $grossProfit .  '</b></td>';
echo '                    </tr>';
echo '                </tbody>';
echo '            </table>';
echo '            <h3>Net Profit</h3>';
echo '            <table class="table">';
echo '                <tbody>';
echo '                    <tr>';
echo '                        <th style="color: green;" scope="row">Net Income</th>';
echo '                        <td></td>';
echo '                        <td style="color: green;"><b>' . $netProfit .  '</b></td>';
echo '                    </tr>';
echo '                </tbody>';
echo '            </table>';
echo '        </div>';

echo '    </div>';

// Close prepared statements and connection
$stmtSwimoRevenue->close();
$stmtAccommodationRevenue->close();
$stmtTransactionRevenue->close();
$stmtOperationExpenses->close();
$stmtWages->close();
$conn->close();
?>
