<?php
require_once('tcpdf/tcpdf.php');
include '../db.php';

// Fetch and sanitize filters
$where = "WHERE (sb_type = 'Bill' OR sb_type = '')";
$show_pending = isset($_GET['show_pending']) && $_GET['show_pending'] == '1';
$where .= $show_pending ? " AND sb_recycle = '0'" : " AND sb_recycle = '1'";

// Filters
$invoice_search = isset($_GET['invoice_search']) ? mysqli_real_escape_string($con, trim($_GET['invoice_search'])) : '';
$student_search = isset($_GET['student_search']) ? mysqli_real_escape_string($con, trim($_GET['student_search'])) : '';
$asso_search = isset($_GET['asso_search']) ? mysqli_real_escape_string($con, trim($_GET['asso_search'])) : '';
$start_date = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
$end_date = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
$selected_pid = isset($_GET['selected_pid']) ? mysqli_real_escape_string($con, $_GET['selected_pid']) : '';
$batch = isset($_GET['batch']) ? mysqli_real_escape_string($con, $_GET['batch']) : '';

if (!empty($invoice_search)) {
    $where .= " AND sb_invoice LIKE '%$invoice_search%'";
}

if (!empty($asso_search)) {
    $fran_sql = mysqli_query($con, "SELECT uref_code FROM users WHERE utype = 'Associate' AND uname = '$asso_search'");
    if ($fran_row = mysqli_fetch_assoc($fran_sql)) {
        $associate_uref = trim($fran_row['uref_code']);
        $sub_assoc_codes = [];
        $sub_q = mysqli_query($con, "SELECT uref_code FROM users WHERE utype = 'Sub Associate' AND admin_ref = '$associate_uref'");
        while ($sub_row = mysqli_fetch_assoc($sub_q)) {
            $sub_assoc_codes[] = "'" . mysqli_real_escape_string($con, trim($sub_row['uref_code'])) . "'";
        }
        $ref_codes = array_merge(["'$associate_uref'"], $sub_assoc_codes);
        $ref_in = implode(",", $ref_codes);
        $uid_q = mysqli_query($con, "SELECT uid FROM users WHERE admin_ref IN ($ref_in)");
        $uid_list = [];
        while ($uid_row = mysqli_fetch_assoc($uid_q)) {
            $uid_list[] = "'" . $uid_row['uid'] . "'";
        }
        $where .= !empty($uid_list) ? " AND sb_for IN (" . implode(",", $uid_list) . ")" : " AND 1=0";
    } else {
        $where .= " AND 1=0";
    }
}

if (!empty($student_search)) {
    $uid_q = mysqli_query($con, "SELECT uid FROM users WHERE utype = 'Student' AND uname = '$student_search'");
    if ($uid_row = mysqli_fetch_assoc($uid_q)) {
        $where .= " AND sb_for = '" . $uid_row['uid'] . "'";
    } else {
        $where .= " AND 1=0";
    }
}

if (!empty($start_date)) {
    $start_formatted = date("M j, Y", strtotime($start_date));
    $where .= empty($end_date)
        ? " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) = STR_TO_DATE('$start_formatted', '%b %e, %Y')"
        : " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) BETWEEN STR_TO_DATE('$start_formatted', '%b %e, %Y') AND STR_TO_DATE('" . date("M j, Y", strtotime($end_date)) . "', '%b %e, %Y')";
} elseif (!empty($end_date)) {
    $end_formatted = date("M j, Y", strtotime($end_date));
    $where .= " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) <= STR_TO_DATE('$end_formatted', '%b %e, %Y')";
}

if (!empty($selected_pid)) {
    $where .= " AND sb_course = '$selected_pid'";
}
if (!empty($batch)) {
    $where .= " AND sb_batch = '$batch'";
}

// Data Query
$sql = "SELECT sb.*, u.uname AS student_name, p.product AS course_name, p.pAmount, a.uname AS bill_by 
        FROM student_bill sb
        LEFT JOIN users u ON sb.sb_for = u.uid
        LEFT JOIN product p ON sb.sb_course = p.pid
        LEFT JOIN users a ON sb.sb_by = a.uid
        $where
        ORDER BY sb.sb_id DESC";

$result = mysqli_query($con, $sql);

$total_paid = 0;
$total_payable = 0;
$unique_pairs = [];
while ($row = mysqli_fetch_assoc($result)) {
    $total_paid += floatval($row['sb_amount']);
    $pair_key = $row['sb_for'] . '_' . $row['sb_course'];
    if (!isset($unique_pairs[$pair_key])) {
        $unique_pairs[$pair_key] = true;
        $total_payable += floatval($row['pAmount']);
    }
}
mysqli_data_seek($result, 0);

// Clean values for filename usage
function clean_filename($string) {
    return preg_replace('/[^a-zA-Z0-9]/', '_', $string);
}

