<?php
error_log($_SERVER['REQUEST_METHOD']);
 header("Access-Control-Allow-Origin: *");
 header("Access-Control-Allow-Methods: POST, GET, OPTIONS, PUT, DELETE");
 header("Access-Control-Allow-Headers: Content-Type, Authorization");
header('Content-Type: application/json');
include 'connection.php';
require 'vendor/autoload.php'; // Load Composer dependencies

use \Firebase\JWT\JWT;
use \Firebase\JWT\Key; 
use \Firebase\JWT\ExpiredException;

$secret_key = "1ED495A77B65435D317B3E6582E4C"; 
$issuer = "http://kaninfotech.in"; 
$audience = "http://xesstechlink.com"; 

// Check connection
if ($conn->connect_error) {
    echo json_encode(['success' => false, 'message' => 'Connection failed: ' . $conn->connect_error]);
    exit;
}

// Check if the required POST variables are set
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    header('Content-Type: application/json'); // Ensure JSON response

    $headers = apache_request_headers();
    if (isset($headers['Authorization'])) {
        $authHeader = $headers['Authorization'];
        list($jwt) = sscanf($authHeader, 'Bearer %s');
    } else {
        echo json_encode(["success" => false, "message" => "Authorization header missing"]);
        exit;
    }

    $type = isset($_POST['type']) ? $_POST['type'] : '';

    try {
        // Decode JWT token
        $decoded = JWT::decode($jwt, new Key($secret_key, 'HS256'));
        $company_id = $decoded->data->companyId;
        $uid = $decoded->data->uid;

        if (isset($_POST['date']) &&
            isset($_POST['name']) &&
            isset($_POST['under']) &&
            isset($_POST['opening_bal']) &&
            isset($_POST['opening_bal_type']) &&
            isset($_POST['status'])
        ) {
            $name = $_POST['name'];

            // Check if ledger name already exists
            $checkQuery = "SELECT COUNT(*) as count FROM ledger_entry WHERE ledger_name = ? AND com_id = ?";
            $checkStmt = $conn->prepare($checkQuery);
            $checkStmt->bind_param("si", $name, $company_id);
            $checkStmt->execute();
            $checkResult = $checkStmt->get_result();
            $checkRow = $checkResult->fetch_assoc();

            if ($checkRow['count'] > 0) {
                echo json_encode(["success" => false, "message" => "Name already exists!"]);
                exit;
            }

            // Capture other POST values
            $under = $_POST['under'];
            $opening_bal = $_POST['opening_bal'];
            $opening_bal_type = $_POST['opening_bal_type'];
            $status = $_POST['status'];

            // Convert date to yyyy-MM-dd format
            $date = $_POST['date'];
            $date = date("Y-m-d", strtotime($date));

            // Insert into ledger_entry
            $stmt = $conn->prepare("INSERT INTO ledger_entry (date, ledger_name, ledger_under, opening, acc_type, status, com_id, uid)
                                    VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

            if ($stmt) {
                $stmt->bind_param("ssssssss", $date, $name, $under, $opening_bal, $opening_bal_type, $status, $company_id, $uid);

                if ($stmt->execute()) {

                    // Determine credit/debit for ledger_day_wise
                    if ($opening_bal_type === 'Debit') {
                        $credit = $opening_bal;
                        $debit = 0.00;
                    } elseif ($opening_bal_type === 'Credit') {
                        $credit = 0.00;
                        $debit = $opening_bal;
                    } else {
                        echo json_encode(["success" => false, "message" => "Invalid opening balance type"]);
                        exit;
                    }

                    // Insert into ledger_day_wise
                    $ledger_sql1 = "INSERT INTO ledger_day_wise (com_id, uid, date, ledger, voucher_type, voucher_number, particulars, credit, debit)
                                    VALUES (?, ?, ?, ?, '', ' ', '', ?, ?)";

                    $ledgerStmt = $conn->prepare($ledger_sql1);

                    if ($ledgerStmt) {
                        $ledgerStmt->bind_param("ssssss", $company_id, $uid, $date, $name, $credit, $debit);

                        if ($ledgerStmt->execute()) {
                            echo json_encode([
                                "success" => true,
                                "message" => "Ledger inserted successfully into both tables"
                            ]);
                        } else {
                            echo json_encode([
                                "success" => false,
                                "message" => "Failed to insert into ledger_day_wise",
                                "error" => $ledgerStmt->error
                            ]);
                        }

                        $ledgerStmt->close();
                    } else {
                        echo json_encode(["success" => false, "message" => "Failed to prepare ledger_day_wise statement"]);
                    }

                } else {
                    echo json_encode([
                        "success" => false,
                        "message" => "Failed to insert into ledger_entry",
                        "error" => $stmt->error
                    ]);
                }

                $stmt->close();
            } else {
                echo json_encode(["success" => false, "message" => "Failed to prepare ledger_entry statement"]);
            }
        } else {
            echo json_encode(["success" => false, "message" => "Required fields are missing"]);
        }

    } catch (ExpiredException $e) {
        echo json_encode(["success" => false, "message" => "Token has expired"]);
    } catch (Exception $e) {
        echo json_encode(["success" => false, "message" => "Failed to decode token: " . $e->getMessage()]);
    }
}

