<?php
require_once('../config.php');
$user_id = $_SESSION['user_id'];
$emp_id = $_SESSION['emp_id'];
$user_role = $_SESSION['user_role'];
// SQL server connection information
// $sql_details = $search_db;
$sql_details = $search_db;

if(!isset($_GET['isset'])){
    exit();
}

$extra_where = json_decode($_GET['extra_where'], true);
$financial_year_start = $extra_where['financial_year_start'];
$financial_year_end = $extra_where['financial_year_end'];

$financial_year_start = date("Y-m", strtotime($financial_year_start));
$financial_year_end = date("Y-m", strtotime($financial_year_end));


$taxSlabs = mysqli_fetch_all(mysqli_query($db, "SELECT * FROM taxSlabs WHERE year_start='$financial_year_start' AND year_end='$financial_year_end'"), MYSQLI_ASSOC);
function calculateTax($income, $slabs) {
    $totalTax = 0;

    // Sort slabs by 'tax_slab_from' ascending
    usort($slabs, function ($a, $b) {
        return $a['tax_slab_from'] <=> $b['tax_slab_from'];
    });

    foreach ($slabs as $slab) {
        $from = (int)$slab['tax_slab_from'];
        $to = (int)$slab['tax_slab_to'];
        $rate = (float)$slab['tax_slab_rate'];

        // If income is below slab's lower limit, skip
        if ($income <= $from) {
            break;
        }

        // If upper limit is 0, treat as infinity (last slab)
        $slabUpper = ($to == 0) ? $income : min($to, $income);

        // Taxable amount in this slab
        $taxableAmount = $slabUpper - $from;

        // Calculate tax
        $tax = ($taxableAmount * $rate) / 100;
        $totalTax += $tax;
    }

    return $totalTax;
}



