<?php
require('../config.php');
require_once "../salaryCodeCalculate.php";
require_once('../salaryCode.php');

function getFinancialYearRange($date)
{
    $timestamp = strtotime($date . "-01");

    $year = date("Y", $timestamp);
    $month = date("m", $timestamp);

    if ($month >= 4) {
        // April to December
        $startYear = $year;
        $endYear = $year + 1;
    } else {
        // January to March
        $startYear = $year - 1;
        $endYear = $year;
    }

    return [
        "financial_year_start" => $startYear . "-04",
        "financial_year_end"   => $endYear . "-03"
    ];
}

function find_total_leave($financial_year_start,$financial_year_end,$getMonth,$joining_date,$eid,$firstDate,$lastDate){
    /* ===================== SAFETY INITIALIZATION ===================== */
    $leave_number = [];
    $leave_name = [];
    $total_leave_used = [];
    $max_leave_available = 0;
    $in_cash_current_leave_count = null;

    /* ===================== MAIN LEAVE POLICY QUERY ===================== */
    $total_leave_sql = "
        WITH RECURSIVE final_leave_type AS (
            SELECT *
            FROM (
                SELECT *,
                    (CASE
                        WHEN main_applicable_from < DATE_FORMAT(DATE_SUB(CONCAT(applicable_from,'-01'), INTERVAL 1 YEAR), '%Y-%m')
                        THEN DATE_FORMAT(DATE_SUB(CONCAT(applicable_from,'-01'), INTERVAL 1 YEAR), '%Y-%m')
                        ELSE main_applicable_from
                    END) AS prev_applicable_from,
                    (CASE
                        WHEN COALESCE(main_applicable_to,'')=''
                        THEN DATE_FORMAT(DATE_SUB(CONCAT(applicable_to,'-01'), INTERVAL 1 YEAR), '%Y-%m')
                        WHEN main_applicable_to < DATE_FORMAT(DATE_SUB(CONCAT(applicable_to,'-01'), INTERVAL 1 YEAR), '%Y-%m')
                        THEN DATE_FORMAT(DATE_SUB(CONCAT(applicable_to,'-01'), INTERVAL 1 YEAR), '%Y-%m')
                        ELSE DATE_FORMAT(CONCAT(applicable_to,'-01'), '%Y-03')
                    END) AS prev_applicable_to
                FROM (
                    SELECT
                        id, duration, leave_details, max_leave_take,
                        carry_forward, max_carry_forward,
                        applicable_from AS main_applicable_from,
                        applicable_to AS main_applicable_to,
                        (CASE WHEN applicable_from >= '$financial_year_start' THEN applicable_from ELSE '$financial_year_start' END) AS applicable_from,
                        (CASE WHEN NULLIF(applicable_to,'') <= '$financial_year_end' THEN applicable_to ELSE '$financial_year_end' END) AS applicable_to,
                        leave_format
                    FROM leave_policy
                ) sub
            ) sub
            WHERE ('$getMonth' BETWEEN applicable_from AND applicable_to)
            AND leave_format = 'regular'
        ),
        find_total_leave AS (
            SELECT
                id, duration, leave_details, max_leave_take,
                carry_forward, max_carry_forward,
                prev_applicable_from, prev_applicable_to,
                applicable_from, applicable_to,
                JSON_UNQUOTE(JSON_EXTRACT(leave_details,'$[0].leave_type')) AS leave_name,
                JSON_UNQUOTE(JSON_EXTRACT(leave_details,'$[0].total_leave')) AS leave_number,
                0 AS idx
            FROM final_leave_type
            UNION ALL
            SELECT
                id, duration, leave_details, max_leave_take,
                carry_forward, max_carry_forward,
                prev_applicable_from, prev_applicable_to,
                applicable_from, applicable_to,
                JSON_UNQUOTE(JSON_EXTRACT(leave_details, CONCAT('$[',idx+1,'].leave_type'))) AS leave_name,
                JSON_UNQUOTE(JSON_EXTRACT(leave_details, CONCAT('$[',idx+1,'].total_leave'))) AS leave_number,
                idx+1
            FROM find_total_leave
            WHERE idx+1 < JSON_LENGTH(leave_details)
        ),
        final_max_leave AS (
            SELECT
                id, duration,
                (CASE WHEN COALESCE(max_leave_take,'')='' THEN leave_number ELSE max_leave_take END) AS max_leave_take,
                carry_forward, max_carry_forward,
                prev_applicable_from, prev_applicable_to,
                applicable_from, applicable_to,
                leave_name, leave_number
            FROM find_total_leave
        )
        SELECT
            id,
            MAX(duration) AS duration,
            MAX(max_leave_take) AS max_leave_take,
            MAX(carry_forward) AS carry_forward,
            MAX(max_carry_forward) AS max_carry_forward,
            MAX(prev_applicable_from) AS prev_applicable_from,
            MAX(prev_applicable_to) AS prev_applicable_to,
            MAX(applicable_from) AS applicable_from,
            MAX(applicable_to) AS applicable_to,
            GROUP_CONCAT(leave_name) AS leave_name,
            GROUP_CONCAT(leave_number) AS leave_number
        FROM final_max_leave
        GROUP BY id
    ";

    $total_leave = mysqli_fetch_assoc(mysqli_query($GLOBALS['db'], $total_leave_sql));

    if (!$total_leave) {
        return [];
    }

    /* ===================== NORMALIZE ARRAYS ===================== */
    $leave_name   = explode(',', $total_leave['leave_name'] ?? '');
    $leave_number = array_map('floatval', explode(',', $total_leave['leave_number'] ?? ''));

    /* ===================== YEARLY LOGIC ===================== */
    if ($total_leave['duration'] === 'Yearly') {
        $st = ($total_leave['applicable_from'] > date('Y-m', strtotime($joining_date)))
            ? "{$total_leave['applicable_from']}-01"
            : $joining_date;

        $date_num = date('d', strtotime($st));
        if ($date_num > 5 && $date_num <= 16) {
            $based_month_leave = [0.5, 0.5];
        } elseif ($date_num > 17 && $date_num <= 24) {
            $based_month_leave = [0, 0.5];
        } elseif ($date_num > 24) {
            $based_month_leave = [0, 0];
        } else {
            $based_month_leave = [1, 0.5];
        }


        $diff = date_diff(date_create($st), date_create(date('Y-m-t', strtotime($financial_year_end))));
        $months = $diff->m;

        foreach ($leave_number as $i => $v) {
            $leave_number[$i] = ($v / 12 * $months) + ($based_month_leave[$i] ?? 0);
        }
    }

    /* ===================== UTILIZED LEAVE ===================== */
    $utilize_leave_max_sql = "
        SELECT 
            sum(leave_count) as utilize_leave_max_count
        FROM utilize_leave WHERE employee_id='$eid' AND leave_status IN ('Casual','Medical') AND (leave_date BETWEEN '$firstDate' and '$lastDate')
        GROUP BY employee_id
    ";
    $utilize_leave_max_count = mysqli_fetch_assoc(mysqli_query($GLOBALS['db'],$utilize_leave_max_sql))['utilize_leave_max_count'];
    $max_leave_available = ($total_leave['max_leave_take']-$utilize_leave_max_count);

    if($total_leave['duration']=='Monthly' && $total_leave['carry_forward']=='No'){

        $utilize_leave_total_sql = "
            SELECT 
                Max(employee_id) as employee_id, leave_status, COALESCE(sum(leave_count),0) as leave_count 
            FROM utilize_leave WHERE employee_id='$eid' AND leave_status IN ('Casual','Medical') AND (leave_date BETWEEN '$firstDate' and '$lastDate')
            GROUP BY leave_status
        ";
        $utilize_leave_total_count = mysqli_query($GLOBALS['db'],$utilize_leave_total_sql);
        $leave_name = explode(',',$total_leave['leave_name']);
        // $leave_number = explode(',',$total_leave['leave_number']);
        $leave_number = $leave_number;

        while($ultc = mysqli_fetch_assoc($utilize_leave_total_count)){
            $ni = array_search($ultc['leave_status'],$leave_name);
            $total_leave_used[$ni] = $ultc['leave_count'];
            $leave_number[$ni] = $leave_number[$ni]- $ultc['leave_count'];
        }
    }else if($total_leave['duration']=='Monthly' && $total_leave['carry_forward']=='Yes'){

        $utilize_leave_total_sql = "
            SELECT 
                Max(employee_id) as employee_id, leave_status, COALESCE(sum(leave_count),0) as leave_count 
            FROM utilize_leave WHERE employee_id='$eid' AND leave_status IN ('Casual','Medical') AND (leave_date BETWEEN '$firstDate' and '$lastDate')
            GROUP BY leave_status
        ";
        $utilize_leave_total_count = mysqli_query($GLOBALS['db'],$utilize_leave_total_sql);
        $leave_name = explode(',',$total_leave['leave_name']);
        // $leave_number = explode(',',$total_leave['leave_number']);
        $leave_number = $leave_number;

        while($ultc = mysqli_fetch_assoc($utilize_leave_total_count)){
            $ni = array_search($ultc['leave_status'],$leave_name);
            $leave_number[$ni] = $leave_number[$ni]- $ultc['leave_count'];
        }

        // ################################################### previous leave
        $prev_start_month = date('Y-m-01',strtotime($total_leave['applicable_from']));
        $prev_till_month = date('Y-m-t',strtotime('previous month'));
            $date1=date_create($prev_start_month);
            $date2=date_create($prev_till_month);
            $diff=date_diff($date1,$date2);
        $total_prev_months = $diff->m +1;
        // $total_leave['total_prev_months'] = $total_prev_months;

        $u_leave_number = explode(',', $total_leave['leave_number']);
        foreach($u_leave_number as $i=> $v){
            $u_leave_number[$i] = $u_leave_number[$i]*$total_prev_months;
        }
        $prev_year_leave = $u_leave_number;

        
        $prev_utilize_leave_sql = "
            SELECT 
                Max(employee_id) as employee_id, leave_status, COALESCE(sum(leave_count),0) as leave_count 
            FROM utilize_leave WHERE employee_id='$eid' AND leave_status IN ('Casual','Medical') AND (leave_date BETWEEN '$prev_start_month' and '$prev_till_month')
            GROUP BY leave_status
        ";
        $prev_utilize_leave_count = mysqli_query($GLOBALS['db'],$prev_utilize_leave_sql);
        while($ultc = mysqli_fetch_assoc($prev_utilize_leave_count)){
            $ni = array_search($ultc['leave_status'],$leave_name);
            $total_leave_used[$ni] = $ultc['leave_count'];
            $prev_year_leave[$ni] = $prev_year_leave[$ni]- $ultc['leave_count'];
        }

        $max_leave_available = array_sum($prev_year_leave) + array_sum($leave_number);
        // ################################################### previous leave

    }else if($total_leave['duration']=='Yearly' && $total_leave['carry_forward']=='No'){

        $leave_year_start = date('Y-m-01', strtotime($total_leave['applicable_from']));
        $leave_year_end = date('Y-m-t',strtotime($total_leave['applicable_to']));

        // get yearly leave
        $utilize_leave_total_sql = "
            SELECT 
                Max(employee_id) as employee_id, leave_status, COALESCE(sum(leave_count),0) as leave_count 
            FROM utilize_leave WHERE employee_id='$eid' AND leave_status IN ('Casual','Medical') AND (leave_date BETWEEN '$leave_year_start' and '$leave_year_end') 
            GROUP BY leave_status
        ";
        $utilize_leave_total_count = mysqli_query($GLOBALS['db'],$utilize_leave_total_sql);
        $leave_name = explode(',',$total_leave['leave_name']);
        // $leave_number = explode(',',$total_leave['leave_number']);
        $leave_number = $leave_number;
        $leave_number_sum = array_sum($leave_number);

        while($ultc = mysqli_fetch_assoc($utilize_leave_total_count)){
            $ni = array_search($ultc['leave_status'],$leave_name);
            $total_leave_used[$ni] = $ultc['leave_count'];
            $leave_number[$ni] = $leave_number[$ni]- $ultc['leave_count'];
        }

    }else if($total_leave['duration']=='Yearly' && $total_leave['carry_forward']=='Yes'){

        $leave_year_start = date('Y-m-01', strtotime($total_leave['applicable_from']));
        $leave_year_end = date('Y-m-t',strtotime($total_leave['applicable_to']));

        $leave_name = explode(',',$total_leave['leave_name']);
        // $leave_number = explode(',', $total_leave['leave_number']);
        $leave_number = $leave_number;

        // ############################ current year in case
        $in_cash_current_leave_count_sql = "SELECT * FROM incash_leave WHERE employee_id='$eid' AND (financial_year_start<='$leave_year_start' and financial_year_end='$leave_year_end')";
        $in_cash_current_leave_count = mysqli_fetch_assoc(mysqli_query($GLOBALS['db'],$in_cash_current_leave_count_sql));
        $leave_number[0] = $leave_number[0] + $in_cash_current_leave_count['casual_carryForward'] ?? 0;
        $leave_number[1] = $leave_number[1] + $in_cash_current_leave_count['medical_carryForward'] ?? 0;
        $total_leave['leave_number'] = implode(',',$leave_number);
        $leave_number_sum = array_sum($leave_number);

        // ############################ current year in case


        // get yearly leave
        $utilize_leave_total_sql = "
            SELECT 
                Max(employee_id) as employee_id, leave_status, COALESCE(sum(leave_count),0) as leave_count 
            FROM utilize_leave WHERE employee_id='$eid' AND leave_status IN ('Casual','Medical') AND (leave_date BETWEEN '$leave_year_start' and '$leave_year_end') 
            GROUP BY leave_status
        ";
        $utilize_leave_total_count = mysqli_query($GLOBALS['db'],$utilize_leave_total_sql);
        while($ultc = mysqli_fetch_assoc($utilize_leave_total_count)){
            $ni = array_search($ultc['leave_status'],$leave_name);
            $total_leave_used[$ni] = $ultc['leave_count'];
            $leave_number[$ni] = $leave_number[$ni]- $ultc['leave_count'];
        }

    }

    // I want to sum total_leave_used
    $total_leave_used_sum = array_sum($total_leave_used);
    $excessive_leave_count = ($total_leave_used_sum > $leave_number_sum) ? ($total_leave_used_sum - $leave_number_sum) : 0;

    $general_total_yearly_leaves = 18;
    $extra_carry_over_leaves = 0;

    if($leave_number_sum > $general_total_yearly_leaves){
        $extra_carry_over_leaves = $leave_number_sum - $general_total_yearly_leaves;
    }

    // $excessive_leave_count = 0;

    if($excessive_leave_count > 0 && $extra_carry_over_leaves > 0){
        if($excessive_leave_count >= $extra_carry_over_leaves){
            $excessive_leave_count = $excessive_leave_count - $extra_carry_over_leaves;
            $extra_carry_over_leaves = 0;
        }else{
            $extra_carry_over_leaves = $extra_carry_over_leaves - $excessive_leave_count;
            $excessive_leave_count = $extra_carry_over_leaves;  
        }
    }
    
    // $max_leave_available = ($max_leave_available>array_sum($leave_number))?array_sum($leave_number):$max_leave_available;
    // return $total_leave += [
    //     'available_leave' => !empty($leave_number) ? implode(',', $leave_number) : '0',
    //     'max_leave_available' => !empty($max_leave_available) ? "$max_leave_available" : '0',
    //     'total_leave_used' => !empty($total_leave_used) ? implode(',', $total_leave_used) : '0',
    //     'in_cash_current_leave_count' => $in_cash_current_leave_count ?? null,
    // ];

    return $total_leave += [
        'total_leave_used' => !empty($total_leave_used_sum) ? $total_leave_used_sum : '0',
        'leave_number_sum' => !empty($leave_number_sum) ? $leave_number_sum : '0',
        'excessive_leave_count' => $excessive_leave_count,
        'extra_carry_over_leaves' => $extra_carry_over_leaves,
        'general_total_yearly_leaves' => $general_total_yearly_leaves,
    ];
}

