<?php
$servername = "localhost";
$username = "jaysfina_root";
$password = "2022wemadeit";
$dbname = "jaysfina_faida1";

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

$currentMonth = date('m');
$currentYear = date('Y');

// Check if a record for the current month already exists
$sqlCheckRecord = "SELECT * FROM income_statement WHERE month = $currentMonth AND year = $currentYear";
$resultCheckRecord = $conn->query($sqlCheckRecord);

if ($resultCheckRecord->num_rows > 0) {
    // Record exists, update it
    $sqlUpdate = "
        UPDATE income_statement
        SET
            total_invoices = (SELECT SUM(total_amount) FROM invoices WHERE MONTH(due_date) = $currentMonth AND YEAR(due_date) = $currentYear AND payment_status = 'Paid'),
            total_expenses = (SELECT SUM(amount) FROM expenses WHERE MONTH(expense_date) = $currentMonth AND YEAR(expense_date) = $currentYear),
            net_income = (SELECT SUM(total_amount) FROM invoices WHERE MONTH(due_date) = $currentMonth AND YEAR(due_date) = $currentYear AND payment_status = 'Paid') - (SELECT SUM(amount) FROM expenses WHERE MONTH(expense_date) = $currentMonth AND YEAR(expense_date) = $currentYear)
        WHERE
            month = $currentMonth AND year = $currentYear;
    ";

    if ($conn->query($sqlUpdate) === TRUE) {
        echo "Monthly task updated successfully.";
    } else {
        echo "Error updating monthly task: " . $conn->error;
    }
} else {
    // Record doesn't exist, insert a new one
    $sqlInsert = "
        INSERT INTO `income_statement` (month, year, total_invoices, total_expenses, net_income)
        SELECT
            MONTH(expense_date) AS month,
            YEAR(expense_date) AS year,
            (SELECT SUM(total_amount) FROM invoices WHERE MONTH(due_date) = month AND YEAR(due_date) = year AND payment_status = 'Paid') AS total_invoices,
            SUM(amount) AS total_expenses,
            (SELECT SUM(total_amount) FROM invoices WHERE MONTH(due_date) = month AND YEAR(due_date) = year AND payment_status = 'Paid') - SUM(amount) AS net_income
        FROM
            expenses
        WHERE
            YEAR(expense_date) = YEAR(NOW())
        GROUP BY
            month, year;
    ";

    if ($conn->query($sqlInsert) === TRUE) {
        echo "Monthly task inserted successfully.";
    } else {
        echo "Error inserting monthly task: " . $conn->error;
    }
}

$conn->close();
?>
