<?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");

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"; // Replace with your secret key

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

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    $headers = apache_request_headers();
    $jwt = null;

    // Extract JWT from Authorization header
    if (isset($headers['Authorization'])) {
        $authHeader = $headers['Authorization'];
        if (preg_match('/Bearer\s(\S+)/', $authHeader, $matches)) {
            $jwt = $matches[1];
        }
    }

    if (!$jwt) {
        echo json_encode(["success" => false, "message" => "Authorization token missing or malformed"]);
        exit;
    }

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

        // Validate 'type' param
        if (!isset($_GET['type'])) {
            echo json_encode(["success" => false, "message" => "Type not provided"]);
            exit;
        }

        $type = $_GET['type'];
        $sql = "";
        $bindTypes = "i"; // default bind type
        $bindValues = [$company_id];

        switch ($type) {
            case 'ledger_group':
                $sql = "SELECT id, under_name AS under_name_data
                        FROM ledger_under 
                        WHERE com_id = ?";
                break;

            case 'area_name':
                $sql = "SELECT id, main_area AS main_area_data
                        FROM area 
                        WHERE com_id = ?";
                break;

            case 'loan_details':
                $loanId = $_GET['loan_id'];
                $sql = "SELECT * FROM loan_entry WHERE com_id = ? AND loan_id = ?";
                $bindTypes = "is";
                $bindValues[] = $loanId;
            break;

            case 'loan_receipts':
                $loanId = $_GET['loan_id'];
                $sql = "SELECT * FROM loan_receipt WHERE com_id = ? AND loan_id = ?";
                $bindTypes = "is";
                $bindValues[] = $loanId;
            break;
            case 'receipt_details':
                $recNo = $_GET['receipt_no'];
                $sql = "SELECT * FROM loan_receipt WHERE com_id = ? AND rec_no = ?";
                $bindTypes = "is";
                $bindValues[] = $recNo;
            break;

            case 'loan_no':
                $sql = "SELECT id, status, loan_id AS loan_id_data
                        FROM loan_entry
                        WHERE com_id = ?
                        ORDER BY loan_id DESC";
                break;


            case 'leger_name':
                $sql = "SELECT id, ledger_name AS ledger_name_data
                        FROM ledger_entry
                        WHERE ledger_under IN ('Cash in hand', 'Bank')
                          AND com_id = ?";
                break;

            case 'ledger_name':
                $sql = "SELECT ledger_name
                        FROM ledger_entry 
                        WHERE com_id = ?";
                break;

            case 'agent_name':
                $sql = "SELECT id, name AS agent_name_data
                        FROM collection_agent 
                        WHERE com_id = ?";
                break;

            case 'ledger_opening':
                $sql = "
                    SELECT 
                        le.ledger_name,
                        le.acc_type,
                        le.opening,
                        DATE_FORMAT(ldw.date, '%b') AS month_name,
                        IFNULL(SUM(ldw.credit), 0) AS total_credit,
                        IFNULL(SUM(ldw.debit), 0) AS total_debit
                    FROM ledger_entry le
                    LEFT JOIN ledger_day_wise ldw 
                        ON le.ledger_name = ldw.ledger 
                        AND le.com_id = ldw.com_id
                    WHERE le.com_id = ?
                      AND le.status = 'Active'
                    GROUP BY le.ledger_name, MONTH(ldw.date)
                    ORDER BY le.ledger_name, MONTH(ldw.date)
                ";
                break;
                
                case 'loan_view':
                $from   = $_GET['from_date']; // yyyy-MM-dd
                $to     = $_GET['to_date'];   // yyyy-MM-dd
                $ledger = $_GET['ledger'];

                $sql = "SELECT *
                        FROM loan_entry
                        WHERE com_id = ?
                          AND loan_date BETWEEN ? AND ?
                        ORDER BY loan_date ASC";
                $bindTypes = "iss";
                $bindValues = [$company_id, $from, $to];
                break;
                
                case 'receipt_view':
                $from   = $_GET['from_date']; // yyyy-MM-dd
                $to     = $_GET['to_date'];   // yyyy-MM-dd
                $ledger = $_GET['ledger'];

                $sql = "SELECT *
                        FROM loan_receipt
                        WHERE com_id = ?
                          AND rec_date BETWEEN ? AND ?
                        ORDER BY rec_date DESC";
                $bindTypes = "iss";
                $bindValues = [$company_id, $from, $to];
                break;
                
                
               /* case 'ledger_balance_sheet':
                    $sql = "
                        SELECT 
                            le.ledger_name,
                            le.acc_type,
                            le.opening,
                            le.ledger_under,
                            DATE_FORMAT(ldw.date, '%b') AS month_name,
                            IFNULL(SUM(ldw.credit), 0) AS total_credit,
                            IFNULL(SUM(ldw.debit), 0) AS total_debit
                        FROM ledger_entry le
                        LEFT JOIN ledger_day_wise ldw 
                            ON le.ledger_name = ldw.ledger 
                            AND le.com_id = ldw.com_id
                        WHERE le.com_id = ?
                          AND le.status = 'Active'
                        GROUP BY le.ledger_name, le.acc_type, le.opening, le.ledger_under, MONTH(ldw.date)
                        ORDER BY le.ledger_name, MONTH(ldw.date)
                    ";
                    break;*/
    
    
                      case 'ledger_balance_sheet':
                                    $sql = "
                                      SELECT 
                                        le.ledger_name,
                                        le.acc_type,
                                        le.opening,
                                        le.ledger_under,
                                        ldw.date AS entry_date, 
                                        DATE_FORMAT(ldw.date, '%b') AS month_name,
                                        IFNULL(SUM(ldw.credit), 0) AS total_credit,
                                        IFNULL(SUM(ldw.debit), 0) AS total_debit
                                    FROM ledger_entry le
                                    LEFT JOIN ledger_day_wise ldw 
                                        ON le.ledger_name = ldw.ledger 
                                        AND le.com_id = ldw.com_id
                                    WHERE le.com_id = ?
                                      AND le.status = 'Active'
                                    GROUP BY 
                                        le.ledger_name, 
                                        le.acc_type, 
                                        le.opening, 
                                        le.ledger_under, 
                                        ldw.date       -- added date here
                                    ORDER BY 
                                        le.ledger_name, 
                                        ldw.date;
                                    ";
                                    break;
    
    
    
                
           /* case 'profit_loss':
                $sql = "
                    SELECT 
                        le.ledger_name,
                        le.ledger_under,
                        IFNULL(SUM(ldw.debit), 0) AS total_debit,
                        IFNULL(SUM(ldw.credit), 0) AS total_credit,
                        IFNULL(SUM(CASE WHEN ldw.voucher_type IN ('Loan Interest', 'Loan Diff') THEN ldw.credit ELSE 0 END), 0) AS loan_interest_credit,
                        (IFNULL(SUM(ldw.debit), 0) - IFNULL(SUM(ldw.credit), 0)) AS overall_balance
                    FROM 
                        ledger_day_wise ldw
                    INNER JOIN 
                        ledger_entry le ON le.ledger_name = ldw.ledger
                    WHERE 
                        le.ledger_under = 'Expenses'
                        AND ldw.com_id = ?
                    GROUP BY 
                        le.ledger_name, le.ledger_under
                    ORDER BY 
                        le.ledger_under, le.ledger_name;
                ";
                break;*/
                                
                case 'profit_loss':
                    $sql = "
                                            SELECT 
    le.ledger_name,
    le.ledger_under,
    ldw.voucher_type,
    ldw.date,
    IFNULL(SUM(ldw.credit), 0) AS total_credit
FROM 
    ledger_day_wise ldw
INNER JOIN 
    ledger_entry le ON le.ledger_name = ldw.ledger
WHERE 
    ldw.com_id = ?
    AND (
            le.ledger_under = 'Expenses'
         OR ldw.voucher_type IN ('Loan Interest', 'Loan Diff')
        )
GROUP BY 
    le.ledger_name,
    le.ledger_under,
    ldw.voucher_type,
    ldw.date
ORDER BY 
    le.ledger_under,
    ldw.voucher_type,
    le.ledger_name,
    ldw.date;
                    ";
                    break;
    
    
    /*SELECT 
            le.ledger_name,
            le.ledger_under,
            IFNULL(SUM(ldw.credit), 0) AS total_credit
        FROM 
            ledger_day_wise ldw
        INNER JOIN 
            ledger_entry le ON le.ledger_name = ldw.ledger
        WHERE 
            le.ledger_under = 'Expenses'
            AND ldw.com_id = ?
        GROUP BY 
            le.ledger_name, le.ledger_under
        ORDER BY 
            le.ledger_under, le.ledger_name;*/
    
    
    case 'profit_loss_income':
    $sql = "
        SELECT 
            le.ledger_name,
            le.ledger_under,
            IFNULL(SUM(ldw.credit), 0) AS total_credit
        FROM 
            ledger_day_wise ldw
        INNER JOIN 
            ledger_entry le ON le.ledger_name = ldw.ledger
        WHERE 
            ldw.voucher_type IN ('Loan Interest', 'Loan Diff')
            AND ldw.com_id = ?
        GROUP BY 
            ldw.voucher_type,
            le.ledger_name, 
            le.ledger_under
        ORDER BY 
            le.ledger_under, le.ledger_name;
    ";
    break;


    
 /*case 'profit_loss_income':
    $sql = "
        SELECT 
            le.ledger_name,
            le.ledger_under,
            IFNULL(SUM(ldw.debit), 0) AS total_credit
        FROM 
            ledger_day_wise ldw
        INNER JOIN 
            ledger_entry le ON le.ledger_name = ldw.ledger
        WHERE 
            ldw.voucher_type IN ('Loan Interest', 'Loan Diff')
            AND ldw.com_id = ?
        GROUP BY 
            le.ledger_name, le.ledger_under
        ORDER BY 
            le.ledger_under, le.ledger_name;
    ";
    break;
*/

            case 'ledger_all_data':
                $from   = $_GET['from_date']; // yyyy-MM-dd
                $to     = $_GET['to_date'];   // yyyy-MM-dd
                $ledger = $_GET['ledger'];

                $sql = "SELECT date, particulars, voucher_type, credit, debit
                        FROM ledger_day_wise
                        WHERE com_id = ?
                          AND date BETWEEN ? AND ?
                          AND ledger = ?
                        ORDER BY date ASC";
                $bindTypes = "isss";
                $bindValues = [$company_id, $from, $to, $ledger];
                break;

            case 'loan_type':
                $sql = "SELECT id, loan_type AS loan_type_data, due_count AS due_count_data
                        FROM loan_type 
                        WHERE com_id = ?";
                break;

            case 'loan_id':
                $year = date("Y");
                $prefix = "DL" . $year;
                $likePattern = $prefix . "%";

                $sql = "SELECT loan_id FROM loan_entry 
                        WHERE com_id = ? AND loan_id LIKE ? 
                        ORDER BY loan_id DESC LIMIT 1";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("is", $company_id, $likePattern);
                $stmt->execute();
                $result = $stmt->get_result();

                if ($row = $result->fetch_assoc()) {
                    $lastLoanId = $row['loan_id'];
                    $lastNumber = intval(substr($lastLoanId, -3));
                    $nextNumber = str_pad($lastNumber + 1, 3, "0", STR_PAD_LEFT);
                } else {
                    $nextNumber = "001";
                }

                echo json_encode(["success" => true, "loan_id" => $prefix . $nextNumber]);
                exit;
                
                
                                case 'voucher_id':
                    $year = date("Y");
                    $prefix = "RC" . $year;
                    $likePattern = $prefix . "%";
                
                    // Query 1: Get last voucher_id from voucher table
                    $sql1 = "SELECT voucher_id FROM voucher
                             WHERE com_id = ? AND voucher_id LIKE ?
                             ORDER BY voucher_id DESC LIMIT 1";
                    $stmt1 = $conn->prepare($sql1);
                    $stmt1->bind_param("is", $company_id, $likePattern);
                    $stmt1->execute();
                    $result1 = $stmt1->get_result();
                    $last1 = ($row1 = $result1->fetch_assoc()) ? intval(substr($row1['voucher_id'], -3)) : 0;
                
                    // Query 2: Get last voucher_id from daily_entry table
                    $sql2 = "SELECT voucher_id FROM daily_entry
                             WHERE com_id = ? AND voucher_id LIKE ?
                             ORDER BY voucher_id DESC LIMIT 1";
                    $stmt2 = $conn->prepare($sql2);
                    $stmt2->bind_param("is", $company_id, $likePattern);
                    $stmt2->execute();
                    $result2 = $stmt2->get_result();
                    $last2 = ($row2 = $result2->fetch_assoc()) ? intval(substr($row2['voucher_id'], -3)) : 0;
                
                    // Find the highest number among both tables
                    $lastNumber = max($last1, $last2);
                
                    // Generate next voucher number
                    $nextNumber = str_pad($lastNumber + 1, 3, "0", STR_PAD_LEFT);
                
                    echo json_encode([
                        "success" => true,
                        "voucher_id" => $prefix . $nextNumber
                    ]);
                    exit;


         

            case 'receipt_no':
                $sql = "SELECT rec_no FROM loan_receipt 
                        WHERE com_id = ? 
                        ORDER BY CAST(rec_no AS UNSIGNED) DESC LIMIT 1";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("i", $company_id);
                $stmt->execute();
                $result = $stmt->get_result();

                if ($row = $result->fetch_assoc()) {
                    $lastReceiptNo = $row['rec_no'];
                    $nextNumber = str_pad(intval($lastReceiptNo) + 1, 3, "0", STR_PAD_LEFT);
                } else {
                    $nextNumber = "001";
                }

                echo json_encode(["success" => true, "receipt_no" => $nextNumber]);
                exit;

            default:
                echo json_encode(["success" => false, "message" => "Invalid type provided"]);
                exit;
        }

        // Prepare and bind dynamically
        if (!empty($sql)) {
            $stmt = $conn->prepare($sql);
            $stmt->bind_param($bindTypes, ...$bindValues);
            $stmt->execute();
            $result = $stmt->get_result();

            if ($result->num_rows > 0) {
                $data = [];
                while ($row = $result->fetch_assoc()) {
                    $data[] = $row;
                }
                echo json_encode(["success" => true, "data" => $data]);
            } else {
                echo json_encode(["success" => false, "message" => "No records found"]);
            }

            $stmt->close();
        }

    } catch (ExpiredException $e) {
        echo json_encode(["success" => false, "message" => "Token expired"]);
        exit;
    } catch (Exception $e) {
        echo json_encode(["success" => false, "message" => "Error: " . $e->getMessage()]);
        exit;
    }
} else {
    echo json_encode(['success' => false, 'message' => 'Invalid request method']);
}

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