USE [BsevitaLibrary];
GO
SET NOCOUNT ON;

SELECT 'Students' AS Entity, COUNT(*) AS Total FROM dbo.Students WHERE IsActive = 1
UNION ALL SELECT 'Books', COUNT(*) FROM dbo.Books WHERE IsActive = 1
UNION ALL SELECT 'ActiveLoans', COUNT(*) FROM dbo.Loans WHERE ReturnedAt IS NULL
UNION ALL SELECT 'OverdueLoans', COUNT(*) FROM dbo.Loans WHERE ReturnedAt IS NULL AND DueAt < SYSUTCDATETIME();

SELECT l.LoanId, s.CardNumber, CONCAT(s.FirstName, ' ', s.LastName) AS StudentName,
       b.BookNumber, b.Title, l.LoanedAt, l.DueAt, l.ReturnedAt
FROM dbo.Loans l
JOIN dbo.Students s ON s.StudentId = l.StudentId
JOIN dbo.Books b ON b.BookId = l.BookId
ORDER BY l.LoanedAt DESC;
GO