<?php
function renumberAutomaticTransaction($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $transactiontype = null)
{
    try {
        // Step 1: Create a temporary table with filtered rows
        $sql = "CREATE TEMPORARY TABLE temp_transactiondata AS
                SELECT id, date, transactiontype, 
                       IF(financial_year IS NULL, '0', financial_year) AS financial_year_placeholder, 
                       financial_year,
                       unique_id, transaction_type_suffix
                FROM act_transactiondata 
                WHERE workspace_id = ? AND 
                      company_id = ? AND 
                      branch_id = ? AND 
                      book_id = ?";
        if ($transactiontype !== null) {
            $sql .= " AND transactiontype = ?";
        }
        $stmt = $specific_db_conn->prepare($sql);
        $params = [
            $workspace_id,
            $company_id,
            $branch_id,
            $book_id
        ];
        if ($transactiontype !== null) {
            $params[] = $transactiontype;
        }
        $stmt->execute($params);

        // Step 2: Renumber unique_id using user-defined variables
        $specific_db_conn->exec("SET @num := 0");
        $specific_db_conn->exec("SET @transactiontype := ''");
        $specific_db_conn->exec("SET @financial_year := ''");

        $sql = "CREATE TEMPORARY TABLE temp_numbered AS
                SELECT id, 
                       @num := IF(@transactiontype = transactiontype AND @financial_year = financial_year_placeholder, @num + 1, 1) AS new_unique_id,
                       @transactiontype := transactiontype AS current_type,
                       @financial_year := financial_year_placeholder AS current_year
                FROM temp_transactiondata
                ORDER BY transactiontype, financial_year_placeholder, date, id";
        $specific_db_conn->exec($sql);

        // Step 3: Update the original table with the new unique_id values
        $sql = "UPDATE act_transactiondata a
                JOIN temp_numbered t
                ON a.id = t.id
                SET a.unique_id = t.new_unique_id 
                WHERE a.workspace_id = ? AND 
                      a.company_id = ? AND 
                      a.branch_id = ? AND 
                      a.book_id = ?";
        if ($transactiontype !== null) {
            $sql .= " AND a.transactiontype = ?";
        }
        $stmt = $specific_db_conn->prepare($sql);
        $params = [
            $workspace_id,
            $company_id,
            $branch_id,
            $book_id
        ];
        if ($transactiontype !== null) {
            $params[] = $transactiontype;
        }
        $stmt->execute($params);

        // Optional Step 4: Clean up (drop) the temporary tables if necessary
        $specific_db_conn->exec("DROP TEMPORARY TABLE IF EXISTS temp_transactiondata");
        $specific_db_conn->exec("DROP TEMPORARY TABLE IF EXISTS temp_numbered");

        return true;
    } catch (PDOException $e) {
        return false;
    }
}

function updateFinancialYearTransactions($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $transactiontype = null)
{
    try {
        $sql = "SELECT financial_year_startdate from act_books where workspace_id = ? and company_id = ? and branch_id = ? and id = ?";
        $fetch_book = $specific_db_conn->prepare($sql);
        $fetch_book->execute(array($workspace_id, $company_id, $branch_id, $book_id));
        $result_book = $fetch_book->fetchAll();

        $financial_year_startdate = $result_book[0]['financial_year_startdate'];

        $update_sql = "UPDATE act_transactiondata set financial_year = ? where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and id = ?";
        if ($transactiontype != null) {
            $update_sql .= " and transactiontype = ?";
        }
        $update_financial_year = $specific_db_conn->prepare($update_sql);

        $sql = "SELECT id, date from act_transactiondata where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ?";
        if ($transactiontype != null) {
            $sql .= " and transactiontype = ?";
        }
        $fetch_transactiondate = $specific_db_conn->prepare($sql);
        if ($transactiontype != null) {
            $array_params_sql = [$workspace_id, $company_id, $branch_id, $book_id, $transactiontype];
        } else {
            $array_params_sql = [$workspace_id, $company_id, $branch_id, $book_id];
        }
        $fetch_transactiondate->execute($array_params_sql);
        $result_transactiondate = $fetch_transactiondate->fetchAll();
        foreach ($result_transactiondate as $element) {
            $financial_year = getFinancialYear($element['date'], $financial_year_startdate);
            if ($transactiontype != null) {
                $array_params_update = [$financial_year, $workspace_id, $company_id, $branch_id, $book_id, $element['id'], $transactiontype];
            } else {
                $array_params_update = [$financial_year, $workspace_id, $company_id, $branch_id, $book_id, $element['id']];
            }
            $update_financial_year->execute($array_params_update);
        }
        return true;
    } catch (Exception $e) {
        return false;
    }
}

$transactiontypes_array_settings = ['journal', 'receipt', 'contra', 'payment', 'purchase', 'sales'];