$table = <<<EOT
(
    WITH RECURSIVE
    -- ============================
    -- LEAVES
    -- ============================
    leaves_keys AS (
        SELECT 
            employee_id,
            JSON_KEYS(leaves) AS keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(leaves), '$[0]')) AS key_name,
            1 AS idx,
            leaves
        FROM employee_yearly_salary  
        WHERE JSON_LENGTH(leaves) > 0 AND (`which_month`>='$financial_year_start' AND `which_month`<='$financial_year_end')

        UNION ALL

        SELECT
            employee_id,
            keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']'))) AS key_name,
            idx + 1,
            leaves
        FROM leaves_keys
        WHERE JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']')) IS NOT NULL
    ),
    aggregated_leaves AS (
        SELECT 
            employee_id,
            SUM(total_value) AS total_leaves,
            JSON_OBJECTAGG(key_name, total_value) AS leaves_json
        FROM (
            SELECT 
                employee_id,
                key_name,
                SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(leaves, CONCAT('$.', key_name))) AS UNSIGNED)) AS total_value
            FROM leaves_keys
            WHERE key_name IS NOT NULL
            GROUP BY employee_id, key_name
        ) t
        GROUP BY employee_id
    ),

    -- ============================
    -- NET CTC
    -- ============================
    net_ctc_keys AS (
        SELECT 
            employee_id,
            JSON_KEYS(net_ctc) AS keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(net_ctc), '$[0]')) AS key_name,
            1 AS idx,
            net_ctc
        FROM employee_yearly_salary 
        WHERE JSON_LENGTH(net_ctc) > 0 AND (`which_month`>='$financial_year_start' AND `which_month`<='$financial_year_end')

        UNION ALL

        SELECT
            employee_id,
            keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']'))) AS key_name,
            idx + 1,
            net_ctc
        FROM net_ctc_keys
        WHERE JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']')) IS NOT NULL
    ),
    aggregated_net_ctc AS (
        SELECT 
            employee_id,
            SUM(total_value) AS total_net_ctc,
            JSON_OBJECTAGG(key_name, total_value) AS net_ctc_json
        FROM (
            SELECT 
                employee_id,
                key_name,
                SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(net_ctc, CONCAT('$.', key_name))) AS UNSIGNED)) AS total_value
            FROM net_ctc_keys
            WHERE key_name IS NOT NULL
            GROUP BY employee_id, key_name
        ) t
        GROUP BY employee_id
    ),

    -- ============================
    -- NET PAYABLE
    -- ============================
    net_payable_keys AS (
        SELECT 
            employee_id,
            JSON_KEYS(net_payable) AS keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(net_payable), '$[0]')) AS key_name,
            1 AS idx,
            net_payable
        FROM employee_yearly_salary 
        WHERE JSON_LENGTH(net_payable) > 0 AND (`which_month`>='$financial_year_start' AND `which_month`<='$financial_year_end')

        UNION ALL

        SELECT
            employee_id,
            keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']'))) AS key_name,
            idx + 1,
            net_payable
        FROM net_payable_keys
        WHERE JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']')) IS NOT NULL
    ),
    aggregated_net_payable AS (
        SELECT 
            employee_id,
            SUM(total_value) AS total_net_payable,
            JSON_OBJECTAGG(key_name, total_value) AS net_payable_json
        FROM (
            SELECT 
                employee_id,
                key_name,
                SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(net_payable, CONCAT('$.', key_name))) AS UNSIGNED)) AS total_value
            FROM net_payable_keys
            WHERE key_name IS NOT NULL
            GROUP BY employee_id, key_name
        ) t
        GROUP BY employee_id
    ),

    -- ============================
    -- DEDUCTION
    -- ============================
    deduction_keys AS (
        SELECT 
            employee_id,
            JSON_KEYS(deduction) AS keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(deduction), '$[0]')) AS key_name,
            1 AS idx,
            deduction
        FROM employee_yearly_salary 
        WHERE JSON_LENGTH(deduction) > 0 AND (`which_month`>='$financial_year_start' AND `which_month`<='$financial_year_end')

        UNION ALL

        SELECT
            employee_id,
            keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']'))) AS key_name,
            idx + 1,
            deduction
        FROM deduction_keys
        WHERE JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']')) IS NOT NULL
    ),
    aggregated_deduction AS (
        SELECT 
            employee_id,
            SUM(total_value) AS total_deduction,
            JSON_OBJECTAGG(key_name, total_value) AS deduction_json
        FROM (
            SELECT 
                employee_id,
                key_name,
                SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(deduction, CONCAT('$.', key_name))) AS UNSIGNED)) AS total_value
            FROM deduction_keys
            WHERE key_name IS NOT NULL
            GROUP BY employee_id, key_name
        ) t
        GROUP BY employee_id
    ),

    -- ============================
    -- FINAL SALARY
    -- ============================
    final_salary_keys AS (
        SELECT 
            employee_id,
            JSON_KEYS(final_salary) AS keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(final_salary), '$[0]')) AS key_name,
            1 AS idx,
            final_salary
        FROM employee_yearly_salary 
        WHERE JSON_LENGTH(final_salary) > 0 AND (`which_month`>='$financial_year_start' AND `which_month`<='$financial_year_end')

        UNION ALL

        SELECT
            employee_id,
            keys_json,
            JSON_UNQUOTE(JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']'))) AS key_name,
            idx + 1,
            final_salary
        FROM final_salary_keys
        WHERE JSON_EXTRACT(keys_json, CONCAT('$[', idx, ']')) IS NOT NULL
    ),
    aggregated_final_salary AS (
        SELECT 
            employee_id,
            SUM(total_value) AS total_final_salary,
            JSON_OBJECTAGG(key_name, total_value) AS final_salary_json
        FROM (
            SELECT 
                employee_id,
                key_name,
                SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(final_salary, CONCAT('$.', key_name))) AS UNSIGNED)) AS total_value
            FROM final_salary_keys
            WHERE key_name IS NOT NULL
            GROUP BY employee_id, key_name
        ) t
        GROUP BY employee_id
    )

    -- ============================
    -- FINAL SELECT
    -- ============================
    SELECT 
        l.employee_id,

        l.total_leaves,
        l.leaves_json,

        c.total_net_ctc,
        c.net_ctc_json,

        p.total_net_payable,
        p.net_payable_json,

        d.total_deduction,
        d.deduction_json,

        f.total_final_salary,
        f.final_salary_json
    FROM aggregated_leaves l
    INNER JOIN aggregated_net_ctc c ON l.employee_id = c.employee_id
    INNER JOIN aggregated_net_payable p ON l.employee_id = p.employee_id
    INNER JOIN aggregated_deduction d ON l.employee_id = d.employee_id
    INNER JOIN aggregated_final_salary f ON l.employee_id = f.employee_id
) join_table
EOT;


