"""Auto-generated SQLite library database.

Generated from: library.dbml
DO NOT EDIT — modify the DBML data model or Jinja2 template instead.
"""

import sqlite3


DATABASE = ":memory:"


def create_tables(conn: sqlite3.Connection) -> None:
    """Create all tables."""
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS authors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            birth_year INTEGER NOT NULL
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            isbn TEXT NOT NULL,
            author_id INTEGER NOT NULL REFERENCES authors(id)
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS borrowers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS loans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_id INTEGER NOT NULL REFERENCES books(id),
            borrower_id INTEGER NOT NULL REFERENCES borrowers(id),
            loan_date TEXT NOT NULL,
            return_date TEXT
        )
    """)
    conn.commit()


def insert_seed_data(conn: sqlite3.Connection) -> None:
    """Insert sample data."""
    cursor = conn.cursor()

    # Seed: authors
    cursor.executemany(
        "INSERT INTO authors (name, birth_year) VALUES (?, ?)",
        [
            ('Gabriel Garcia Marquez', 1927),
            ('Toni Morrison', 1931),
            ('Haruki Murakami', 1949),
        ],
    )

    # Seed: books
    cursor.executemany(
        "INSERT INTO books (title, isbn, author_id) VALUES (?, ?, ?)",
        [
            ('One Hundred Years of Solitude', '978-0-06-088328-7', 1),
            ('Love in the Time of Cholera', '978-0-307-38962-2', 1),
            ('Beloved', '978-1-4000-3341-6', 2),
            ('Song of Solomon', '978-1-4000-3342-3', 2),
            ('Norwegian Wood', '978-0-375-70402-4', 3),
        ],
    )

    # Seed: borrowers
    cursor.executemany(
        "INSERT INTO borrowers (name, email) VALUES (?, ?)",
        [
            ('Alice Johnson', 'alice@example.com'),
            ('Bob Smith', 'bob@example.com'),
        ],
    )

    # Seed: loans
    cursor.executemany(
        "INSERT INTO loans (book_id, borrower_id, loan_date, return_date) VALUES (?, ?, ?, ?)",
        [
            (1, 1, '2024-09-15', '2024-10-01'),
            (3, 2, '2024-10-10', None),
        ],
    )
    conn.commit()


def report_active_loans(conn: sqlite3.Connection) -> None:
    """Run report: active_loans."""
    cursor = conn.cursor()
    cursor.execute("""
        SELECT b.title, br.name FROM loans l JOIN books b ON l.book_id = b.id JOIN borrowers br ON l.borrower_id = br.id WHERE l.return_date IS NULL
    """)
    rows = cursor.fetchall()
    print("=== Active Loans ===")
    for title, borrower in rows:
        print(f"  {title} -> {borrower}")
    if not rows:
        print("  (no active loans)")


def main() -> None:
    """Entry point."""
    conn = sqlite3.connect(DATABASE)
    try:
        create_tables(conn)
        insert_seed_data(conn)
        report_active_loans(conn)
    finally:
        conn.close()


if __name__ == "__main__":
    main()