if(isset($_POST['calculate_fnf'])){
    $fnf_months = $_POST['calculate_fnf'];
    $employee_id = $_POST['employee_id'];
    $fnf_months_array = explode(' to ', $fnf_months);
    
    // ###############################################################################
    $query = "SELECT u.employee_id, u.name, d.department_name, u.joining_date, u.working_status_date, u.account_no
        FROM users as u 
        JOIN departments as d on u.department=d.id
        WHERE u.employee_id='$employee_id'";
    $user = mysqli_fetch_assoc(mysqli_query($db, $query));

    if(!$user){
        echo json_encode(['status' => 404, 'message' => 'Employee not found.']);
        exit();
    }
    if($user['joining_date']){
        $joining_month = date("Y-m", strtotime($user['joining_date']));
        $join_month = date("Y-m", strtotime($fnf_months_array[0]));
        if($join_month < $joining_month){
            echo json_encode(['status' => 400, 'message' => 'Employee joining date is after the selected FNF Start Month.']);
            exit();
        }
    }
    if(empty($user['working_status_date'])){
        echo json_encode(['status' => 400, 'message' => 'Employee last working date is not set.']);
        exit();
    }
    if($user['working_status_date']){
        $ws_month = date("Y-m", strtotime($user['working_status_date']));
        $wsDate = date("Y-m", strtotime($fnf_months_array[1]));
        if($wsDate > $ws_month){
            echo json_encode(['status' => 400, 'message' => 'Employee working status date is before the selected FNF End Month.']);
            exit();
        }
    }
    // ###############################################################################

    list($financial_year_start, $financial_year_end) = array_values(getFinancialYearRange($ws_month));

    $joining_date = $user['joining_date'];
    // $firstDate = date("Y-m-01", strtotime($ws_month));
    // $lastDate = date("Y-m-t", strtotime($ws_month));

    // $totalLeavesRecords = find_total_leave($financial_year_start,$financial_year_end,$ws_month,$joining_date,$employee_id,$firstDate,$lastDate);

    // $excessive_leave_count = $totalLeavesRecords['excessive_leave_count'] ?? 0;
    // $extra_carry_over_leaves = $totalLeavesRecords['extra_carry_over_leaves'] ?? 0;
    
    $all_salaries = [];
    $fnf_details = [];
    $start_date = date("Y-m", strtotime($fnf_months_array[0]));
    $end_date = date("Y-m", strtotime($fnf_months_array[1]));
    while($start_date <= $end_date){
        $firstDayLast = date("Y-m-01", strtotime($start_date));
        $lastDayLast = date("Y-m-t", strtotime($start_date));
        
        // '$firstDayLast' as firstDayLast, '$lastDayLast' as lastDayLast
        $user['firstDayLast'] = $firstDayLast;
        $user['lastDayLast'] = $lastDayLast;

        $s_code = salaryCode($user, $firstDayLast, $lastDayLast);

        // if($excessive_leave_count > 0 && $ws_month == $start_date){

        //     $fixedMonthlySalary = $s_code['fixedMonthlySalary'] ?? 0;

        //     $excessive_leave_amount = ($fixedMonthlySalary / date("t", strtotime($ws_month . "-01"))) * $excessive_leave_count;

        //     $leave_deduction = [
        //         'monthly_amount' => $excessive_leave_amount,
        //         'title' => 'Excessive leaves (' . $excessive_leave_count . ' days)'
        //     ];

        //     array_push($s_code['salary_components']['deduction'], $leave_deduction);
        // }

        // if($extra_carry_over_leaves > 0 && $ws_month == $start_date){

        //     $fixedMonthlySalary = $s_code['fixedMonthlySalary'] ?? 0;

        //     $carry_over_leave_amount = ($fixedMonthlySalary / date("t", strtotime($ws_month . "-01"))) * $extra_carry_over_leaves;

        //     $carry_over_leave_add_on = [
        //         'monthly_amount' => $carry_over_leave_amount,
        //         'title' => 'Extra Carry Over Leaves (' . $extra_carry_over_leaves . ' days)'
        //     ];

        //     array_push($s_code['salary_components']['add_on'], $carry_over_leave_add_on);
        // }

        $leaves = $s_code['leaves'];
        $working_salary = $s_code['working_salary'];
        $deduction = $s_code['deduction'];
        $final_salary = $s_code['final_salary'];
        $salary_components = $s_code['salary_components'];

        $month_year = date("M Y", strtotime($firstDayLast));

        $all_salaries[$month_year] = [
            "user" => $user,
            "leaves" => $leaves,
            "salary_components" => $salary_components,
            // "totalLeavesRecords" => $totalLeavesRecords
        ];
        
        $fnf_details[$month_year]['fixed'][] = ["title"=>"Total Working Days", "value"=>$leaves['Current Range Days']];
        $fnf_details[$month_year]['fixed'][] = ["title"=>"Full Day Leave", "value"=>$leaves['Absent']];
        $fnf_details[$month_year]['fixed'][] = ["title"=>"Half Day Leave", "value"=>$leaves['Half Day']];
        $fnf_details[$month_year]['fixed'][] = ["title"=>"Allowed Leaves", "value"=>0];
        $fnf_details[$month_year]['fixed'][] = ["title"=>"Net Paid Days", "value"=>$leaves['Total Working Days']];
        $fnf_details[$month_year]['fixed'][] = ["title"=>"Payable Salary", "value"=>$s_code['fixedWorkingSalary']];

        if($month_year == date('M Y', strtotime(end($fnf_months_array)))){
            $fnf_details[$month_year]['fixed'][] = ["title"=>"Excess Leaves Utilized", "value"=>0];
        }

        $start_date = date("Y-m", strtotime("+1 month", strtotime($start_date)));
    }

    echo json_encode(['status' => 200, 'data' => ["all_salaries" => $all_salaries, "fnf_details" => $fnf_details]]);
    exit();
}


