<?php
// get the config file
include_once("../public/config.php");

//usage example
/*$dateFrom = isset($_REQUEST['datefrom']) ? $_REQUEST['datefrom'] : '';//2025-01-01
$dateTo = isset($_REQUEST['to']) ? $_REQUEST['to'] : '';//2025-12-12
//
$saveId = isset($_REQUEST['saveid']) ? (int)$_REQUEST['saveid'] : 0;//1
//
$storeId = isset($_REQUEST['storeid']) ? (int)$_REQUEST['storeid'] : 0;
$productId = isset($_REQUEST['productid']) ? (int)$_REQUEST['productid'] : 0;
//
$clientid = isset($_REQUEST['clientid']) ? (int)$_REQUEST['clientid'] : 0;
$supplierid = isset($_REQUEST['supplierid']) ? (int)$_REQUEST['supplierid'] : 0;

// Display search parameters at the top
echo "<div style='padding: 15px; margin-bottom: 20px; border-radius: 5px; background-color: #e3f2fd; border-left: 5px solid #2196f3;'>
        <h3 style='margin-top: 0; color: #1565c0;'>Search Parameters</h3>
        <div style='display: grid; grid-template-columns: repeat(auto-fill, minmax(250px, 1fr)); gap: 10px;'>
            <div><strong>Date Range:</strong> $dateFrom to $dateTo</div>
            <div><strong>Save ID:</strong> " . ($saveId > 0 ? $saveId : 'All') . "</div>
            <div><strong>Store ID:</strong> " . ($storeId > 0 ? $storeId : 'All') . "</div>
            <div><strong>Product ID:</strong> " . ($productId > 0 ? $productId : 'All') . "</div>
            <div><strong>Client ID:</strong> " . ($clientid > 0 ? $clientid : 'All') . "</div>
            <div><strong>Supplier ID:</strong> " . ($supplierid > 0 ? $supplierid : 'All') . "</div>
        </div>
      </div>";

reviewsaves($dateFrom, $dateTo, $saveId);
reviewstorereports($dateFrom, $dateTo, $storeId, $productId);
reviewsupplier($dateFrom, $dateTo, $supplierId);
reviewclient($dateFrom, $dateTo, $clientId);*/

///-- functions -------------------------------------------///
function reviewclient($dateFrom, $dateTo, $clientId)
{
    $queryString = "";

    if (isset($clientId) && $clientId > 0) {
        $queryString .= " AND clientid = $clientId";
    }

    if (isset($dateFrom) && !empty($dateFrom)) {
        $queryString .= " AND DATE(clientdebtchangedate) >= '" . $dateFrom . "'";
    }

    if (isset($dateTo) && !empty($dateTo)) {
        $queryString .= " AND DATE(clientdebtchangedate) <= '" . $dateTo . "'";
    }

    // Get distinct client IDs that match the criteria
    $clientIds = R::getCol("SELECT DISTINCT clientid FROM clientdebtchange WHERE 1=1 $queryString");
    $updatedCount = 0;

    foreach ($clientIds as $clientIdRow) {
        // Get all records for this client, ordered by date and ID
        $records = R::getAll("SELECT * FROM clientdebtchange WHERE clientid = $clientIdRow $queryString ORDER BY clientdebtchangedate, clientdebtchangeid");
        
        $previousRecord = null;
        if (!empty($records)) {
            $firstRecordId = $records[0]['clientdebtchangeid'];
            if($firstRecordId > 0) {
                $previousRecord = R::getRow("SELECT * FROM clientdebtchange WHERE clientid = $clientIdRow and clientdebtchangeid < $firstRecordId ORDER BY clientdebtchangeid DESC LIMIT 1");
            }
        }

        $previousBalance = 0;

        foreach ($records as $record) {
            $previousBalance = $previousRecord ? $previousRecord['clientdebtchangeafter'] : 0;
            $currentBefore = (float)$record['clientdebtchangebefore'];
            $changeAmount = (float)$record['clientdebtchangeamount'];
            $changeType = (int)$record['clientdebtchangetype'];

            // Calculate expected values
            $expectedBefore = $previousBalance;
            $expectedAfter = ($changeType === 0) ? $expectedBefore + $changeAmount : $expectedBefore - $changeAmount;

            // Round to 3 decimal places as per the database schema
            $expectedBefore = round($expectedBefore, 3);
            $expectedAfter = round($expectedAfter, 3);
            $currentAfter = (float)$record['clientdebtchangeafter'];

            // Check if values need to be updated
            if ($currentBefore != $expectedBefore || $currentAfter != $expectedAfter) {
                // Update the record with corrected values
                R::exec("UPDATE clientdebtchange SET clientdebtchangebefore = $expectedBefore, clientdebtchangeafter = $expectedAfter WHERE clientdebtchangeid = " . $record['clientdebtchangeid']);
                $updatedCount++;
            }

            $previousRecord = [
                'clientdebtchangeafter' => $expectedAfter,
                'clientdebtchangeid' => $record['clientdebtchangeid']
            ];
        }

        if (!empty($records) && $previousRecord && isset($previousRecord['clientdebtchangeafter'])) {
            $finalClientDebt = round((float)$previousRecord['clientdebtchangeafter'], 3);
            R::exec('UPDATE client SET clientdebt = ? WHERE clientid = ?', [$finalClientDebt, $clientIdRow]);                
        }
    }

    echo "<div style='padding: 10px; margin: 5px 0;text-align:left; border-radius: 4px; background-color: #e8f5e9; border-left: 4px solid #4caf50;'>
            <strong>Client Debt Verification:</strong> 
            <span style='color: #2e7d32;'>Updated $updatedCount records</span>
        </div>";
}

