<?php
session_start();
ini_set('session.cookie_secure', 1);

require_once "../../includes/db.php";

header('Content-Type: application/json');

$user_id = $_SESSION['user_id'] ?? 0;
$coid  = $_SESSION['co_id'];
$acc_srch     = $_GET['acc_srch'] ?? '';

try {

    $date1 = isset($_GET['date3']) &&
        preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['date3'])
        ? $_GET['date3']
        : '0000-00-00';

    $date2 = isset($_GET['date4']) &&
        preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['date4'])
        ? $_GET['date4']
        : '0000-00-00';

    $stmt3 = $pdo->prepare("

    SELECT 
    d.t_date AS sort_date,
        d.co_id,
        d.t_tt_doc,
        d.t_ct_det,
        d.t_status,

        DATE_FORMAT(d.t_date, '%d-%b-%y') AS t_date,
        DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,

        '' AS t_doc_no,

        MAX(DATE_FORMAT(d.t_date, '%a, %d %b %Y')) AS acc_name,
        a.acc_name AS depositor,

        'DATE' AS t_type,

        NULL AS t_amount,
        
        d.t_narration,
        
        p.pm_acc_lvl,

        0 AS sort_order

    FROM transactions d

    JOIN accounts u
        ON d.t_cr = u.id
    JOIN accounts a
        ON d.t_entry_by = a.user_id

    JOIN permissions p
        ON (
            u.id = p.pm_acc_id
            OR u.acc_categ = p.pm_acc_categ
        )
        AND p.user_id = :user_id
        AND p.co_id = :coid3

    WHERE
        u.acc_categ IN ('expense')
        AND d.t_type IN ('exp')
        AND p.pm_acc_lvl IN (0,1,2,4,5,6,7,8)
        AND d.t_date BETWEEN :date1 AND :date2
        AND d.co_id = :coid
        AND u.acc_name LIKE :acc_srch

    GROUP BY d.t_date

    UNION ALL

    SELECT
    d.t_date AS sort_date,
        d.co_id,
        d.t_tt_doc,
        d.t_ct_det,
        d.t_status,

        DATE_FORMAT(d.t_date, '%d-%b-%y') AS t_date,
        DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,

        d.t_doc_no,

        u.acc_name,
        a.acc_name AS depositor,

        d.t_type,

        SUM(
            COALESCE(d.t_debit,0)
            + COALESCE(d.t_credit,0)
        ) AS t_amount,
        
        d.t_narration,
        
        p.pm_acc_lvl,

        1 AS sort_order

    FROM transactions d

    JOIN accounts u
        ON d.t_cr = u.id
    JOIN accounts a
        ON d.t_entry_by = a.user_id

    JOIN permissions p
        ON (
            u.id = p.pm_acc_id
            OR u.acc_categ = p.pm_acc_categ
        )
        AND p.user_id = :user_id1
        AND p.co_id = :coid4

    WHERE
        u.acc_categ IN ('expense')
        AND d.t_type IN ('exp')
        AND p.pm_acc_lvl IN (0,1,2,4,5,6,7,8)
        AND d.t_date BETWEEN :date12 AND :date22
        AND d.co_id = :coid1
        AND u.acc_name LIKE :acc_srch1

    GROUP BY
        d.t_date,
        d.t_doc_no,
        u.acc_name,
        d.t_type,
        d.t_status

    UNION ALL

    SELECT
    d.t_date AS sort_date,
        d.co_id,
        d.t_tt_doc,
        d.t_ct_det,
        d.t_status,

        DATE_FORMAT(d.t_date, '%d-%b-%y') AS t_date,
        DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,

        '' AS t_doc_no,

        '' AS acc_name,
        a.acc_name AS depositor,

        'Total' AS t_type,

        SUM(
            CASE
                WHEN LOWER(TRIM(d.t_status)) = 'Confirmed'
                THEN COALESCE(d.t_credit,0)
                ELSE 0
            END
        ) AS t_amount,
        
        d.t_narration,
        
        p.pm_acc_lvl,

        2 AS sort_order

    FROM transactions d

    JOIN accounts u
        ON d.t_cr = u.id
    JOIN accounts a
        ON d.t_entry_by = a.user_id

    JOIN permissions p
        ON (
            u.id = p.pm_acc_id
            OR u.acc_categ = p.pm_acc_categ
        )
        AND p.user_id = :user_id2
        AND p.co_id = :coid5

    WHERE
        u.acc_categ IN ('expense')
        AND d.t_type IN ('exp')
        AND p.pm_acc_lvl IN (0,1,2,4,5,6,7,8)
        AND d.t_date BETWEEN :date13 AND :date23
        AND d.co_id = :coid2
        AND u.acc_name LIKE :acc_srch2

    GROUP BY d.t_date

    ORDER BY
        sort_date DESC,
        sort_order,
        t_doc_no
    ");
    
    $search = "%".$acc_srch."%";

    $stmt3->bindParam(':user_id', $user_id, PDO::PARAM_INT);
    $stmt3->bindParam(':user_id1', $user_id, PDO::PARAM_INT);
    $stmt3->bindParam(':user_id2', $user_id, PDO::PARAM_INT);
    
    $stmt3->bindParam(':coid', $coid, PDO::PARAM_INT);
    $stmt3->bindParam(':coid1', $coid, PDO::PARAM_INT);
    $stmt3->bindParam(':coid2', $coid, PDO::PARAM_INT);
    $stmt3->bindParam(':coid3', $coid, PDO::PARAM_INT);
    $stmt3->bindParam(':coid4', $coid, PDO::PARAM_INT);
    $stmt3->bindParam(':coid5', $coid, PDO::PARAM_INT);
    
    $stmt3->bindParam(':acc_srch', $search);
    $stmt3->bindParam(':acc_srch1', $search);
    $stmt3->bindParam(':acc_srch2', $search);

    $stmt3->bindParam(':date1', $date1);
    $stmt3->bindParam(':date2', $date2);

    $stmt3->bindParam(':date12', $date1);
    $stmt3->bindParam(':date22', $date2);

    $stmt3->bindParam(':date13', $date1);
    $stmt3->bindParam(':date23', $date2);

    $stmt3->execute();

    $data3 = $stmt3->fetchAll(PDO::FETCH_ASSOC);

    if (!empty($data3)) {

        foreach ($data3 as $row) {

            $t_type = $row['t_type'];
            
            $pm_acc_lvl = $row['pm_acc_lvl'];

            $group_date = $row['t_date'];

            $lineStyle = "color:#6c757d;";
            $dateStyle = "color:#6c757d;";
            $statusStyle = "color:#6c757d;";
            $depositorStyle = "color:#6c757d;";

            switch ($t_type) {

                case 'DATE':

                    $lineStyle = "font-weight:bold;font-size:14px;color:transparent;";
                    $dateStyle = "font-weight:bold;font-size:14px;color:#000;";
                    $statusStyle = "font-weight:bold;font-size:14px;color:transparent;";
                    $depositorStyle = "font-weight:bold;font-size:14px;color:transparent;";

                    break;

                case 'Total':

                    $lineStyle = "font-weight:bold;font-size:14px;color:#000;";
                    $dateStyle = "font-weight:bold;font-size:14px;color:#000;";
                    $statusStyle = "font-weight:bold;font-size:14px;color:#000;";
                    $depositorStyle = "font-weight:bold;font-size:14px;color:transparent;";

                    break;

                default:

                    $lineStyle = "font-size:13px;color:#000;";
                    $dateStyle = "font-size:13px;color:#000;";
                    $statusStyle = "font-size:13px;color:#000;";
                    $depositorStyle = "font-size:13px;color:#000;";

                    break;
            }

            $rowClass = '';
            $permClass = 0;

            if ($t_type === 'Total') {
                $rowClass = 'daily-total-row';
            }
            
            if ($pm_acc_lvl =='7' || $pm_acc_lvl =='6' || $pm_acc_lvl =='2'|| $pm_acc_lvl =='1') {
            $permClass = 1;
            }

            echo '

            <tr 
                class="data-row ' . $rowClass . '" 
                data-group="' . htmlspecialchars($group_date) . '"
                style="border:none;">

                <td style="' . $dateStyle . '">
                        <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                    ' . htmlspecialchars($row["acc_name"] ?? '') . '
                    </a>
                </td>

                <td style="' . $dateStyle . '">
                        <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                    ' . htmlspecialchars($row["t_doc_no"] ?? '') . '
                    </a>
                </td>

                <td style="' . $lineStyle . '">
                        <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                    ' . htmlspecialchars($t_type) . '
                    </a>
                </td>
                
                <td style="' . $depositorStyle . '">
                        <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                    ' . htmlspecialchars($row["t_narration"] ?? '') . '
                    </a>
                </td>

                <td style="text-align:right; ' . $statusStyle . '">
                 
                       
                       
            ';

            if ($t_type === 'Total') {

                echo '
                <span class="daily-total-amount">
                    ' . number_format($row["t_amount"], 0, ".", ",") . '
                </span>';

            } else {

                echo'
                <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                     ' .  number_format($row["t_amount"], 0, ".", ","). '
                       </a>';

            }

            if (
                strtolower(trim($row['t_status'] ?? '')) === 'pending'
                && $t_type !== 'DATE'
                && $t_type !== 'Total'
                && $permClass ==1
            ) {

                echo '

                <button 
                    class="btn btn-sm btn-success approve-btn ml-2"
                    data-doc="' . htmlspecialchars($row['t_doc_no']) . '"
                    data-type="' . htmlspecialchars($t_type) . '"
                    data-amount="' . floatval($row["t_amount"]) . '">

                    Approve

                </button>';
            }
            
            if (
                strtolower(trim($row['t_status'] ?? '')) === 'pending'
                && $t_type !== 'DATE'
                && $t_type !== 'Total'
                && $permClass ==0
            ) {

                echo '

                <button 
                    class="btn btn-sm btn-warning pend-btn ml-2">

                    Pending

                </button>';
            }

            echo '

                </td>

            </tr>';
        }

    } else {

        echo '
        <tr>
            <td colspan="4" class="text-center text-warning">
                No matching records found.
            </td>
        </tr>';
    }

} catch (PDOException $e) {

    echo '
    <tr>
        <td colspan="4" class="text-center text-danger">
            Error fetching data.
        </td>
    </tr>';

    error_log("Database Error: " . $e->getMessage());
}
?>