if(isset($_POST['fetch_employee'])){
    $employee_id = mysqli_real_escape_string($db, $_POST['fetch_employee']);

    $user_data = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM users WHERE employee_id='$employee_id' "));
    echo json_encode(["status"=>200, "user_data"=>$user_data]);
    exit();
}


if(isset($_POST['add_fnf'])){

    $employee_id = $_POST['employee_id'];
    $net_payable = $_POST['net_payable'];

    $fixed_title = $_POST['fixed_title'];
    $fixed_value = $_POST['fixed_value'];
    $fnf_details = [];
    foreach ($fixed_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['fixed'][] = ["title" => $title_key, "value" => $fixed_value[$key][$index]];
        }
    }

    $add_on_title = $_POST['add_on_title'];
    $add_on_value = $_POST['add_on_value'];
    foreach ($add_on_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['add_on'][] = ["title" => $title_key, "value" => $add_on_value[$key][$index]];
        }
    }

    $deduction_title = $_POST['deduction_title'];
    $deduction_value = $_POST['deduction_value'];
    foreach ($deduction_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['deduction'][] = ["title" => $title_key, "value" => $deduction_value[$key][$index]];
        }
    }

    $monthly_payable_title = $_POST['monthly_payable_title'];
    $monthly_payable_value = $_POST['monthly_payable_value'];
    foreach ($monthly_payable_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['monthly_payable'][] = ["title" => $title_key, "value" => $monthly_payable_value[$key][$index]];
        }
    }

    // INSERT INTO `fnf` (`id`, `employee_id`, `fnf_details`, `net_payable`, `created_at`) VALUES (NULL, NULL, NULL, NULL, current_timestamp());
    $employee_id = mysqli_real_escape_string($db, $employee_id);
    $fnf_details = mysqli_real_escape_string($db, json_encode($fnf_details));
    $net_payable = mysqli_real_escape_string($db, $net_payable);
    $insert_query = "INSERT INTO `fnf` (`employee_id`, `fnf_details`, `net_payable`) VALUES ('$employee_id', '$fnf_details', '$net_payable')";
    $insert_result = mysqli_query($db, $insert_query);
    if(!$insert_result){
        echo json_encode(["status"=>500, "message"=>"Something went wrong", "error"=>"Database error: ".mysqli_error($db)]);
        exit();
    }

    echo json_encode(["status"=>200, "message"=>"FNF details added successfully", "fnf_details"=>$fnf_details]);
    exit();
}


