# Library Management API — ASP.NET WebAPI

A RESTful backend for managing library books, authors, and borrow records.  
Built with **ASP.NET Core 8 Web API** + **Entity Framework Core 8** + **MySQL (Pomelo)**.

---

## Project Structure

```
LibraryAPI/
├── Controllers/
│   ├── AuthorsController.cs       # CRUD + Top Borrowed Authors
│   ├── BooksController.cs         # CRUD + filtering
│   └── BorrowRecordsController.cs # CRUD + filtering + Overdue Report
├── Data/
│   └── LibraryDbContext.cs        # EF Core DbContext + seed data
├── DTOs/
│   ├── AuthorDtos.cs
│   ├── BookDtos.cs
│   └── BorrowRecordDtos.cs
├── Migrations/
├── Models/
│   ├── Author.cs
│   ├── Book.cs
│   └── BorrowRecord.cs
├── appsettings.json
├── appsettings.Development.json
├── LibraryAPI.csproj
└── Program.cs
```

---

## Database Schema

```
Authors          Books                BorrowRecords
─────────        ─────────────────    ──────────────────
Id (PK)          Id (PK)              Id (PK)
FirstName        Title                BorrowerName
LastName         ISBN (unique)        BorrowerEmail
Biography        Genre                BorrowDate
Nationality      PublishedYear        DueDate
CreatedAt        Price                ReturnDate
                 TotalCopies          Status (Active/Returned/Overdue)
                 AvailableCopies      Notes
                 Description          BookId (FK → Books)
                 CreatedAt
                 AuthorId (FK → Authors)
```

---

## Setup in Visual Studio 2022

### Prerequisites
- Visual Studio 2022 (any edition)
- .NET 8 SDK
- MySQL Server running locally (or remote)

### Steps

1. **Open** the project: `File → Open → Project/Solution → LibraryAPI.csproj`

2. **Update the connection string** in `appsettings.json`:
   ```json
   "DefaultConnection": "Server=localhost;Port=3306;Database=LibraryDB;User=root;Password=YOUR_PASSWORD;"
   ```

3. **Restore NuGet packages**: right-click solution → *Restore NuGet Packages*

4. **Apply migrations** (creates the DB automatically on first run).  
   Either use the Tools\NuGet Package Manager\Package Manager Console menu item:
   ```powershell
   Install-Package Microsoft.EntityFrameworkCore.Tools       # to install the EF migration tools (if they are not already installed)
   Add-Migration InitialCreate                               # EF Core will read the DbContext directly and generate a fresh, consistent 
                                                             # pair of files in the Migrations/ folder
   Update-Database                                           # create the database, all three tables, the indexes, and insert the seed data.
   ```
   Or just **run the project** — `Program.cs` calls `db.Database.Migrate()` automatically.

5. **Run the project** (`F5` or `Ctrl+F5`).  
   Swagger UI opens at `https://localhost:{port}/` (root URL).

---

## API Endpoints

### Authors

| Method | URL | Description |
|--------|-----|-------------|
| GET | `/api/authors` | List all authors (filter: `?name=&nationality=`) |
| GET | `/api/authors/{id}` | Get author by ID |
| POST | `/api/authors` | Create author |
| PUT | `/api/authors/{id}` | Update author |
| DELETE | `/api/authors/{id}` | Delete author (blocked if has books) |
| **GET** | **`/api/authors/top-borrowed?topN=5`** | **[Complex] Top borrowed authors** |

### Books

| Method | URL | Description |
|--------|-----|-------------|
| GET | `/api/books` | List all books (see filters below) |
| GET | `/api/books/{id}` | Get book by ID |
| POST | `/api/books` | Create book |
| PUT | `/api/books/{id}` | Update book |
| DELETE | `/api/books/{id}` | Delete book (blocked if active borrows) |

**Book filters** (query params):  
`?title=&genre=&authorId=&isAvailable=true&publishedYearFrom=1900&publishedYearTo=2000&priceMax=15`

### Borrow Records

| Method | URL | Description |
|--------|-----|-------------|
| GET | `/api/borrowrecords` | List all records (see filters below) |
| GET | `/api/borrowrecords/{id}` | Get record by ID |
| POST | `/api/borrowrecords` | Create record (auto-decrements available copies) |
| PUT | `/api/borrowrecords/{id}` | Update record (handles copy count on return) |
| DELETE | `/api/borrowrecords/{id}` | Delete record (only allowed if Returned) |
| **GET** | **`/api/borrowrecords/overdue-report`** | **[Complex] Full overdue report** |

**BorrowRecord filters** (query params):  
`?borrowerName=&borrowerEmail=&bookId=&status=Active&borrowDateFrom=&borrowDateTo=&isOverdue=true`

---

## Complex Features

### 1. Top Borrowed Authors — `GET /api/authors/top-borrowed?topN=5`
Joins **Authors → Books → BorrowRecords** to rank authors by total number of times their books were borrowed. Returns:
- Author details
- `TotalBorrows` — total borrow events across all their books
- `UniqueBooks` — how many different books were borrowed
- `LastBorrowDate` — most recent borrow date

### 2. Overdue Report — `GET /api/borrowrecords/overdue-report`
Joins **BorrowRecords → Books → Authors** to find all currently overdue loans. Also **auto-updates** their status to `Overdue` in the database. Returns:
- Borrower contact info
- Book + author details
- `DaysOverdue` — how many days past the due date

---

## Business Rules

- **Borrowing** a book auto-decrements `Book.AvailableCopies`; returns auto-increment it.
- Cannot borrow a book with 0 available copies.
- Cannot delete an **Author** who still has books in the system.
- Cannot delete a **Book** with active or overdue borrow records.
- Cannot delete a **BorrowRecord** that is still Active or Overdue.
- `AvailableCopies` can never exceed `TotalCopies`.
- ISBN is unique across the whole library.