$whereResult = null;
$whereAll = null;
if (isset($_GET['where_condition']) && !empty($_GET['where_condition'])) {
    $whereAll = $_GET['where_condition'];
}

// Table's primary key
$primaryKey = 'employee_id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array('db' => 'employee_id', 'dt' => 'employee_id'),
    array(
        'db' => 'leaves_json', 
        'dt' => 'leaves_json',
        'formatter' => function($cell, $row){
            $leaves = json_decode($cell, true);
            $returnValue = '<div>';
            foreach ($leaves as $key => $value) {
                $returnValue .= '<p class="mb-0"><strong>' . htmlspecialchars($key) . ':</strong> ' . htmlspecialchars($value) . '</p>';
            }
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'net_ctc_json', 
        'dt' => 'net_ctc_json',
        'formatter' => function($cell, $row){
            $net_ctc = json_decode($cell, true);
            $total = 0;
            $returnValue = '<div>';
            foreach ($net_ctc as $key => $value) {
                $returnValue .= '<p class="mb-0"><strong>' . htmlspecialchars($key) . ':</strong> ' . htmlspecialchars($value) . '</p>';
                $total += (int)$value;
            }
            $returnValue .= '<p class="mb-0"><strong>Total:</strong> ' . htmlspecialchars($total) . '</p>';
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'net_payable_json', 
        'dt' => 'net_payable_json',
        'formatter' => function($cell, $row){
            $net_payable = json_decode($cell, true);
            $total = 0;
            $returnValue = '<div>';
            foreach ($net_payable as $key => $value) {
                $returnValue .= '<p class="mb-0"><strong>' . htmlspecialchars($key) . ':</strong> ' . htmlspecialchars($value) . '</p>';
                $total += (int)$value;
            }
            $returnValue .= '<p class="mb-0"><strong>Total:</strong> ' . htmlspecialchars($total) . '</p>';
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'deduction_json', 
        'dt' => 'deduction_json',
        'formatter' => function($cell, $row){
            $deduction = json_decode($cell, true);
            $total = 0;
            $returnValue = '<div>';
            foreach ($deduction as $key => $value) {
                $returnValue .= '<p class="mb-0"><strong>' . htmlspecialchars($key) . ':</strong> ' . htmlspecialchars($value) . '</p>';
                $total += (int)$value;
            }
            $returnValue .= '<p class="mb-0"><strong>Total:</strong> ' . htmlspecialchars($total) . '</p>';
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'final_salary_json', 
        'dt' => 'final_salary_json',
        'formatter' => function($cell, $row){
            $final_salary = json_decode($cell, true);
            $total = 0;
            $returnValue = '<div>';
            foreach ($final_salary as $key => $value) {
                $returnValue .= '<p class="mb-0"><strong>' . htmlspecialchars($key) . ':</strong> ' . htmlspecialchars($value) . '</p>';
                $total += (int)$value;
            }
            $returnValue .= '<p class="mb-0"><strong>Total:</strong> ' . htmlspecialchars($total) . '</p>';
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'total_final_salary', 
        'dt' => 'total_final_salary',
        'formatter' => function($cell, $row){
            return calculateTax($row['total_final_salary'], $GLOBALS['taxSlabs']);
        }
    ),

);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
require('ssp.class.php');

echo json_encode(
    // SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns)
    SSP::complex($_GET, $sql_details, $table, $primaryKey, $columns, $whereResult, $whereAll)
);