if(isset($_POST['update_fnf'])){

    $fnf_id = $_POST['edit_id'];
    $employee_id = $_POST['employee_id'];
    $net_payable = $_POST['net_payable'];

    $fixed_title = $_POST['fixed_title'];
    $fixed_value = $_POST['fixed_value'];
    $fnf_details = [];
    foreach ($fixed_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['fixed'][] = ["title" => $title_key, "value" => $fixed_value[$key][$index]];
        }
    }

    $add_on_title = $_POST['add_on_title'];
    $add_on_value = $_POST['add_on_value'];
    foreach ($add_on_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['add_on'][] = ["title" => $title_key, "value" => $add_on_value[$key][$index]];
        }
    }

    $deduction_title = $_POST['deduction_title'];
    $deduction_value = $_POST['deduction_value'];
    foreach ($deduction_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['deduction'][] = ["title" => $title_key, "value" => $deduction_value[$key][$index]];
        }
    }

    $monthly_payable_title = $_POST['monthly_payable_title'];
    $monthly_payable_value = $_POST['monthly_payable_value'];
    foreach ($monthly_payable_title as $key => $title_keys) {
        foreach ($title_keys as $index => $title_key) {
            $fnf_details[$key]['monthly_payable'][] = ["title" => $title_key, "value" => $monthly_payable_value[$key][$index]];
        }
    }

    $edit_id = mysqli_real_escape_string($db, $fnf_id);
    $fnf_details = mysqli_real_escape_string($db, json_encode($fnf_details));
    $net_payable = mysqli_real_escape_string($db, $net_payable);
    $insert_query = "UPDATE `fnf` SET `fnf_details`='$fnf_details', `net_payable`='$net_payable' WHERE `id`='$edit_id'";
    $insert_result = mysqli_query($db, $insert_query);
    if(!$insert_result){
        echo json_encode(["status"=>500, "message"=>"Something went wrong", "error"=>"Database error: ".mysqli_error($db)]);
        exit();
    }

    echo json_encode(["status"=>200, "message"=>"FNF details Updated successfully", "fnf_details"=>$fnf_details]);
    exit();
}

