# ----------------------------------------------------------------------------
# library.py  —  SQLite book-library system
# DO NOT EDIT — generated by generator.py from data/library.dbml
# ----------------------------------------------------------------------------

import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).parent / "library.db"


def create_connection(db_path: Path = DB_PATH) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys = ON")
    conn.row_factory = sqlite3.Row
    return conn


# ---------------------------------------------------------------------------
# Schema — generated from data/library.dbml
# ---------------------------------------------------------------------------

def create_tables(conn: sqlite3.Connection) -> None:
    with conn:
        conn.executescript(
            """
            PRAGMA foreign_keys = ON;
            CREATE TABLE IF NOT EXISTS authors (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                birth_year INTEGER
            );
            CREATE TABLE IF NOT EXISTS books (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                isbn TEXT,
                author_id INTEGER NOT NULL,
                FOREIGN KEY (author_id) REFERENCES authors(id)
            );
            CREATE TABLE IF NOT EXISTS borrowers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS loans (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                book_id INTEGER NOT NULL,
                borrower_id INTEGER NOT NULL,
                loan_date TEXT NOT NULL,
                return_date TEXT,
                FOREIGN KEY (book_id) REFERENCES books(id),
                FOREIGN KEY (borrower_id) REFERENCES borrowers(id)
            );
            """
        )


# ---------------------------------------------------------------------------
# Sample data — 3 authors, 5 books, 2 borrowers, 2 loans
# ---------------------------------------------------------------------------

def insert_sample_data(conn: sqlite3.Connection) -> None:
    with conn:
        # Authors
        conn.executemany(
            "INSERT OR IGNORE INTO authors (id, name, birth_year) VALUES (?, ?, ?)",
            [
                (1, "J.R.R. Tolkien",   1892),
                (2, "George Orwell",    1903),
                (3, "Ursula K. Le Guin",1929),
            ],
        )

        # Books
        conn.executemany(
            "INSERT OR IGNORE INTO books (id, title, isbn, author_id) VALUES (?, ?, ?, ?)",
            [
                (1, "The Hobbit",                  "978-0-261-10221-7", 1),
                (2, "The Lord of the Rings",       "978-0-261-10235-4", 1),
                (3, "Nineteen Eighty-Four",        "978-0-141-03614-4", 2),
                (4, "Animal Farm",                 "978-0-141-18776-1", 2),
                (5, "The Left Hand of Darkness",   "978-0-441-47812-5", 3),
            ],
        )

        # Borrowers
        conn.executemany(
            "INSERT OR IGNORE INTO borrowers (id, name, email) VALUES (?, ?, ?)",
            [
                (1, "Alice Smith", "alice@example.com"),
                (2, "Bob Jones",   "bob@example.com"),
            ],
        )

        # Loans — one returned (return_date set), one active (return_date NULL)
        conn.executemany(
            "INSERT OR IGNORE INTO loans (id, book_id, borrower_id, loan_date, return_date)"
            " VALUES (?, ?, ?, ?, ?)",
            [
                (1, 3, 2, "2024-01-10", "2024-01-24"),  # Returned: Bob borrowed 1984
                (2, 1, 1, "2024-03-15", None),           # Active:   Alice has The Hobbit
            ],
        )


# ---------------------------------------------------------------------------
# Report
# ---------------------------------------------------------------------------

def print_active_loans(conn: sqlite3.Connection) -> None:
    rows = conn.execute(
        """
        SELECT
            b.title        AS book_title,
            bw.name        AS borrower_name,
            bw.email       AS borrower_email,
            l.loan_date
        FROM loans l
        JOIN books     b  ON b.id  = l.book_id
        JOIN borrowers bw ON bw.id = l.borrower_id
        WHERE l.return_date IS NULL
        ORDER BY l.loan_date
        """
    ).fetchall()

    print("=" * 52)
    print("Active Loans Report")
    print("=" * 52)
    if not rows:
        print("  (no active loans)")
    for row in rows:
        print(f'  "{row["book_title"]}"')
        print(f'    Borrower : {row["borrower_name"]} <{row["borrower_email"]}>')
        print(f'    Loan date: {row["loan_date"]}')
        print()
    print("=" * 52)


# ---------------------------------------------------------------------------
# Entry point
# ---------------------------------------------------------------------------

def main() -> None:
    conn = create_connection()
    create_tables(conn)
    insert_sample_data(conn)
    print_active_loans(conn)
    conn.close()


if __name__ == "__main__":
    main()