function reviewsupplier($dateFrom, $dateTo, $supplierId)
{
    $queryString = "";

    if (isset($supplierId) && $supplierId > 0) {
        $queryString .= " AND supplierid = $supplierId";
    }

    if (isset($dateFrom) && !empty($dateFrom)) {
        $queryString .= " AND DATE(supplierdebtchangedate) >= '" . $dateFrom . "'";
    }

    if (isset($dateTo) && !empty($dateTo)) {
        $queryString .= " AND DATE(supplierdebtchangedate) <= '" . $dateTo . "'";
    }

    // Get distinct supplier IDs that match the criteria
    $supplierIds = R::getCol("SELECT DISTINCT supplierid FROM supplierdebtchange WHERE 1=1 $queryString");
    $updatedCount = 0;

    foreach ($supplierIds as $supplierIdRow) {
        // Get all records for this supplier, ordered by date and ID
        $records = R::getAll("SELECT * FROM supplierdebtchange WHERE supplierid = $supplierIdRow $queryString ORDER BY supplierdebtchangedate, supplierdebtchangeid");
        
        $currentBefore = $previousBalance = 0;
        $previousRecord = null;
        if (!empty($records)) {
            $firstRecordId = $records[0]['supplierdebtchangeid'];
            if($firstRecordId > 0) {
                $previousRecord = R::getRow("SELECT * FROM supplierdebtchange WHERE supplierid = $supplierIdRow and supplierdebtchangeid < $firstRecordId ORDER BY supplierdebtchangedate, supplierdebtchangeid DESC LIMIT 1");
            }
        }

        foreach ($records as $record) {
            $currentBefore = $previousBalance = $previousRecord ? $previousRecord['supplierdebtchangeafter'] : 0;
            $changeAmount = (float)$record['supplierdebtchangeamount'];
            $changeType = (int)$record['supplierdebtchangetype'];

            // Calculate expected values
            $expectedBefore = $previousBalance;
            $expectedAfter = ($changeType === 0) ? $expectedBefore + $changeAmount : $expectedBefore - $changeAmount;

            // Round to 2 decimal places as per the database schema
            $expectedBefore = round($expectedBefore, 2);
            $expectedAfter = round($expectedAfter, 2);
            $currentAfter = (float)$record['supplierdebtchangeafter'];

            // Check if values need to be updated
            if ($currentBefore != $expectedBefore || $currentAfter != $expectedAfter) {
                // Update the record with corrected values
                R::exec("UPDATE supplierdebtchange SET supplierdebtchangebefore = $expectedBefore, supplierdebtchangeafter = $expectedAfter WHERE supplierdebtchangeid = " . $record['supplierdebtchangeid']);
                $updatedCount++;
            }

            $previousRecord = [
                'supplierdebtchangeafter' => $expectedAfter,
                'supplierdebtchangeid' => $record['supplierdebtchangeid']
            ];
        }

        if (!empty($records) && $previousRecord && isset($previousRecord['supplierdebtchangeafter'])) {
            $finalSupplierDebt = round((float)$previousRecord['supplierdebtchangeafter'], 2);
            R::exec('UPDATE supplier SET suppliercurrentDebt = ? WHERE supplierid = ?', [$finalSupplierDebt, $supplierIdRow]);
        }
    }

    echo "<div style='padding: 10px; margin: 5px 0;text-align:left; border-radius: 4px; background-color: #e8f5e9; border-left: 4px solid #4caf50;'>
            <strong>Supplier Debt Verification:</strong> 
            <span style='color: #2e7d32;'>Updated $updatedCount records</span>
        </div>";
}