if(isset($_POST['manage_execessive_leaves'])){
    $fnf_months = $_POST['calculate_fnf_months'];
    $employee_id = $_POST['employee_id'];
    $excessive_leaves = $_POST['excessive_leaves'];
    $fnf_months_array = explode(' to ', $fnf_months);
    // $net_paid_days = $_POST['net_paid_days'] - $excessive_leaves;

    $firstDate = date("Y-m-01", strtotime($fnf_months_array[1]));
    $lastDate = date("Y-m-t", strtotime($fnf_months_array[1]));

    $query = "SELECT u.employee_id, u.name, d.department_name, u.joining_date, u.working_status_date, u.account_no
        FROM users as u 
        JOIN departments as d on u.department=d.id
        WHERE u.employee_id='$employee_id'";
    $user = mysqli_fetch_assoc(mysqli_query($db, $query));

    $user['firstDayLast'] = $firstDate;
    $user['lastDayLast'] = $lastDate;

    $s_code = salaryCode($user, $firstDate, $lastDate);

    // if($excessive_leaves == 0){
        $net_paid_days = $s_code['leaves']['Total Working Days'];
    // }

    $net_paid_days -= $excessive_leaves;

    $fixedMonthlySalary = $s_code['fixedMonthlySalary'] ?? 0;

    $deduction_total = array_reduce($s_code['deduction'] ?? [], function($carry, $item) {
        return $carry + (is_array($item) ? ($item['monthly_amount'] ?? 0) : $item);
    }, 0);

    $perDaySalary = round($fixedMonthlySalary / date("t", strtotime($firstDate)), 2);
    $excessive_leave_amount = round($perDaySalary * $excessive_leaves, 2);
    $monthly_payable_amount = round(($perDaySalary * $net_paid_days) - $deduction_total);
    $monthly_payable_salary = round($perDaySalary * $net_paid_days);

    $formattedMonthYear = date("M Y", strtotime($fnf_months_array[1]));

    echo json_encode([
        'status' => 200,
        'scode' => $s_code,
        'user' => $user,
        'excessive_leaves' => $excessive_leaves,
        'per_day_salary' => $perDaySalary,
        'excessive_leave_amount' => $excessive_leave_amount,
        'formatted_month_year' => $formattedMonthYear,
        'net_paid_days' => $net_paid_days,
        'monthly_payable_amount' => $monthly_payable_amount,
        'monthly_payable_salary' => $monthly_payable_salary,
        'deduction_total' => $deduction_total
    ]);
    
}

?>