/*if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    header('Content-Type: application/json'); // Ensure JSON response

    $headers = apache_request_headers();
    if (isset($headers['Authorization'])) {
        $authHeader = $headers['Authorization'];
        list($jwt) = sscanf($authHeader, 'Bearer %s');
    } else {
        echo json_encode(["success" => false, "message" => "Authorization header missing"]);
        exit;
    }

    $type = isset($_POST['type']) ? $_POST['type'] : '';

    try {
        // Decode JWT token
        $decoded = JWT::decode($jwt, new Key($secret_key, 'HS256'));
        $company_id = $decoded->data->companyId;
        $uid = $decoded->data->uid;

        // Required fields
        if (
            isset($_POST['date']) &&
            isset($_POST['name']) &&
            isset($_POST['under']) &&
            isset($_POST['opening_bal']) &&
            isset($_POST['opening_bal_type']) &&
            isset($_POST['status'])
        ) {
            $name = $_POST['name'];

            // Check if ledger name already exists
            $checkQuery = "SELECT COUNT(*) as count FROM ledger_entry WHERE ledger_name = ? AND com_id = ?";
            $checkStmt = $conn->prepare($checkQuery);
            $checkStmt->bind_param("si", $name, $company_id);
            $checkStmt->execute();
            $checkResult = $checkStmt->get_result();
            $checkRow = $checkResult->fetch_assoc();

            if ($checkRow['count'] > 0) {
                echo json_encode(["success" => false, "message" => "Name already exists!"]);
                exit;
            }

            // Capture other POST values
            $under = $_POST['under'];
            $opening_bal = $_POST['opening_bal'];
            $opening_bal_type = $_POST['opening_bal_type'];
            $status = $_POST['status'];

            // Convert date to yyyy-MM-dd format
            $date = date("Y-m-d", strtotime($_POST['date']));

            // Insert into ledger_entry ONLY
            $stmt = $conn->prepare("INSERT INTO ledger_entry 
                (date, ledger_name, ledger_under, opening, acc_type, status, com_id, uid)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

            if ($stmt) {
                $stmt->bind_param(
                    "ssssssss",
                    $date,
                    $name,
                    $under,
                    $opening_bal,
                    $opening_bal_type,
                    $status,
                    $company_id,
                    $uid
                );

                if ($stmt->execute()) {
                    echo json_encode([
                        "success" => true,
                        "message" => "Ledger inserted successfully"
                    ]);
                } else {
                    echo json_encode([
                        "success" => false,
                        "message" => "Failed to insert into ledger_entry",
                        "error" => $stmt->error
                    ]);
                }

                $stmt->close();
            } else {
                echo json_encode(["success" => false, "message" => "Failed to prepare ledger_entry statement"]);
            }
        } else {
            echo json_encode(["success" => false, "message" => "Required fields are missing"]);
        }

    } catch (ExpiredException $e) {
        echo json_encode(["success" => false, "message" => "Token has expired"]);
    } catch (Exception $e) {
        echo json_encode(["success" => false, "message" => "Failed to decode token: " . $e->getMessage()]);
    }
}*/




