<?php
header("Content-Type: application/json");
require_once "../config.php";

$fromDate = $_POST['fromDate'] ?? $_GET['fromDate'] ?? '';
$toDate   = $_POST['toDate'] ?? $_GET['toDate'] ?? '';
$qrcodeFilter = $_GET['bookQrId'] ?? $_POST['bookQrId'] ?? '';


try {
    if ($qrcodeFilter != '') {
        $sql = "SELECT 
    td.id,
    BookId,
    BookName,
    ISBNNumber,
    CopyId,
    book_no,
    BorrowerId,
    fine,
    RetrunStatus,
    tsd.FullName AS student,
    ts.FullName AS staff,
    BorrowerType,
    IssuesDate,
    DueDate,
    ReturnDate,
    tsd.StudentId,
    ts.StaffId,
    te.approved_due_date,
    (CASE 
        WHEN tsd.StudentId IS NOT NULL AND tsd.StudentId != '' THEN tsd.FullName 
        WHEN ts.StaffId IS NOT NULL AND ts.StaffId != '' THEN ts.FullName 
        ELSE '' 
     END) AS fullname
    FROM tblissuedbookdetails AS td
    INNER JOIN tblbooks AS tb ON tb.id = td.BookId
    LEFT JOIN tblbookcopies AS tbc ON tbc.id = td.CopyId
    LEFT JOIN tblstudents AS tsd ON tsd.StudentId = td.BorrowerID
    LEFT JOIN tblstaff AS ts ON ts.StaffId = td.BorrowerID
    LEFT JOIN tblbook_extensions AS te ON te.issue_id = td.id
    WHERE tbc.book_no = :qrcode AND tbc.book_status = 'issued' 
    AND td.RetrunStatus = 1 AND td.ReturnDate IS NOT NULL
    GROUP BY td.id";

        $stmt = $dbh->prepare($sql);
        $stmt->bindParam(':qrcode', $qrcodeFilter, PDO::PARAM_STR);
        $stmt->execute();
        $issuedBooks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } else if ($fromDate != '' && $toDate != '') {
        // Fetch From date and to date issued books
        $sql = "SELECT 
    td.id,
    BookId,
    BookName,
    ISBNNumber,
    CopyId,
    book_no,
    BorrowerId,
    fine,
    RetrunStatus,
    tsd.FullName AS student,
    ts.FullName AS staff,
    BorrowerType,
    IssuesDate,
    DueDate,
    ReturnDate,
    tsd.StudentId,
    ts.StaffId,
    te.approved_due_date,
    (CASE 
        WHEN tsd.StudentId IS NOT NULL AND tsd.StudentId != '' THEN tsd.FullName 
        WHEN ts.StaffId IS NOT NULL AND ts.StaffId != '' THEN ts.FullName 
        ELSE '' 
     END) AS fullname
    FROM tblissuedbookdetails AS td
    INNER JOIN tblbooks AS tb ON tb.id = td.BookId
    LEFT JOIN tblbookcopies AS tbc ON tbc.id = td.CopyId
    LEFT JOIN tblstudents AS tsd ON tsd.StudentId = td.BorrowerID
    LEFT JOIN tblstaff AS ts ON ts.StaffId = td.BorrowerID
    LEFT JOIN tblbook_extensions AS te ON te.issue_id = td.id
    WHERE (:fromDate = '' OR DATE(td.ReturnDate) >= :fromDate)
    AND (:toDate = '' OR DATE(td.ReturnDate) <= :toDate)
    AND td.RetrunStatus = 1 AND td.ReturnDate IS NOT NULL    
    GROUP BY td.id;";

        $stmt = $dbh->prepare($sql);
        $stmt->bindParam(':fromDate', $fromDate, PDO::PARAM_STR);
        $stmt->bindParam(':toDate', $toDate, PDO::PARAM_STR);
        $stmt->execute();
        $issuedBooks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } else {
        // Fetch all issued books
        $sql = "SELECT 
    td.id,
    BookId,
    BookName,
    ISBNNumber,
    CopyId,
    book_no,
    BorrowerId,
    fine,
    RetrunStatus,
    tsd.FullName AS student,
    ts.FullName AS staff,
    BorrowerType,
    IssuesDate,
    DueDate,
    ReturnDate,
    tsd.StudentId,
    ts.StaffId,
    te.approved_due_date,
    (CASE 
        WHEN tsd.StudentId IS NOT NULL AND tsd.StudentId != '' THEN tsd.FullName 
        WHEN ts.StaffId IS NOT NULL AND ts.StaffId != '' THEN ts.FullName 
        ELSE '' 
     END) AS fullname
    FROM tblissuedbookdetails AS td
    INNER JOIN tblbooks AS tb ON tb.id = td.BookId
    LEFT JOIN tblbookcopies AS tbc ON tbc.id = td.CopyId
    LEFT JOIN tblstudents AS tsd ON tsd.StudentId = td.BorrowerID
    LEFT JOIN tblstaff AS ts ON ts.StaffId = td.BorrowerID
    LEFT JOIN tblbook_extensions AS te ON te.issue_id = td.id
    AND td.RetrunStatus = 1 AND td.ReturnDate IS NOT NULL
    WHERE DATE(td.ReturnDate) = CURDATE()   -- 🔹 Only today’s issues
    GROUP BY td.id
    ORDER BY td.id DESC";

        $stmt = $dbh->prepare($sql);
        $stmt->execute();
        $issuedBooks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }



    if (empty($issuedBooks)) {
        echo json_encode([
            "status" => "error",
            "message" => "No issued books found"
        ]);
        exit;
    }

    $result = [];

    foreach ($issuedBooks as $book) {
        $tableId = $book['id'];
        $BookId = $book['BookId'];
        $BorrowerId = $book['BorrowerId'];
        $BookName = $book['BookName'];
        $fullname = $book['fullname'];
        $BorrowerType = $book['BorrowerType'];
        $ISBNNumber = $book['ISBNNumber'];
        $copyId = $book['CopyId'];
        $IssuesDate = $book['IssuesDate'];
        $copy_no = $book['book_no'];
        $DueDate = $book['DueDate'];
        $ReturnDate = $book['ReturnDate'];
        $fine = $book['fine'];
        $RetrunStatus = $book['RetrunStatus'];
        $approved_due_date = $book['approved_due_date'];

        // Add record to result array
        $result[] = [
            "tableId" => $tableId,
            "BookId" => $BookId,
            "BorrowerId" => $BorrowerId,
            "BookName" => $BookName,
            "fullname" => $fullname,
            "BorrowerType" => $BorrowerType,
            "ISBNNumber" => $ISBNNumber,
            "copyId" => $copyId,
            "copy_no" => $copy_no,
            "IssuesDate" => $IssuesDate,
            "DueDate" => $DueDate,
            "ReturnDate" => $ReturnDate,
            "fine" => $fine,
            "RetrunStatus" => $RetrunStatus,
            "approved_due_date" => $approved_due_date,
        ];
    }

    // Return JSON
    echo json_encode([
        "status" => "success",
        "data" => $result
    ], JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
} catch (Exception $e) {
    echo json_encode([
        "status" => "error",
        "message" => $e->getMessage()
    ]);
}