// Get current date and time
$timestamp = date('M j, Y');

// Prepare filename parts based on filters
$filenameParts = [];
$nameParts = [];

if (!empty($invoice_search)) {
    $filenameParts[] = 'Invoice_' . clean_filename($invoice_search);
}
if (!empty($student_search)) {
    $filenameParts[] = 'Student_' . clean_filename($student_search);
    $nameParts[] = clean_filename($student_search);
}
if (!empty($asso_search)) {
    $filenameParts[] = 'Associate_' . clean_filename($asso_search);
    $nameParts[] = clean_filename($asso_search);
}
if (!empty($start_date) && !empty($end_date)) {
    $filenameParts[] = 'From_' . clean_filename($start_date) . '_To_' . clean_filename($end_date);
} elseif (!empty($start_date)) {
    $filenameParts[] = 'From_' . clean_filename($start_date);
} elseif (!empty($end_date)) {
    $filenameParts[] = 'To_' . clean_filename($end_date);
}
if (!empty($selected_pid)) {
    $course_q = mysqli_query($con, "SELECT course_name FROM clg_course WHERE sl_no = '{$selected_pid}'");
    if ($cr = mysqli_fetch_assoc($course_q)) {
        $course = $cr['course_name'];
        // $filenameParts[] = 'Course_' . clean_filename($course);
        // $nameParts[] = 'Course_' . clean_filename($course);
    } else{
        // $filenameParts[] = 'Course_' . clean_filename($selected_pid);
    }
}
if (!empty($batch)) {
    $batch_sql = mysqli_query($con, "SELECT * FROM branch WHERE b_id = '$batch' ");
    if($batch_row = mysqli_fetch_assoc($batch_sql)){
        // $filenameParts[] = 'Batch_' . clean_filename($batch_row['batch_title']);
        // $nameParts[] = 'Batch_' . clean_filename($batch_row['batch_title']);
    } else{
        // $filenameParts[] = 'Batch_' . clean_filename($batch);
    }
}

// Append timestamp
$filenameParts[] = $timestamp;

// Implode all parts to build final name
$finalFilename = implode('_', $filenameParts);
$Filename = implode(' ', $nameParts);

$baseTitle = 'Bill Report';

if (!empty($Filename)) {
    $title = "$baseTitle - $Filename - $timestamp";
} else {
    $title = "$baseTitle - $timestamp";
}

// Create PDF
$pdf = new TCPDF('L', 'mm', 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Admin');
$pdf->SetTitle($title);
$pdf->SetMargins(10, 10, 10);
$pdf->SetAutoPageBreak(TRUE, 10);
$pdf->AddPage();

$html = '<h2 style="text-align:center;">Bill Report</h2>';
$html .= '<table border="1" cellpadding="4" cellspacing="0">
<thead style="background-color:#f2f2f2;">
<tr>
<th width="5%">#</th>
<th width="15%">Invoice</th>
<th width="20%">Student Name</th>
<th width="20%">Course</th>
<th width="10%">Amount (Rs)</th>
<th width="15%">Date</th>
<th width="15%">Bill By</th>
</tr>
</thead><tbody>';

$i = 1;
while ($row = mysqli_fetch_assoc($result)) {
    $course_sql = mysqli_query($con, "SELECT course_name FROM clg_course WHERE sl_no = '{$row['course_name']}'");
    if ($crow = mysqli_fetch_assoc($course_sql)) {
        $course_name = $crow['course_name'];
    }
    $html .= '<tr>
        <td width="5%">' . $i++ . '</td>
        <td width="15%">' . $row['sb_invoice'] . '</td>
        <td width="20%">' . htmlspecialchars($row['student_name']) . '</td>
        <td width="20%">' . htmlspecialchars($course_name) . '</td>
        <td width="10%" style="text-align:right;">' . number_format($row['sb_amount'], 2) . '</td>
        <td width="15%">' . date("Y-m-d", strtotime($row['sb_date'])) . '</td>
        <td width="15%">' . htmlspecialchars($row['bill_by']) . '</td>
    </tr>';
}
$html .= '</tbody></table>';

$html .= '<br><div><strong>Total Payable:</strong> Rs. ' . number_format($total_payable, 2) . '</div>';
$html .= '<div><strong>Total Paid:</strong> Rs. ' . number_format($total_paid, 2) . '</div>';
$html .= '<div><strong>Pending:</strong> Rs. ' . number_format($total_payable - $total_paid, 2) . '</div>';

$pdf->writeHTML($html, true, false, true, false, '');
$finalFilename = $finalFilename ?: 'Bill_Report_' . $timestamp;
$finalFilename .= '.pdf';

// Output PDF
$pdf->Output($finalFilename, 'D');

?>