<?php

$credittotal = 0.00;
$creditrecord = $postdata['creditrecord'];
// Decoding the JSON string to an array
$creditrecord = json_decode($creditrecord, true);
foreach ($creditrecord as  $item) {

    $credittotal += str_replace(",", "", $item['amount']);
    $ledgerid = $item['ledgerid'];
    $ledger_detail = GetBasicLedgerDetail($specific_db_conn, $item['ledgerid'], $workspace_id, $company_id, $branch_id, $book_id, $additionaldata);

    if ($ledger_detail[1]['ledger_main_ledgerhead'] == 'Bank Account' or $ledger_detail[1]['ledger_main_ledgerhead'] == 'Cash-in-Hand' and (key_exists('denominations', $item) or key_exists('instrument_details', $item))) {

        // call validateBankInstrument 
        $additional_data = array();
        $additional_data = ['base_currency_formal_name'];

        $base_currency = GetBasicCurrencyFormalName($specific_db_conn, $workspace_id, $company_id, $branch_id, $additional_data);
        foreach ($base_currency as $currency) {
            //  print_r($currency);
            $base_currency_formal_name = $currency['base_currency_formal_name'];
        }
        if (key_exists('instrument_details', $item)) {

            $instrument_details = $item['instrument_details'];
            // Decoding the JSON string to an array
            //$instrument_details = json_decode($instrument_details, true);
            validateBankInstrument($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $instrument_details, $base_currency_formal_name, $ledgerid, $book_opening_date, $book_closing_date);
        } else {

            $instrument_details = null;
        }

        if (key_exists('denominations', $item)) {
            $denominations = $item['denominations'];
            // Decoding the JSON string to an array
            //$denominations = json_decode($denominations, true);
            validateDenominations($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $denominations, $base_currency_formal_name, $item['amount']);
        } else {
            $denominations = null;
        }
    } else {
        $instrument_details = null;
        $denominations = null;
    }

    $transaction_record_rows[] = array(
        'ledgerid' => $item['ledgerid'], 'relative_amount' => - (floatval($item['amount'])),
        'ledger_main_ledgerhead' => $ledger_detail[1]['ledger_main_ledgerhead'], 'ledger_parent_group' => $ledger_detail[0]['ledger_parent_group'],
        'amount' => abs(floatval($item['amount'])), 'balancetype' => 'cr', 'instrument_details' => $instrument_details, 'denominations' => $denominations
    );

    validateTransactionTypeRecords($transactiontype, 'cr', $ledger_detail[1]['ledger_main_ledgerhead']);
}

$debitrecord = $postdata['debitrecord'];
// Decoding the JSON string to an array
$debitrecord = json_decode($debitrecord, true);
$debittotal = 0.00;
foreach ($debitrecord as $item) {

    $debittotal += $item['amount'];
    $ledgerid = $item['ledgerid'];
    $ledger_detail = GetBasicLedgerDetail($specific_db_conn, $item['ledgerid'], $workspace_id, $company_id, $branch_id, $book_id, $additionaldata);

    if ($ledger_detail[1]['ledger_main_ledgerhead'] == 'Bank Account'  or $ledger_detail[1]['ledger_main_ledgerhead'] == 'Cash-in-Hand' and (key_exists('instrument_details', $item) or
        key_exists('denominations', $item))) {
        // call validateBankInstrument 
        $additional_data = array();
        $additional_data = ['base_currency_formal_name'];

        $base_currency = GetBasicCurrencyFormalName($specific_db_conn, $workspace_id, $company_id, $branch_id, $additional_data);
        foreach ($base_currency as $currency) {
            $base_currency_formal_name = $currency['base_currency_formal_name'];
        }

        if (key_exists('instrument_details', $item)) {

            $instrument_details = $item['instrument_details'];
            // Decoding the JSON string to an array
            //$instrument_details = json_decode($instrument_details, true);
            validateBankInstrument($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $instrument_details, $base_currency_formal_name, $ledgerid, $book_opening_date, $book_closing_date);
        } else {

            $instrument_details = null;
        }

        if (key_exists('denominations', $item)) {

            $denominations = $item['denominations'];
            // Decoding the JSON string to an array
            //$denominations = json_decode($denominations, true);
            validateDenominations($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $denominations, $base_currency_formal_name, $item['amount']);
        } else {

            $denominations = null;
        }
    } else {
        $instrument_details = null;
        $denominations = null;
    }


    $transaction_record_rows[] = array(
        'ledgerid' => $item['ledgerid'], 'relative_amount' => (floatval($item['amount'])),
        'ledger_main_ledgerhead' => $ledger_detail[1]['ledger_main_ledgerhead'],
        'ledger_parent_group' => $ledger_detail[0]['ledger_parent_group'], 'amount' => abs(floatval($item['amount'])), 'balancetype' => 'dr',
        'instrument_details' => $instrument_details, 'denominations' => $denominations

    );

    validateTransactionTypeRecords($transactiontype, 'dr', $ledger_detail[1]['ledger_main_ledgerhead']);
}

