"""
Generated SQLite Library Schema Module

DO NOT EDIT THIS FILE - it is automatically generated from DBML.
Modify data/library.dbml and regenerate using generator/generate_library.py
"""

import sqlite3
from datetime import datetime
from typing import List, Tuple, Optional


def init_database(db_path: str = "library.db") -> sqlite3.Connection:
    """
    Create database and initialize schema.

    Creates all tables with proper relationships and constraints.

    Args:
        db_path: Path to SQLite database file

    Returns:
        sqlite3.Connection object connected to the database
    """
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    {%- for table in tables %}

    # Create {{ table.name }} table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS {{ table.name }} (
        {%- for field in table.fields %}
            {{ field.name }} {{ field.sql_type }}
            {%- if field.is_pk %} PRIMARY KEY{% endif %}
            {%- if field.is_nullable %} {% else %} NOT NULL{% endif %}
            {%- if not loop.last %},{% endif %}
        {%- endfor %}
        )
    """)
    {%- endfor %}

    conn.commit()
    return conn


def insert_sample_data(conn: sqlite3.Connection, data: dict) -> None:
    """
    Insert sample data into the database.

    Args:
        conn: sqlite3.Connection object
        data: Dictionary with 'authors', 'books', 'borrowers', 'loans' keys
    """
    cursor = conn.cursor()

    # Insert authors
    for author in data['authors']:
        cursor.execute(
            "INSERT INTO authors (id, name, birth_year) VALUES (?, ?, ?)",
            (author['id'], author['name'], author['birth_year'])
        )

    # Insert books
    for book in data['books']:
        cursor.execute(
            "INSERT INTO books (id, title, isbn, author_id) VALUES (?, ?, ?, ?)",
            (book['id'], book['title'], book['isbn'], book['author_id'])
        )

    # Insert borrowers
    for borrower in data['borrowers']:
        cursor.execute(
            "INSERT INTO borrowers (id, name, email) VALUES (?, ?, ?)",
            (borrower['id'], borrower['name'], borrower['email'])
        )

    # Insert loans
    for loan in data['loans']:
        cursor.execute(
            "INSERT INTO loans (id, book_id, borrower_id, loan_date, return_date) VALUES (?, ?, ?, ?, ?)",
            (loan['id'], loan['book_id'], loan['borrower_id'], loan['loan_date'], loan['return_date'])
        )

    conn.commit()


def get_active_loans(conn: sqlite3.Connection) -> List[Tuple]:
    """
    Query and return all active loans (not yet returned).

    Returns a list of tuples with:
    (loan_id, book_title, borrower_name, loan_date)

    Args:
        conn: sqlite3.Connection object

    Returns:
        List of tuples representing active loans
    """
    cursor = conn.cursor()
    cursor.execute("""
        SELECT
            l.id,
            b.title,
            br.name,
            l.loan_date
        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
        ORDER BY l.loan_date DESC
    """)
    return cursor.fetchall()


def get_loan_history(conn: sqlite3.Connection) -> List[Tuple]:
    """
    Query and return all loans (active and returned).

    Returns a list of tuples with:
    (loan_id, book_title, borrower_name, loan_date, return_date, status)

    Args:
        conn: sqlite3.Connection object

    Returns:
        List of tuples representing all loans
    """
    cursor = conn.cursor()
    cursor.execute("""
        SELECT
            l.id,
            b.title,
            br.name,
            l.loan_date,
            l.return_date,
            CASE WHEN l.return_date IS NULL THEN 'ACTIVE' ELSE 'RETURNED' END as status
        FROM loans l
        JOIN books b ON l.book_id = b.id
        JOIN borrowers br ON l.borrower_id = br.id
        ORDER BY l.loan_date DESC
    """)
    return cursor.fetchall()


def print_active_loans_report(conn: sqlite3.Connection) -> None:
    """
    Print a formatted report of active loans.

    Args:
        conn: sqlite3.Connection object
    """
    loans = get_active_loans(conn)

    print("\n" + "=" * 70)
    print("ACTIVE LOANS REPORT".center(70))
    print("=" * 70)

    if not loans:
        print("No active loans.".center(70))
    else:
        for loan_id, title, borrower, loan_date in loans:
            print(f"\nLoan ID: {loan_id}")
            print(f"  Book: {title}")
            print(f"  Borrower: {borrower}")
            print(f"  Loan Date: {loan_date}")

    print("\n" + "=" * 70 + "\n")


def print_loan_history_report(conn: sqlite3.Connection) -> None:
    """
    Print a formatted report of all loans with status.

    Args:
        conn: sqlite3.Connection object
    """
    loans = get_loan_history(conn)

    print("\n" + "=" * 90)
    print("LOAN HISTORY REPORT".center(90))
    print("=" * 90)
    print(f"{'Loan ID':<8} {'Book Title':<35} {'Borrower':<20} {'Status':<10}")
    print("-" * 90)

    if not loans:
        print("No loans found.".center(90))
    else:
        for loan_id, title, borrower, loan_date, return_date, status in loans:
            # Truncate title if too long
            title_display = title[:32] + "..." if len(title) > 35 else title
            borrower_display = borrower[:17] + "..." if len(borrower) > 20 else borrower
            print(f"{loan_id:<8} {title_display:<35} {borrower_display:<20} {status:<10}")

    print("\n" + "=" * 90 + "\n")