else if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    $headers = apache_request_headers();
    if (isset($headers['Authorization'])) {
        $authHeader = $headers['Authorization'];
        list($jwt) = sscanf($authHeader, 'Bearer %s');
    } else {
        echo json_encode(["success" => false, "message" => "Authorization header missing"]);
        exit;
    }

    try {
        $decoded = JWT::decode($jwt, new Key($secret_key, 'HS256'));
        $company_id = $decoded->data->companyId; 
        $uid = $decoded->data->uid;

        if (isset($_GET['type'])) {
            $type = $_GET['type'];
            switch ($type) {
                
    case 'ledger_filter':
    // Clear previous output to prevent malformed JSON
    ob_clean();
    header('Content-Type: application/json');

    error_reporting(E_ALL);
    ini_set('display_errors', 1);

    if (!isset($_GET['ledger_name'])) {
        echo json_encode(["success" => false, "message" => "Ledger name not provided"]);
        exit;
    }

    $ledger_name = $conn->real_escape_string($_GET['ledger_name']);

    if (!isset($company_id)) {
        echo json_encode(["success" => false, "message" => "Company ID not provided"]);
        exit;
    }

    try {
    // Step 1: Fetch active year from yearposting
    $yearQuery = "SELECT year FROM yearposting WHERE company_id = '$company_id' AND status = 'Active' LIMIT 1";
    $yearResult = $conn->query($yearQuery);

    if (!$yearResult || $yearResult->num_rows === 0) {
        throw new Exception("Active year not found for the company");
    }

    $yearRow = $yearResult->fetch_assoc();
    $activeYear = $yearRow['year']; // Example: '2025-2026'

    // Step 2: Convert to from_date and to_date
    list($fromYear, $toYear) = explode('-', $activeYear);
    $fromDate = "$fromYear-04-01";
    $toDate = "$toYear-03-31";

    // Step 3: Fetch ledger_entry data
   // $ledgerQuery = "SELECT * FROM ledger_entry WHERE company_id = '$company_id' AND name LIKE '%$ledger_name%'";
   $ledgerQuery = "SELECT * FROM ledger_entry 
    WHERE company_id = '$company_id' 
    AND name LIKE '%$ledger_name%' 
    AND date >= '$fromDate' 
    AND date <= '$toDate'";
    $ledgerResult = $conn->query($ledgerQuery);

    if (!$ledgerResult) {
        throw new Exception("Ledger Query Failed: " . $conn->error);
    }

    $ledgerData = $ledgerResult->fetch_all(MYSQLI_ASSOC);

    // Step 4: Fetch day-wise data based on active year date range
    $dayWiseQuery = "SELECT 
        DATE_FORMAT(date, '%Y-%m') AS month, 
        SUM(credit) AS total_credit, 
        SUM(debit) AS total_debit
    FROM ledger_day_wise
    WHERE company_id = '$company_id' 
        AND ledger LIKE '%$ledger_name%' 
        AND date >= '$fromDate' 
        AND date <= '$toDate'
        AND voucher_type <> 'opening balance'
    GROUP BY month
    ORDER BY month ASC";

    $dayWiseResult = $conn->query($dayWiseQuery);

    if (!$dayWiseResult) {
        throw new Exception("Day Wise Query Failed: " . $conn->error);
    }

    $dayWiseData = $dayWiseResult->fetch_all(MYSQLI_ASSOC);

    echo json_encode([
        "success" => true,
        "ledger_data" => $ledgerData,
        "day_wise_data" => $dayWiseData
    ]);
    exit;

} catch (Exception $e) {
    echo json_encode(["success" => false, "message" => $e->getMessage()]);
    exit;
}

    break;





                
               /* case 'ledger_filter':
                    if (!isset($_GET['ledger_name'])) {
                        echo json_encode(["success" => false, "message" => "Ledger name not provided"]);
                        exit;
                    }
                    $ledger_name = $conn->real_escape_string($_GET['ledger_name']);
                    $sql = "SELECT * FROM ledger_entry WHERE company_id = '$company_id' AND name LIKE '%$ledger_name%'";
                    break;*/
                    
                

                case 'ledger_fetch':
                    $sql = "SELECT DISTINCT name FROM ledger_entry WHERE company_id = '$company_id'";
                    break;
                    
                case 'opening_balance':
                    $sql = "SELECT * FROM ledger_entry WHERE name = ? AND company_id = '$company_id'";
                    break;
                
                case 'trail_balance':
                if (!isset($_GET['date'])) {
                    echo json_encode(["success" => false, "message" => "date not provided"]);
                    exit;
                }
                $date = $conn->real_escape_string($_GET['date']);
                 $sql = "SELECT 
                        le.name, 
                        le.opening_bal, 
                        le.opening_bal_type, 
                        le.under,
                        COALESCE((
                            SELECT SUM(he.hpAmt)
                            FROM hp_entry he 
                            WHERE he.company_id = le.company_id 
                              AND he.loanDate BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d') 
                              AND '$date'
                        ), 0) AS totalhpAmt,
                        COALESCE((
                            SELECT SUM(lo.loanAmt) 
                            FROM loan lo 
                            WHERE lo.company_id = le.company_id 
                              AND lo.loanDate BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d') 
                              AND '$date'
                        ), 0) AS totalLoanAmt,
                        COALESCE((
                            SELECT SUM(hr.reduce_int_due) 
                            FROM hp_rec hr
                            WHERE hr.company_id = le.company_id
                              AND hr.cheque_date BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d')
                              AND '$date'
                        ), 0) AS totalReduceIntDue,
                        COALESCE((
                            SELECT SUM(hr.total_due_int) 
                            FROM hp_rec hr
                            WHERE hr.company_id = le.company_id
                              AND hr.cheque_date BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d')
                              AND '$date'
                        ), 0) AS totalDueInt,
                        COALESCE((
                            SELECT SUM(lr.loanAmount)
                            FROM loan_rec lr
                            WHERE lr.company_id = le.company_id
                              AND lr.dueFrom BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d')
                              AND '$date'
                        ), 0) AS totalLoanAmount,
                        COALESCE((
                            SELECT SUM(lr.payAmount)
                            FROM loan_rec lr
                            WHERE lr.company_id = le.company_id
                              AND lr.dueFrom BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d')
                              AND '$date'
                        ), 0) AS totalPayAmount,
                        COALESCE((
                            SELECT SUM(lr.principalAmt2)
                            FROM loan_rec lr
                            WHERE lr.company_id = le.company_id
                              AND lr.dueFrom BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d')
                              AND '$date'
                        ), 0) AS totalPrincipalAmt2,
                        COALESCE((
                        SELECT SUM(d.deposit_amt)
                        FROM deposit_entry d
                        WHERE d.company_id = le.company_id
                          AND d.deposit_type = 'Deposit'
                          AND d.cash_acc = 'Cash A/c'
                          AND d.deposit_date BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d')
                          AND '$date'
                    ), 0) AS totalDepositAmtDepCash
                    FROM ledger_entry le
                    WHERE le.company_id = '$company_id'
                      AND le.date BETWEEN STR_TO_DATE(
                                              CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                              '%Y-%m-%d') 
                                      AND '$date'
                    GROUP BY le.name, le.opening_bal, le.opening_bal_type, le.under;";
                break;
                case 'hp_data':
                if (!isset($_GET['date'])) {
                    echo json_encode(["success" => false, "message" => "date not provided"]);
                    exit;
                }
                $date = $conn->real_escape_string($_GET['date']);
                $sql = "SELECT 
                he.customerName AS hp_customerName,
                he.accNo2 AS hp_accNo2,
                he.hpAmt - COALESCE((
                    SELECT SUM(hr.reduce_int_due) 
                    FROM hp_rec hr
                    WHERE hr.company_id = he.company_id
                      AND hr.cheque_date BETWEEN STR_TO_DATE(
                        CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                        '%Y-%m-%d')
                      AND '$date'
                      AND hr.accNo2 = he.accNo2
                ), 0) AS hp_hpAmtDue
            FROM hp_entry he
            WHERE he.company_id = '$company_id'
              AND he.loanDate BETWEEN STR_TO_DATE(
                  CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                  '%Y-%m-%d') 
              AND '$date';";
                break;
                case 'loan_data':
                    if (!isset($_GET['date'])) {
                        echo json_encode(["success" => false, "message" => "date not provided"]);
                        exit;
                    }
                    $date = $conn->real_escape_string($_GET['date']);
                    $sql = "SELECT 
                                lo.accNo2 AS loan_accNo2,
                                lo.customerName AS loan_customerName,
                                lo.loanAmt AS loan_loanAmt
                            FROM loan lo
                            WHERE lo.company_id = '$company_id'
                              AND lo.loanDate BETWEEN STR_TO_DATE(
                                    CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                    '%Y-%m-%d') 
                              AND '$date';";
                    
                    break;
                     case 'deposit_cash':
                    if (!isset($_GET['date'])) {
                        echo json_encode(["success" => false, "message" => "date not provided"]);
                        exit;
                    }
                    $date = $conn->real_escape_string($_GET['date']);
                    $sql = "SELECT 
                        SUM(de.deposit_amt) AS totalCashDepositAmt
                    FROM deposit_entry de
                    WHERE de.company_id = '$company_id'
                      AND de.deposit_date BETWEEN STR_TO_DATE(
                          CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                          '%Y-%m-%d') 
                      AND '$date'
                      AND de.deposit_type = 'Deposit'
                      AND de.deposit_mode = 'Cash';";
                    
                    break;
                    case 'deposit_bank':
                    if (!isset($_GET['date'])) {
                        echo json_encode(["success" => false, "message" => "date not provided"]);
                        exit;
                    }
                    $date = $conn->real_escape_string($_GET['date']);
                    $sql = "SELECT 
                        SUM(de.deposit_amt) AS totalBankDepositAmt
                    FROM deposit_entry de
                    WHERE de.company_id = '$company_id'
                      AND de.deposit_date BETWEEN STR_TO_DATE(
                          CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                          '%Y-%m-%d') 
                      AND '$date'
                      AND de.deposit_type = 'Deposit'
                      AND de.deposit_mode = 'Cheque';";
                    break;
                    
                    case 'capital_cash':
                    if (!isset($_GET['date'])) {
                        echo json_encode(["success" => false, "message" => "date not provided"]);
                        exit;
                    }
                    $date = $conn->real_escape_string($_GET['date']);
                    $sql = "SELECT 
                        SUM(de.deposit_amt) AS totalCashCapitalAmt
                    FROM deposit_entry de
                    WHERE de.company_id = '$company_id'
                      AND de.deposit_date BETWEEN STR_TO_DATE(
                          CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                          '%Y-%m-%d') 
                      AND '$date'
                      AND de.deposit_type = 'Capital'
                      AND de.deposit_mode = 'Cash';";
                    break;
                    case 'capital_bank':
                    if (!isset($_GET['date'])) {
                        echo json_encode(["success" => false, "message" => "date not provided"]);
                        exit;
                    }
                    $date = $conn->real_escape_string($_GET['date']);
                    $sql = "SELECT 
                        SUM(de.deposit_amt) AS totalBankCapitalAmt
                    FROM deposit_entry de
                    WHERE de.company_id = '$company_id'
                      AND de.deposit_date BETWEEN STR_TO_DATE(
                          CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                          '%Y-%m-%d') 
                      AND '$date'
                      AND de.deposit_type = 'Capital'
                      AND de.deposit_mode = 'Cheque';";
                    break;
                    
               case 'profit_loss':
                if (!isset($_GET['date'])) {
                    echo json_encode(["success" => false, "message" => "date not provided"]);
                    exit;
                }
                $date = $conn->real_escape_string($_GET['date']);
                $sql = "SELECT 
                            COALESCE(SUM(lo.docCharge2), 0) AS totalDocCharge2,
                            COALESCE((
                                SELECT SUM(hr.total_due_int) 
                                FROM hp_rec hr
                                WHERE hr.company_id = lo.company_id
                                AND hr.cheque_date BETWEEN STR_TO_DATE(
                                    CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                    '%Y-%m-%d')
                                AND '$date'
                            ), 0) AS totalDueInt,
                            COALESCE((
                                SELECT SUM(hr.penalty_amt) 
                                FROM hp_rec hr
                                WHERE hr.company_id = lo.company_id
                                AND hr.cheque_date BETWEEN STR_TO_DATE(
                                    CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                    '%Y-%m-%d')
                                AND '$date'
                            ), 0) AS totalPenaltyAmt,
                            COALESCE((
                                SELECT SUM(he.docCharge2)
                                FROM hp_entry he
                                WHERE he.company_id = lo.company_id
                                AND he.loanDate BETWEEN STR_TO_DATE(
                                    CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                    '%Y-%m-%d')
                                AND '$date'
                            ), 0) AS totalDocCharge2HpEntry,
                            COALESCE((
                                SELECT SUM(lr.intAmount)
                                FROM loan_rec lr
                                WHERE lr.company_id = lo.company_id
                                AND lr.loanDate BETWEEN STR_TO_DATE(
                                    CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                    '%Y-%m-%d')
                                AND '$date'
                            ), 0) AS totalIntAmountLoan,
                            COALESCE((
                                SELECT SUM(lr.odAmount)
                                FROM loan_rec lr
                                WHERE lr.company_id = lo.company_id
                                AND lr.loanDate BETWEEN STR_TO_DATE(
                                    CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                    '%Y-%m-%d')
                                AND '$date'
                            ), 0) AS totalOdAmountLoan,
                            COALESCE((
                                SELECT JSON_ARRAYAGG(JSON_OBJECT('allto', j.allto, 'amount', j.amount))
                                FROM journal j
                                WHERE j.companyId = j.companyId
                                AND j.date BETWEEN STR_TO_DATE(
                                    CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                    '%Y-%m-%d')
                                AND '$date'
                                AND j.type = 'payment'
                            ), '[]') AS paymentDetails
                        FROM 
                            loan lo
                        WHERE 
                            lo.company_id = '$company_id'
                            AND lo.loanDate BETWEEN STR_TO_DATE(
                                CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                '%Y-%m-%d') 
                            AND '$date';";
                break;
                case 'journal_data':
                if (!isset($_GET['date'])) {
                    echo json_encode(["success" => false, "message" => "date not provided"]);
                    exit;
                }
                $date = $conn->real_escape_string($_GET['date']);
                $sql = "SELECT 
                            aj.journal_from, 
                            aj.journal_to, 
                            aj.amount
                        FROM acc_journal aj
                        WHERE aj.company_id = '$company_id'
                          AND aj.date BETWEEN STR_TO_DATE(
                                              CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                              '%Y-%m-%d') 
                                          AND '$date';";
                case 'account_data':
                if (!isset($_GET['date'])) {
                    echo json_encode(["success" => false, "message" => "date not provided"]);
                    exit;
                }
                $date = $conn->real_escape_string($_GET['date']);
            
                $sql = "
                    SELECT 
                        acc_type,
                        under,
                        amount,
                        SUM(amount) OVER () AS sum_of_amt
                    FROM 
                        account
                    WHERE 
                        under IN ('Cash in hand', 'Bank')
                        AND company_id = '$company_id'
                        AND date BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d'
                        ) 
                        AND '$date'
                ";
                $result = $conn->query($sql);
                break;


                case 'balance_sheet':
                if (!isset($_GET['date'])) {
                    echo json_encode(["success" => false, "message" => "date not provided"]);
                    exit;
                }
                $date = $conn->real_escape_string($_GET['date']);
                $sql = "SELECT 
                    le.name, 
                    le.opening_bal, 
                    le.opening_bal_type, 
                    le.under,
                    COALESCE((
                        SELECT SUM(he.hpAmt) 
                        FROM hp_entry he 
                        WHERE he.company_id = le.company_id 
                          AND he.loanDate BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d') 
                          AND '$date'
                    ), 0) AS totalhpAmt,
                    COALESCE((
                        SELECT SUM(lo.loanAmt) 
                        FROM loan lo 
                        WHERE lo.company_id = le.company_id 
                          AND lo.loanDate BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d') 
                          AND '$date'
                    ), 0) AS totalLoanAmt,
                    COALESCE((
                        SELECT SUM(hr.reduce_int_due) 
                        FROM hp_rec hr
                        WHERE hr.company_id = le.company_id
                          AND hr.cheque_date BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d')
                          AND '$date'
                    ), 0) AS totalReduceIntDue,
                    COALESCE((
                        SELECT SUM(hr.total_due_int) 
                        FROM hp_rec hr
                        WHERE hr.company_id = le.company_id
                          AND hr.cheque_date BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d')
                          AND '$date'
                    ), 0) AS totalDueInt,
                    COALESCE((
                        SELECT SUM(lr.loanAmount)
                        FROM loan_rec lr
                        WHERE lr.company_id = le.company_id
                          AND lr.dueFrom BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d')
                          AND '$date'
                    ), 0) AS totalLoanAmount,
                    COALESCE((
                        SELECT SUM(lr.payAmount)
                        FROM loan_rec lr
                        WHERE lr.company_id = le.company_id
                          AND lr.dueFrom BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d')
                          AND '$date'
                    ), 0) AS totalPayAmount,
                    COALESCE((
                        SELECT SUM(lr.principalAmt2)
                        FROM loan_rec lr
                        WHERE lr.company_id = le.company_id
                          AND lr.dueFrom BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d')
                          AND '$date'
                    ), 0) AS totalPrincipalAmt2,
                     COALESCE((
                        SELECT SUM(d.deposit_amt)
                        FROM deposit_entry d
                        WHERE d.company_id = le.company_id
                          AND d.cash_acc = 'Cash A/c'
                          AND d.deposit_date BETWEEN STR_TO_DATE(
                            CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                            '%Y-%m-%d')
                          AND '$date'
                    ), 0) AS totalDepositAmtDepCash
                    
                FROM ledger_entry le
                WHERE le.company_id = '$company_id'
                  AND le.date BETWEEN STR_TO_DATE(
                                          CONCAT(YEAR('$date') - IF(MONTH('$date') < 4, 1, 0), '-04-01'),
                                          '%Y-%m-%d') 
                                  AND '$date'
                                  AND le.under NOT IN ('Cash in hand', 'Bank')
                GROUP BY le.name, le.opening_bal, le.opening_bal_type, le.under;";
                break;
                default:
                    echo json_encode(["success" => false, "message" => "Invalid type provided"]);
                    exit;
            }

         $result = $conn->query($sql);

if (!$result) {
    echo json_encode(["success" => false, "message" => "SQL Error: " . $conn->error]);
    exit;
}

if ($result->num_rows > 0) {
    $data = array();
    while ($row = $result->fetch_assoc()) {
        foreach ($row as $key => $value) {
            $row[$key] = ($value === null) ? "0.00" : $value; // Convert NULL values
        }
        $data[] = $row;
    }
    echo json_encode(["success" => true, "data" => $data]);
} else {
    echo json_encode(["success" => false, "message" => "No records found"]);
}


        } else {
            echo json_encode(["success" => false, "message" => "Type not provided"]);
        }

    } catch (Exception $e) {
        echo json_encode(["success" => false, "message" => "Error: " . $e->getMessage()]);
    }
}