if ($credittotal != $debittotal) {
    errorreturn('Credit and Debit total do not match.');
}

try {
    if (in_array($transactiontype, $transaction_numbering_system['manual'])) {
        // check for existing transaction number
        $sql = "Select count(*) as total from act_transactiondata where unique_id = ? and workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and id != ?";
        $check_transaction_uniqueid = $specific_db_conn->prepare($sql);
        $check_transaction_uniqueid->execute(array($unique_id, $workspace_id, $company_id, $branch_id, $book_id, $transaction_id));
        $result_uniqueid = $check_transaction_uniqueid->fetchAll();
        if ($result_uniqueid[0]['total'] > 0) {
            errorreturn('Transaction number already exists.');
        }
        $unique_id = $unique_id;
        $financial_year = null;
        $transaction_type_suffix = null;
    } else if (in_array($transactiontype, $transaction_numbering_system['automatic'])) {
        // fetching existing financial year
        $sql = "SELECT financial_year, unique_id from act_transactiondata where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and id = ?";
        $fetch_existing = $specific_db_conn->prepare($sql);
        $fetch_existing->execute(array($workspace_id, $company_id, $branch_id, $book_id, $transaction_id));
        $result_existing_transaction = $fetch_existing->fetchAll();
        $existing_financial_year = $result_existing_transaction[0]['financial_year'];
        $unique_id = $result_existing_transaction[0]['unique_id'];

        $transaction_type_suffix = extractFirstWords($transactiontype);

        // automatic changes only if prefix financial year is true and financial year changes
        if ($prefix_financial_year_bool == 1) {
            $financial_year = getFinancialYear($date, $financial_year_startdate);
            if ($existing_financial_year == $financial_year) {
                // no unique_id change is required
            } else {
                // build the new unique id
                $sql_get_max_number = "SELECT COALESCE(MAX(unique_id), 0) as unique_id from act_transactiondata where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and transactiontype = ? and financial_year = ?";
                $get_max_number = $specific_db_conn->prepare($sql_get_max_number);
                $get_max_number->execute(array($workspace_id, $company_id, $branch_id, $book_id, $transactiontype, $financial_year));
                $result_max_number = $get_max_number->fetchAll();
                if (count($result_max_number) != 1) {
                    $max_transaction_number = 0;
                } else {
                    $max_transaction_number = $result_max_number[0]['unique_id'];
                }

                $unique_id = $max_transaction_number + 1;
            }
        } else {
            $financial_year = null;
        }
    }


    $new_transactiondata = array('transactiontype' => $transactiontype, 'reference_number' => $reference_number, 'date' => $date, 'narration' => $narration, 'unique_id' => $unique_id, 'dispatch_id' => null, 'flexible_mode' => $flexible_mode, 'billing_address' => null, 'shipping_address' => null, 'e_way_bill' => $e_way_bill, 'financial_year' => $financial_year);


    // 'transactiontype' => $row[0]['transactiontype'], 'reference_number' => $row[0]['reference_number'], 'filesattached' => $row[0]['filesattached'], 'date' => $row[0]['date'],
    // 'narration' => $row[0]['narration'], 'reference_number' => $row[0]['reference_number'], 'dispatch_id' => $row[0]['dispatch_id'], 'flexible_mode' => $row[0]['flexible_mode'],
    // 'billing_address' => $row[0]['billing_address'], 'shipping_address' => $row[0]['shipping_address'], 'e_way_bill' => $row[0]['e_way_bill']


    $data_changed = false;
    $record_changed = false;
    $denomination_changed = false;
    $instrument_changed = false;


    $data_changed = checkTransactionData($specific_db_conn, $transaction_id, $workspace_id, $company_id, $branch_id, $book_id, $new_transactiondata, $transaction_numbering_system);
    $record_changed = checkTransactionRecord($specific_db_conn, $transaction_id, $workspace_id, $company_id, $branch_id, $book_id, $transaction_record_rows);
    foreach ($transaction_record_rows as $record) {
        $denomination_changed = checkTransactionDenomination($specific_db_conn, $transaction_id, $workspace_id, $company_id, $branch_id, $book_id, $record['ledgerid'], $record['denominations']);
        if ($denomination_changed == true)
            break;
        $instrument_changed = checkTransactionReference($specific_db_conn, $transaction_id, $workspace_id, $company_id, $branch_id, $book_id, $record['ledgerid'], $record['instrument_details']);
        if ($instrument_changed == true)
            break;
    }

    try {
        // Set the isolation level to READ COMMITTED
        $specific_db_conn->exec("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

        // Begin a transaction
        $specific_db_conn->beginTransaction();


        if ($data_changed == true or $record_changed != false or $instrument_changed != false or $denomination_changed != false) {
            $sql = "UPDATE act_transactiondata SET  transactiontype = ?, unique_id = ?, financial_year = ?, reference_number = ?, date=?, last_updated = ?, last_modified_by = ?, narration = ? WHERE  workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and id = ?";
            $specific_db_conn->prepare($sql)->execute(array($transactiontype, $unique_id, $financial_year, $reference_number, $date, time(), $username, $narration, $workspace_id, $company_id, $branch_id, $book_id, $transaction_id));
        }

        if ($record_changed != false or $instrument_changed != false or $denomination_changed != false) {

            if (!deleteOnlyTransactionRecords($specific_db_conn, $transaction_id, $workspace_id, $company_id, $branch_id, $book_id)) {
                $specific_db_conn->rollBack();
                errorreturn('Unable to update transaction. Kindly try later.');
            }

            $sql_update_ledgerdetails = "Update act_ledgerdetails SET ledger_current_bal = ledger_current_bal + ? where id = ? and workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? ";
            $stmt_update_ledgerdetails = $specific_db_conn->prepare($sql_update_ledgerdetails);

            $sql = "INSERT INTO act_transactionrecord(workspace_id,company_id,branch_id,book_id,transaction_id,ledgerid,main_ledgerhead,parent_ledgerhead,amount,balancetype,date) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $insert_into_transactionrecord =  $specific_db_conn->prepare($sql);


            foreach ($transaction_record_rows as $row1) {

                $ledgerid = $row1['ledgerid'];
                $main_ledgerhead = $row1['ledger_main_ledgerhead'];
                $parent_ledgerhead = $row1['ledger_parent_group'];
                $amount = $row1['amount'];
                $relative_amount = $row1['relative_amount'];
                $balancetype = $row1['balancetype'];

                $insert_into_transactionrecord->execute(array($workspace_id, $company_id, $branch_id, $book_id, $transaction_id, $ledgerid, $main_ledgerhead, $parent_ledgerhead, $amount, $balancetype, $date));

                // use update ledgerbalance here
                UpdateLedgerBalance($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $ledgerid, $relative_amount, $date, $main_ledgerhead, $parent_ledgerhead, 1);
            }
            //add bank instrument function
            foreach ($transaction_record_rows as $row1) {

                $ledgerid = $row1['ledgerid'];
                $amount = $row1['amount'];
                $balancetype = $row1['balancetype'];
                $instrument_details = $row1['instrument_details'];
                $denominations = $row1['denominations'];
                if ($row1['instrument_details'] != null) {

                    $temp_instrument_bank = AddBankInstrument($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $instrument_details, $ledgerid, $amount, $balancetype, $transactiontype, $date, $transaction_id);
                    if ($temp_instrument_bank == false) {
                        $specific_db_conn->rollBack();
                        errorreturn('Unable to save transaction. Kindly try again later.');
                    }
                    $sql = "Update act_transactionrecord set reference_id = ? where   workspace_id = ? and company_id =? and branch_id=? and book_id =? and transaction_id= ?  and ledgerid =? ";
                    $stmt = $specific_db_conn->prepare($sql);
                    $stmt->execute(array($temp_instrument_bank, $workspace_id, $company_id, $branch_id, $book_id, $transaction_id, $ledgerid));
                }

                if ($row1['denominations'] != null) {
                    $denominations_id = addDenominations($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $denominations, $ledgerid, $balancetype, $transaction_id, $amount);
                    if ($denominations_id == false) {
                        $specific_db_conn->rollBack();
                        errorreturn('Unable to save transaction. Kindly try again later.');
                    }

                    $sql = "Update act_transactionrecord set reference_id = ? where   workspace_id = ? and company_id =? and branch_id=? and book_id =? and transaction_id= ? and ledgerid =? ";
                    $stmt = $specific_db_conn->prepare($sql);
                    $stmt->execute(array($denominations_id, $workspace_id, $company_id, $branch_id, $book_id, $transaction_id, $ledgerid));
                }
            }
        }

        //update files
        if ($filesattached == null) {
            $filesattached = null;
        } else {

            if ($existing_filesattached === null) {

                $currentYear = date('Y');
                $currentMonth = date('n');
                $currentDay =  date('d');
                $directoryPathMain = "user_uploads/" .  'workspace_'  . $workspace_id . "/" . 'company_' .  $company_id . "/" . 'branch_' .  $branch_id . "/" . 'book_' .  $book_id . "/transaction_attachments/" . $currentYear . "/" . $currentMonth . "/";
            } else {
                $directoryPathMain = substr($existing_filesattached, 0, strrpos($existing_filesattached, '/') + 1);
            }
            $originalFileName = $_FILES['filesattached']['name'];
            $fileTmpName = $_FILES['filesattached']["tmp_name"];
            $fileSize = $_FILES['filesattached']["size"];
            $fileType = $_FILES['filesattached']["type"];
            $fileError = $_FILES['filesattached']["error"];
            $file_extension = strtolower(pathinfo($_FILES['filesattached']['name'], PATHINFO_EXTENSION));
            $size = filesize($fileTmpName);
            // Check if it's a valid image
            $allowedTypes = [
                'image/jpeg', 'image/png', 'image/gif', 'image/heic',
                'application/pdf',
                'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                'application/vnd.ms-excel',
                'application/msword',
                'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
            ]; // Allowed image types
            $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
            $maxFileSize = 2; // Maximum file size (2MB)

            checkFileValidation($fileSize, $fileType, $file_extension, $maxFileSize, $allowedTypes, $allowed_extensions);
        }

        if ($filesattached == null) {

            if ($remove_bool == 1 and $existing_filesattached != null) {
                // get user upload id
                $sql_get_userupload_id = "Select id from user_upload where workspace_id = ? and path = ? and category = ? and category_unique_id = ?";
                $get_useruploadid = $specific_db_conn->prepare($sql_get_userupload_id);
                $get_useruploadid->execute(array($workspace_id, $existing_filesattached, 'transaction_attachment', $transaction_id));
                $existing_upload_id = $get_useruploadid->fetchAll();

                if (count($existing_upload_id) == 1) {
                    // remove and update
                    awsS3UserUploadDelete($s3_finware_bucket, $finware_bucketName, $specific_db_conn, $workspace_id, $existing_filesattached, $existing_upload_id[0]['id'], true);
                    $sql = "UPDATE act_transactiondata SET filesattached = ? WHERE id = ? and workspace_id = ? and company_id = ? and branch_id= ? and book_id = ?";
                    $specific_db_conn->prepare($sql)->execute(array(null, $transaction_id, $workspace_id, $company_id, $branch_id, $book_id));
                } else {
                    // do nothing
                }
            }
        } else {
            // Generate the image name
            if ($existing_filesattached == null) {
                // insert
                awsS3UserUploadTransactionInsert($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $userid, $username, $size, $file_extension, $filesattached, $directoryPathMain, $transaction_id, $originalFileName);
            } else {
                //echo $originalFileName;
                awsS3UserUploadTransactionUpdate($specific_db_conn, $userid, $workspace_id, $company_id, $branch_id, $book_id, $size, $file_extension, $filesattached, $directoryPathMain, $existing_filesattached, $transaction_id, $originalFileName);
            }
        }

        $specific_db_conn->commit();
    } catch (Exception $e) {
        $specific_db_conn->rollBack();
        errorreturn('Error : ' . $e->getMessage());
    }


    successreturn(array('transaction_unique_id' => $unique_id, 'transaction_id' => $transaction_id, 'successmsg' => "Transaction updated successfully."));
} catch (Exception $e) {
    $specific_db_conn->rollBack();
    errorreturn("Error : " . $e->getMessage());
}