<script>

$(document).on('click', '.approve-btn', function () {

    let btn = $(this);

    let doc_no = btn.data('doc');
    let ttype = btn.data('type');

    $.ajax({

        url: '../includes/approve_bank_transaction.php',

        type: 'POST',

        dataType: 'json',

        data: {
            doc_no: doc_no,
            ttype: ttype
        },

        beforeSend: function () {

            btn.prop('disabled', true)
               .text('Approving...');
        },

        success: function (response) {

            if (response.status === 'success') {

                let amount = parseFloat(
                    btn.data('amount')
                ) || 0;

                let currentRow = btn.closest('tr');

                let group = currentRow.data('group');

                let totalRow = $(
                    '.daily-total-row[data-group="' + group + '"]'
                ).last();

                let totalSpan = totalRow.find(
                    '.daily-total-amount'
                );

                let currentTotal = parseFloat(
                    totalSpan.text()
                        .replace(/,/g, '')
                        .trim()
                ) || 0;

                let newTotal = currentTotal + amount;

                totalSpan.text(
                    newTotal.toLocaleString()
                );

                btn.fadeOut(300, function () {
                    $(this).remove();
                });

            } else {

                alert(response.message || 'Approval failed');

                btn.prop('disabled', false)
                   .text('Approve');
            }
        },

        error: function () {

            alert('Server error');

            btn.prop('disabled', false)
               .text('Approve');
        }
    });

});

</script>