function reviewstorereports($dateFrom, $dateTo, $storeId, $productId)
{
    $queryString = '';
    if (isset($storeId) && $storeId > 0) {
        $queryString .= " AND storeid = $storeId";
    }
    if (isset($productId) && $productId > 0) {
        $queryString .= " AND productid = $productId";
    }
    if (isset($dateFrom) && !empty($dateFrom)) {
        $queryString .= " AND DATE(storereportdate) >= '$dateFrom'";
    }
    if (isset($dateTo) && !empty($dateTo)) {
        $queryString .= " AND DATE(storereportdate) <= '$dateTo'";
    }
    $productStoreCombinations = R::getAll("SELECT DISTINCT productid, storeid 
         FROM storereport 
         WHERE 1=1 $queryString");

    $updatedCount = 0;

    foreach ($productStoreCombinations as $combo) {
        $productIdRow = $combo['productid'];
        $storeIdRow = $combo['storeid'];

        $records = R::getAll("SELECT * FROM storereport WHERE productid = $productIdRow AND storeid = $storeIdRow $queryString ORDER BY storereportdate, storereportid");
        $previousRecord = null;
        if (!empty($records)) {
            $firstRecordId = $records[0]['storereportid'];
            if($firstRecordId > 0) {
                $previousRecord = R::getRow("SELECT * FROM storereport WHERE productid = $productIdRow AND storeid = $storeIdRow AND storereportid < $firstRecordId ORDER BY storereportid DESC LIMIT 1");
            }
        }

        foreach ($records as $record) {
            $expectedBefore = $previousRecord ? $previousRecord['productafter'] : 0;
            $quantity = (float)$record['productquantity'];
            $reportType = (int)$record['storereporttype'];
            $expectedAfter = ($reportType === 0) ? $expectedBefore + $quantity : $expectedBefore - $quantity;
            $expectedBefore = round($expectedBefore, 3);
            $expectedAfter = round($expectedAfter, 3);

            if (
                (float)$record['productbefore'] != $expectedBefore ||
                (float)$record['productafter'] != $expectedAfter
            ) {
                R::exec("UPDATE storereport SET productbefore = $expectedBefore, productafter = $expectedAfter WHERE storereportid = " . $record['storereportid']);

                $updatedCount++;
            }

            $previousRecord = [
                'productafter' => $expectedAfter,
                'storereportid' => $record['storereportid']
            ];
        }

        if (!empty($records) && $previousRecord && isset($previousRecord['productafter'])) {
            $finalCombinationValue = round((float)$previousRecord['productafter'], 2);
            R::exec('UPDATE storedetail SET productquantity = ? WHERE productid = ? and storeid = ?', [$finalCombinationValue, $productIdRow, $storeIdRow]);
        }
    }

    // Update the style in store report verification
    echo "<div style='padding: 10px; margin: 5px 0;text-align:left; border-radius: 4px; background-color: #e8f5e9; border-left: 4px solid #4caf50;'>
            <strong>Store Report Verification:</strong> 
            <span style='color: #2e7d32;'>Updated $updatedCount records</span>
        </div>";
}

function reviewsaves($dateFrom, $dateTo, $saveId)
{
    $queryString = '';
    if (isset($saveId) && $saveId > 0) {
        $queryString .= " AND saveid = $saveId";
    }
    if (isset($dateFrom) && !empty($dateFrom)) {
        $queryString .= " AND DATE(savedailydate) >= '$dateFrom'";
    }
    if (isset($dateTo) && !empty($dateTo)) {
        $queryString .= " AND DATE(savedailydate) <= '$dateTo'";
    }

    $saveIds = R::getCol("SELECT DISTINCT saveid FROM savedaily WHERE 1=1 $queryString");

    $updatedCount = 0;

    foreach ($saveIds as $saveIdRow) {
        $records = R::getAll("SELECT * FROM savedaily WHERE saveid = $saveIdRow $queryString ORDER BY savedailydate, savedailyid");
        $previousRecord = null;
        if (!empty($records)) {
            $firstRecordId = $records[0]['savedailyid'];
            if($firstRecordId > 0) {
                $previousRecord = R::getRow("SELECT * FROM savedaily WHERE saveid = $saveIdRow and savedailyid < $firstRecordId ORDER BY savedailydate DESC, savedailyid DESC LIMIT 1");
            }
        }    
        
        foreach ($records as $record) {
            $expectedBefore = $previousRecord ? $previousRecord['savedailysaveafter'] : 0;

            $changeAmount = (float)$record['savedailychangeamount'];
            $changeType = (int)$record['savedailychangetype'];
            $expectedAfter = ($changeType === 0) ? $expectedBefore + $changeAmount : $expectedBefore - $changeAmount;
            $expectedBefore = round($expectedBefore, 2);
            $expectedAfter = round($expectedAfter, 2);

            if ((float)$record['savedailysavebefore'] != $expectedBefore || (float)$record['savedailysaveafter'] != $expectedAfter) {
                R::exec("UPDATE savedaily SET savedailysavebefore = $expectedBefore, savedailysaveafter = $expectedAfter WHERE savedailyid = " . $record['savedailyid']);
                $updatedCount++;
            }

            $previousRecord = [
                'savedailysaveafter' => $expectedAfter,
                'savedailyid' => $record['savedailyid']
            ];
        }

        if (!empty($records) && $previousRecord && isset($previousRecord['savedailysaveafter'])) {
            $finalSaveValue = round((float)$previousRecord['savedailysaveafter'], 2);
            R::exec('UPDATE save SET savecurrentvalue = ? WHERE saveid = ?', [$finalSaveValue, $saveIdRow]);
        }
    }

    // Update the style in save verification
    echo "<div style='padding: 10px; margin: 5px 0;text-align:left; border-radius: 4px; background-color: #e8f5e9; border-left: 4px solid #4caf50;'>
            <strong>Save Verification:</strong> 
            <span style='color: #2e7d32;'>Updated $updatedCount records</span>
        </div>";
}
?>