else if ($_SERVER['REQUEST_METHOD'] === 'PUT') {
    $headers = apache_request_headers();
    if (isset($headers['Authorization'])) {
        $authHeader = $headers['Authorization'];
        list($jwt) = sscanf($authHeader, 'Bearer %s');
    } else {
        echo json_encode(["success" => false, "message" => "Authorization header missing"]);
        exit;
    }

    // Decode the authToken
    $decoded = JWT::decode($jwt, new Key($secret_key, 'HS256'));

    // Extract companyId and uid from the decoded token
    $company_id = $decoded->data->companyId;
    $uid = $decoded->data->uid;

    // Parse the incoming JSON payload
    $data = json_decode(file_get_contents('php://input'), true);

            // Extract the data from the request
        $id = $data['id'];
        $date = $data['date'];
        $name = $data['name'];
        $under = $data['under'];
        $opening_bal = $data['opening_bal'];
        $old_opening_bal = $data['old_opening_bal'];
        $opening_bal_type = $data['opening_bal_type'];
        $old_opening_bal_type = $data['old_opening_bal_type']; // New field
        $office_no = $data['office_no'];
        $mobile_no = $data['mobile_no'];
        $address = $data['address'];
        $address2 = $data['address2'];
        $address3 = $data['address3'];
        $address4 = $data['address4'];
        $email = $data['email'];
        $status = $data['status'];
        $ledger_id = $data['ledger_id'];
        
        // Start a transaction to ensure all operations succeed or fail together
        $conn->begin_transaction();
        
        try {
            // Update the company record in the company table
            $queryCompany = "UPDATE ledger_entry SET 
                                date = ?, 
                                name = ?, 
                                under = ?, 
                                opening_bal = ?, 
                                opening_bal_type = ?, 
                                address = ?, 
                                address2 = ?, 
                                address3 = ?, 
                                address4 = ?, 
                                office_no = ?, 
                                mobile_no = ?, 
                                email = ?, 
                                status = ? 
                              WHERE id = ?";
        
            $stmtCompany = $conn->prepare($queryCompany);
            $stmtCompany->bind_param('sssssssssssssi', 
                                    $date, 
                                    $name, 
                                    $under, 
                                    $opening_bal, 
                                    $opening_bal_type, 
                                    $address, 
                                    $address2, 
                                    $address3, 
                                    $address4, 
                                    $office_no, 
                                    $mobile_no, 
                                    $email, 
                                    $status, 
                                    $id);
        
            if (!$stmtCompany->execute()) {
                throw new Exception('Error updating company');
            }
            
            // Fetch the current amount from account table
            $fetchQuery = "SELECT amount FROM account WHERE ledger_id = ?";
            $fetchStmt = $conn->prepare($fetchQuery);
            $fetchStmt->bind_param("s", $ledger_id);
            $fetchStmt->execute();
            $result = $fetchStmt->get_result();
            $row = $result->fetch_assoc();
            $current_amount = $row['amount'];
            $fetchStmt->close();
        
            // Scenario calculations
            if ($old_opening_bal > $opening_bal) {
                $diff = $old_opening_bal - $opening_bal;
                $new_amount = $current_amount - $diff;
            } elseif ($old_opening_bal < $opening_bal) {
                $diff = $opening_bal - $old_opening_bal;
                $new_amount = $current_amount + $diff;
            } else {
                $new_amount = $current_amount; // No change
            }
            
            // Ensure amount doesn't go negative
            if ($new_amount < 0) {
                throw new Exception("Update failed: Amount cannot be negative");
            }
            // Step 1: Get the old ledger name based on voucher_number
$getOldLedgerSQL = "SELECT ledger FROM ledger_day_wise WHERE voucher_number = ?";
$getStmt = $conn->prepare($getOldLedgerSQL);
$getStmt->bind_param("s", $ledger_id);
$getStmt->execute();
$getStmt->bind_result($oldLedgerName);

if (!$getStmt->fetch()) {
    echo json_encode(["success" => false, "message" => "No ledger found for the given voucher number"]);
    exit;
}
$getStmt->close();

$oldLedgerName = trim($oldLedgerName); // Remove extra spaces

// Step 2: Update the ledger entry based on voucher_number
$ledger_sql1 = "UPDATE ledger_day_wise 
                SET company_id = ?, 
                    uid = ?, 
                    date = ?, 
                    ledger = ?, 
                    particulars = ?, 
                    credit = ?, 
                    debit = ? 
                WHERE voucher_number = ?";

$ledgerStmt = $conn->prepare($ledger_sql1);

if ($opening_bal_type == 'db') {
    $credit = 0.00;
    $debit = $opening_bal;
} elseif ($opening_bal_type == 'cr') {
    $credit = $opening_bal;
    $debit = 0.00;
} else {
    echo json_encode(["success" => false, "message" => "Invalid opening balance type"]);
    exit;
}

$ledgerStmt->bind_param("issssdds", $company_id, $uid, $date, $name, $name, $credit, $debit, $ledger_id);

if (!$ledgerStmt->execute()) {
    echo json_encode(["success" => false, "message" => "Error updating ledger: " . $ledgerStmt->error]);
    exit;
}
$ledgerStmt->close();


// Step 3: Update all occurrences of the old ledger name in ledger_day_wise (ledger & particulars)
$updateLedgerNameSQL = "UPDATE ledger_day_wise 
                        SET ledger = CASE WHEN ledger = ? THEN ? ELSE ledger END,
                            particulars = CASE WHEN particulars = ? THEN ? ELSE particulars END
                        WHERE ledger = ? OR particulars = ?";
$updateStmt = $conn->prepare($updateLedgerNameSQL);
$updateStmt->bind_param("ssssss", $oldLedgerName, $name, $oldLedgerName, $name, $oldLedgerName, $oldLedgerName);
$updateStmt->execute();
$updateStmt->close();


// Step 4: Update hp_entry table
$updatehpLedgerNameSQL = "UPDATE hp_entry SET cash = ? WHERE cash = ?";
$updatehpStmt = $conn->prepare($updatehpLedgerNameSQL);
$updatehpStmt->bind_param("ss", $name, $oldLedgerName);

$updatehpStmt->execute();
$updatehpStmt->close();

// Step 5: Update hp_rec table
$updateHpRecSQL = "UPDATE hp_rec SET cash = ? WHERE cash = ?";
$updateHpRecStmt = $conn->prepare($updateHpRecSQL);
$updateHpRecStmt->bind_param("ss", $name, $oldLedgerName);

$updateHpRecStmt->execute();
$updateHpRecStmt->close();

// Step 6: Update loan table (account_ledger field)
$updateLoanSQL = "UPDATE loan SET account_ledger = ? WHERE account_ledger = ?";
$updateLoanStmt = $conn->prepare($updateLoanSQL);
$updateLoanStmt->bind_param("ss", $name, $oldLedgerName);

$updateLoanStmt->execute();
$updateLoanStmt->close();

// Step 7: Update loan_rec table (account_ledger field)
$updateLoanRecSQL = "UPDATE loan_rec SET cashAcc = ? WHERE cashAcc = ?";
$updateLoanRecStmt = $conn->prepare($updateLoanRecSQL);
$updateLoanRecStmt->bind_param("ss", $name, $oldLedgerName);

$updateLoanRecStmt->execute();
$updateLoanRecStmt->close();

// Step 8: Update deposit_entry table (case_ac field)
$updateDepositEntrySQL = "UPDATE deposit_entry SET cheque_acc = ? WHERE cheque_acc = ?";
$updateDepositEntryStmt = $conn->prepare($updateDepositEntrySQL);
$updateDepositEntryStmt->bind_param("ss", $name, $oldLedgerName);
$updateDepositEntryStmt->execute();
$updateDepositEntryStmt->close();

// Step 9: Update payment_deposit table (cheque_acc field)
$updatePaymentDepositSQL = "UPDATE payment_deposit SET cash_ac = ? WHERE cash_ac = ?";
$updatePaymentDepositStmt = $conn->prepare($updatePaymentDepositSQL);
$updatePaymentDepositStmt->bind_param("ss", $name, $oldLedgerName);
$updatePaymentDepositStmt->execute();
$updatePaymentDepositStmt->close();

// Step 10: Update journal table (allfrom and allto fields)
$updateJournalSQL = "UPDATE journal 
                     SET allfrom = CASE WHEN allfrom = ? THEN ? ELSE allfrom END,
                         allto = CASE WHEN allto = ? THEN ? ELSE allto END
                     WHERE allfrom = ? OR allto = ?";
$updateJournalStmt = $conn->prepare($updateJournalSQL);
$updateJournalStmt->bind_param("ssssss", $oldLedgerName, $name, $oldLedgerName, $name, $oldLedgerName, $oldLedgerName);
$updateJournalStmt->execute();
$updateJournalStmt->close();

// Final Success Message
echo json_encode(["success" => true, "message" => "Ledger name updated successfully in all tables"]);

            
            // Update the account table
            $accountQuery = "UPDATE account SET 
                                company_id = ?,
                                uid = ?,
                                acc_type = ?,
                                under = ?,
                                amount = ?,
                                date = ?
                                WHERE ledger_id = ?";
            $accountStmt = $conn->prepare($accountQuery);
            $accountStmt->bind_param("ssssdss", $company_id, $uid, $name, $under, $new_amount, $date, $ledger_id);
            
            if (!$accountStmt->execute()) {
                throw new Exception("Failed to update account table");
            }
        
            // Commit transaction
            $conn->commit();
        
            echo json_encode([
                "success" => true,
                "message" => "Account and ledger entry updated successfully"
            ]);}
            catch (Exception $e) {
            // Rollback on failure
            $conn->rollback();
            echo json_encode([
                "success" => false,
                "message" => $e->getMessage()
            ]);
        }

        // Update the cash_acc in the accounts table
        // Step 1: Subtract the old opening balance from cash_acc
        if ($old_opening_bal_type === 'cr') {
            // If old opening balance type is credit (cr), subtract old opening balance
            $queryAccountUpdate = "UPDATE accounts SET 
                                    cash_acc = cash_acc - ? 
                                  WHERE company_id = ?";
        } elseif ($old_opening_bal_type === 'db') {
            // If old opening balance type is debit (db), add old opening balance
            $queryAccountUpdate = "UPDATE accounts SET 
                                    cash_acc = cash_acc + ? 
                                  WHERE company_id = ?";
        } else {
            throw new Exception('Invalid old opening balance type');
        }

        $stmtAccountUpdate = $conn->prepare($queryAccountUpdate);
        $stmtAccountUpdate->bind_param('ii', $old_opening_bal, $company_id);

        if (!$stmtAccountUpdate->execute()) {
            throw new Exception('Error updating accounts (adjusting old balance)');
        }
        // Step 2: Adjust the cash_acc based on opening_bal_type
        if ($opening_bal_type === 'cr') {
            // Add the new opening balance to cash_acc
            $queryAccountAdjust = "UPDATE accounts SET 
                                    cash_acc = cash_acc + ? 
                                  WHERE company_id = ?";

            $stmtAccountAdjust = $conn->prepare($queryAccountAdjust);
            $stmtAccountAdjust->bind_param('ii', $opening_bal, $company_id);
        } elseif ($opening_bal_type === 'db') {
            // Subtract the new opening balance from cash_acc
            $queryAccountAdjust = "UPDATE accounts SET 
                                    cash_acc = cash_acc - ? 
                                  WHERE company_id = ?";

            $stmtAccountAdjust = $conn->prepare($queryAccountAdjust);
            $stmtAccountAdjust->bind_param('ii', $opening_bal, $company_id);
        } else {
            throw new Exception('Invalid opening balance type');
        }

        if (!$stmtAccountAdjust->execute()) {
            throw new Exception('Error updating accounts (adjusting balance)');
        }

        // Commit the transaction if all updates succeed
        $conn->commit();

        echo json_encode(["success" => true, "message" => "Ledger and accounts updated successfully"]);
     

    // Close the statements
    $stmtCompany->close();
    $stmtAccountUpdate->close();
    $stmtAccountAdjust->close();
}

else {
    echo json_encode(['success' => false, 'message' => 'Invalid request method']);
}

// Close the connection
$conn->close();
?>
