import database


def add_book(title, author, shelf="", description="", quantity=1):
    title = (title or "").strip()
    author = (author or "").strip()
    shelf = (shelf or "").strip()
    description = (description or "").strip()

    if quantity == "" or quantity is None:
        quantity = 1

    if not title:
        return False, "Book title is required.", None
    if not author:
        return False, "Author is required.", None

    try:
        quantity = int(quantity)
    except (TypeError, ValueError):
        return False, "Quantity must be a number (default is 1).", None

    if quantity < 1:
        return False, "Quantity must be at least 1.", None

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT id FROM books WHERE title=? COLLATE NOCASE AND author=? COLLATE NOCASE",
            (title, author),
        )
        existing = cursor.fetchone()

        if existing:
            book_id = existing[0]
            cursor.execute("""
                UPDATE books
                SET shelf=?, description=?, quantity=quantity+?, available=available+?
                WHERE id=?
            """, (shelf, description, quantity, quantity, book_id))
        else:
            cursor.execute("""
                INSERT INTO books (title, author, shelf, description, quantity, available)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (title, author, shelf, description, quantity, quantity))
            book_id = cursor.lastrowid

        conn.commit()

    return True, f"'{title}' saved to the catalog.", book_id


def get_all_books(search_term=None):
    with database.connect() as conn:
        cursor = conn.cursor()
        if search_term and search_term.strip():
            like = f"%{search_term.strip()}%"
            cursor.execute("""
                SELECT id, title, author, shelf, description, quantity, available
                FROM books
                WHERE title LIKE ? COLLATE NOCASE
                   OR author LIKE ? COLLATE NOCASE
                   OR shelf LIKE ? COLLATE NOCASE
                ORDER BY title COLLATE NOCASE
            """, (like, like, like))
        else:
            cursor.execute("""
                SELECT id, title, author, shelf, description, quantity, available
                FROM books
                ORDER BY title COLLATE NOCASE
            """)
        return cursor.fetchall()


def get_book_by_id(book_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT id, title, author, shelf, description, quantity, available FROM books WHERE id=?",
            (book_id,),
        )
        return cursor.fetchone()


def count_books():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM books")
        return cursor.fetchone()[0] or 0


def delete_book(book_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT title FROM books WHERE id=?", (book_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Book not found."
        title = row[0]

        cursor.execute("""
            SELECT COUNT(*) FROM loans
            WHERE book_id=? AND returned=0
            AND status IN ('pending', 'borrowed')
        """, (book_id,))
        if cursor.fetchone()[0] > 0:
            return False, (
                "Cannot delete this book while it has pending requests or copies still out. "
                "Return or cancel all active loans first."
            )

        cursor.execute("SELECT id FROM loans WHERE book_id=?", (book_id,))
        for (loan_id,) in cursor.fetchall():
            cursor.execute("DELETE FROM notifications WHERE related_loan_id=?", (loan_id,))

        cursor.execute("DELETE FROM book_incidents WHERE book_id=?", (book_id,))
        cursor.execute("DELETE FROM loans WHERE book_id=?", (book_id,))
        cursor.execute("DELETE FROM reservations WHERE book_id=?", (book_id,))
        cursor.execute("DELETE FROM books WHERE id=?", (book_id,))
        if cursor.rowcount == 0:
            return False, "Book could not be deleted."

        conn.commit()

        cursor.execute("SELECT id FROM books WHERE id=?", (book_id,))
        if cursor.fetchone():
            return False, "Book could not be removed from the catalog."

    return True, f"'{title}' was deleted from the catalog."