from datetime import datetime, timedelta, date
import database

ACTIVE_LOAN_STATUSES = ("pending", "borrowed")
RESERVATION_WAITING = "waiting"
RESERVATION_READY = "ready"
RESERVATION_FULFILLED = "fulfilled"
RESERVATION_CANCELLED = "cancelled"


def count_active_loans(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT COUNT(*) FROM loans
            WHERE user_id=? AND returned=0
            AND status IN ('pending', 'borrowed')
        """, (user_id,))
        return cursor.fetchone()[0]


def get_pending_requests():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.id, books.title, books.author, loans.status,
                   loans.book_id, users.username, loans.borrow_date, users.student_number
            FROM loans
            JOIN books ON loans.book_id = books.id
            JOIN users ON loans.user_id = users.id
            WHERE loans.status = 'pending' AND loans.returned = 0
            ORDER BY loans.id DESC
        """)
        return cursor.fetchall()


def get_active_borrowed_loans():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.id, books.title, books.author, users.username,
                   users.student_number, loans.borrow_date, loans.due_date
            FROM loans
            JOIN books ON loans.book_id = books.id
            JOIN users ON loans.user_id = users.id
            WHERE loans.status = 'borrowed' AND loans.returned = 0
        """)
        rows = []
        for loan_id, title, author, username, student_num, borrow_date, due_date in cursor.fetchall():
            loan_status = "Overdue" if database.is_date_overdue(due_date) else "On Loan"
            rows.append((
                loan_id, title, author, username, student_num,
                borrow_date, due_date, loan_status,
            ))

    rows.sort(key=lambda row: (
        0 if database.is_date_overdue(row[6]) else 1,
        database.parse_loan_date(row[6]) or date.max,
    ))
    return rows


def get_pending_and_active_requests():
  pending = get_pending_requests()
  borrowed = get_active_borrowed_loans()
  active = []
  for row in pending:
      active.append(row)
  for row in borrowed:
      loan_id, title, author, username, student_num, borrow_date, due_date, loan_status = row
      active.append((
          loan_id, title, author, "borrowed", None, username, borrow_date, student_num,
      ))
  return active


def count_pending_requests():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT COUNT(*) FROM loans
            WHERE status = 'pending' AND returned = 0
        """)
        return cursor.fetchone()[0]


def request_book(book_id, user_id, num_to_borrow=1):
    import incidents
    suspended, reason = incidents.is_user_suspended(user_id)
    if suspended:
        return False, f"Borrowing suspended: {reason}"

    active = count_active_loans(user_id)
    if active + num_to_borrow > database.MAX_BORROW_LIMIT:
        remaining = max(0, database.MAX_BORROW_LIMIT - active)
        return False, (
            f"Borrow limit reached ({database.MAX_BORROW_LIMIT} books). "
            f"You can request {remaining} more."
        )

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id FROM loans
            WHERE book_id=? AND user_id=? AND returned=0
            AND status IN ('pending', 'borrowed')
        """, (book_id, user_id))

        if cursor.fetchone():
            return False, "You already borrowed or requested this book."

        cursor.execute("SELECT available FROM books WHERE id=?", (book_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Book not found."

        if row[0] < num_to_borrow:
            return False, f"Only {row[0]} copies available. Place a hold if none are left."

        for _ in range(num_to_borrow):
            cursor.execute("""
                INSERT INTO loans (book_id, user_id, status, returned)
                VALUES (?, ?, 'pending', 0)
            """, (book_id, user_id))

        conn.commit()

    return True, f"Successfully requested {num_to_borrow} copy(ies) of the book."


def place_hold(book_id, user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT available, title FROM books WHERE id=?", (book_id,))
        book = cursor.fetchone()
        if not book:
            return False, "Book not found."

        available, title = book
        if available > 0:
            return False, "Copies are available. Use Request Borrow instead."

        cursor.execute("""
            SELECT id FROM reservations
            WHERE book_id=? AND user_id=? AND status IN ('waiting', 'ready')
        """, (book_id, user_id))
        if cursor.fetchone():
            return False, "You already have a hold on this book."

        cursor.execute("""
            INSERT INTO reservations (book_id, user_id, status, created_at)
            VALUES (?, ?, 'waiting', ?)
        """, (book_id, user_id, datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
        conn.commit()

    return True, f"You are on the waitlist for '{title}'."


def get_student_reservations(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT reservations.id, books.title, books.author, reservations.status,
                   reservations.created_at, reservations.notified_at
            FROM reservations
            JOIN books ON reservations.book_id = books.id
            WHERE reservations.user_id=?
            AND reservations.status IN ('waiting', 'ready')
            ORDER BY reservations.created_at ASC
        """, (user_id,))
        return cursor.fetchall()


def claim_ready_hold(reservation_id, user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT book_id, status FROM reservations
            WHERE id=? AND user_id=?
        """, (reservation_id, user_id))
        row = cursor.fetchone()
        if not row:
            return False, "Reservation not found."

        book_id, status = row
        if status != RESERVATION_READY:
            return False, "This hold is not ready yet."

        cursor.execute("SELECT available FROM books WHERE id=?", (book_id,))
        available = cursor.fetchone()
        if not available or available[0] <= 0:
            return False, "No copies available right now."

    success, message = request_book(book_id, user_id, 1)
    if not success:
        return False, message

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE reservations
            SET status='fulfilled'
            WHERE id=?
        """, (reservation_id,))
        conn.commit()

    return True, "Hold claimed. Your borrow request was sent to the admin."


def cancel_hold(reservation_id, user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE reservations
            SET status='cancelled'
            WHERE id=? AND user_id=? AND status IN ('waiting', 'ready')
        """, (reservation_id, user_id))
        if cursor.rowcount == 0:
            return False, "Hold not found or already closed."
        conn.commit()
    return True, "Hold cancelled."


def _notify_next_hold(book_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id FROM reservations
            WHERE book_id=? AND status='waiting'
            ORDER BY created_at ASC
            LIMIT 1
        """, (book_id,))
        row = cursor.fetchone()
        if not row:
            return

        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute("""
            UPDATE reservations
            SET status='ready', notified_at=?
            WHERE id=?
        """, (now, row[0]))
        conn.commit()


def approve_request(loan_id, due_date=None):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT book_id FROM loans WHERE id=? AND status='pending'", (loan_id,))
        book = cursor.fetchone()
        if not book:
            return False, "Loan request not found or already approved."

        book_id = book[0]
        cursor.execute("SELECT available FROM books WHERE id=?", (book_id,))
        available = cursor.fetchone()
        if not available or available[0] <= 0:
            return False, "No available copies to approve."

        borrow_date = datetime.now()
        if due_date:
            normalized_due = database.normalize_date_str(due_date)
            if not normalized_due:
                return False, "Invalid due date format. Use YYYY-MM-DD."
            due_date = normalized_due
        else:
            due_date = (borrow_date + timedelta(days=14)).strftime("%Y-%m-%d")

        cursor.execute("""
            UPDATE loans
            SET status='borrowed', borrow_date=?, due_date=?, returned=0
            WHERE id=?
        """, (borrow_date.strftime("%Y-%m-%d"), due_date, loan_id))

        cursor.execute("UPDATE books SET available = available - 1 WHERE id=?", (book_id,))
        conn.commit()

    return True, "Loan approved successfully."


def return_book(loan_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.book_id, loans.returned, loans.user_id, loans.due_date, books.title
            FROM loans
            JOIN books ON loans.book_id = books.id
            WHERE loans.id=? AND loans.status='borrowed'
        """, (loan_id,))
        loan = cursor.fetchone()
        if not loan:
            return False, "Loan not found or already returned."

        book_id, returned, user_id, due_date, title = loan
        if returned:
            return False, "Book is already returned."

        days_late, fine_amount = calculate_fine(due_date)

        cursor.execute("""
            UPDATE loans SET returned=1, status='returned' WHERE id=?
        """, (loan_id,))
        cursor.execute("""
            UPDATE books
            SET available = MIN(quantity, available + 1)
            WHERE id=?
        """, (book_id,))
        conn.commit()

    _notify_next_hold(book_id)

    if days_late > 0 and fine_amount > 0:
        import notifications
        notifications.create_notification(
            user_id,
            f"Book Returned — Fine Due: {title}",
            (
                f"You returned '{title}', but an overdue fine of ₱{fine_amount:.0f} "
                f"({days_late} day(s) late) is still unpaid. "
                "Please pay at the library. The admin will mark it as paid."
            ),
            notification_type="overdue",
            related_loan_id=loan_id,
        )
        return True, (
            f"Book marked as returned. Outstanding fine: ₱{fine_amount:.0f} "
            f"— mark as paid in Admin → Fines after the student pays."
        )

    return True, "Book marked as returned successfully."


def update_book(book_id, title, author, shelf, description, quantity):
    title = title.strip()
    author = author.strip()
    shelf = shelf.strip()
    description = description.strip()

    if not title or not author:
        return False, "Title and author are required."

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

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

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT quantity, available FROM books WHERE id=?", (book_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Book not found."

        old_quantity, available = row
        borrowed = old_quantity - available
        if quantity < borrowed:
            return False, f"Cannot set quantity below borrowed copies ({borrowed})."

        new_available = quantity - borrowed
        cursor.execute("""
            UPDATE books
            SET title=?, author=?, shelf=?, description=?, quantity=?, available=?
            WHERE id=?
        """, (title, author, shelf, description, quantity, new_available, book_id))
        conn.commit()

    return True, "Book updated successfully."


def calculate_fine(due_date_str, fine_paid=False):
    if fine_paid or not due_date_str:
        return 0, 0

    due_date = database.parse_loan_date(due_date_str)
    if not due_date:
        return 0, 0

    today = date.today()
    days_late = (today - due_date).days
    if days_late <= 0:
        return 0, 0
    return days_late, days_late * database.FINE_PER_DAY


def get_student_fines(user_id):
    fines = []
    total = 0
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.id, books.title, loans.due_date, loans.fine_paid,
                   loans.returned, loans.status
            FROM loans
            JOIN books ON loans.book_id = books.id
            WHERE loans.user_id=?
            AND loans.fine_paid=0
            AND loans.due_date IS NOT NULL
            AND loans.status IN ('borrowed', 'returned')
        """, (user_id,))
        for loan_id, title, due_date, fine_paid, returned, status in cursor.fetchall():
            days_late, amount = calculate_fine(due_date, bool(fine_paid))
            if amount > 0:
                fines.append({
                    "loan_id": loan_id,
                    "title": title,
                    "due_date": due_date,
                    "days_late": days_late,
                    "amount": amount,
                    "paid": bool(fine_paid),
                    "returned": bool(returned),
                    "book_returned": bool(returned),
                })
                if not fine_paid:
                    total += amount
    return fines, total


def get_all_fines():
    rows = []
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.id, users.username, users.student_number,
                   books.title, loans.due_date, loans.fine_paid, loans.returned
            FROM loans
            JOIN users ON loans.user_id = users.id
            JOIN books ON loans.book_id = books.id
            WHERE loans.fine_paid=0
            AND loans.due_date IS NOT NULL
            AND loans.status IN ('borrowed', 'returned')
        """)
        for loan_id, username, student_number, title, due_date, fine_paid, returned in cursor.fetchall():
            days_late, amount = calculate_fine(due_date, bool(fine_paid))
            if amount > 0:
                rows.append({
                    "loan_id": loan_id,
                    "username": username,
                    "student_number": student_number or "-",
                    "title": title,
                    "due_date": due_date,
                    "days_late": days_late,
                    "amount": amount,
                    "paid": bool(fine_paid),
                    "returned": bool(returned),
                })
    return rows


def mark_fine_paid(loan_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.due_date, loans.fine_paid, books.title, loans.user_id
            FROM loans
            JOIN books ON loans.book_id = books.id
            WHERE loans.id=?
        """, (loan_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Loan not found."

        due_date, fine_paid, title, user_id = row
        if fine_paid:
            return False, "Fine is already marked as paid."

        days_late, amount = calculate_fine(due_date, False)
        if amount <= 0:
            return False, "No overdue fine on this loan."

        cursor.execute("UPDATE loans SET fine_paid=1 WHERE id=?", (loan_id,))
        conn.commit()

    import notifications
    notifications.create_notification(
        user_id,
        f"Fine Cleared: {title}",
        f"Your overdue fine of ₱{amount:.0f} for '{title}' has been marked as paid. Thank you!",
        notification_type="general",
        related_loan_id=loan_id,
    )
    return True, f"Fine of ₱{amount:.0f} marked as paid for '{title}'."


def get_library_stats():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM books")
        total_titles = cursor.fetchone()[0] or 0
        cursor.execute("SELECT COALESCE(SUM(available), 0) FROM books")
        available_copies = cursor.fetchone()[0] or 0
        cursor.execute("SELECT COALESCE(SUM(quantity), 0) FROM books")
        total_copies = cursor.fetchone()[0] or 0
        cursor.execute("""
            SELECT COUNT(*) FROM loans
            WHERE status = 'borrowed' AND returned = 0
        """)
        borrowed_copies = cursor.fetchone()[0] or 0
        cursor.execute("""
            SELECT COUNT(*) FROM loans WHERE status = 'pending' AND returned = 0
        """)
        pending_requests = cursor.fetchone()[0] or 0
        cursor.execute("""
            SELECT COUNT(DISTINCT user_id) FROM loans
            WHERE status = 'borrowed' AND returned = 0
        """)
        students_with_books = cursor.fetchone()[0] or 0
        cursor.execute("""
            SELECT due_date FROM loans
            WHERE status = 'borrowed' AND returned = 0 AND due_date IS NOT NULL
        """)
        overdue_loans = sum(
            1 for (due_date,) in cursor.fetchall() if database.is_date_overdue(due_date)
        )
        cursor.execute("SELECT COUNT(*) FROM users WHERE role='student'")
        total_students = cursor.fetchone()[0] or 0

    return {
        "total_titles": total_titles,
        "available_copies": available_copies,
        "total_copies": total_copies,
        "borrowed_copies": borrowed_copies,
        "pending_requests": pending_requests,
        "students_with_books": students_with_books,
        "overdue_loans": overdue_loans,
        "total_students": total_students,
    }


def get_unreturned_books():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT users.username, users.student_number, users.email,
                   books.title, books.author, loans.borrow_date, loans.due_date,
                   loans.id
            FROM loans
            JOIN users ON loans.user_id = users.id
            JOIN books ON loans.book_id = books.id
            WHERE loans.status = 'borrowed' AND loans.returned = 0
        """)
        rows = []
        for username, student_num, email, title, author, borrowed, due_date, loan_id in cursor.fetchall():
            loan_status = "Overdue" if database.is_date_overdue(due_date) else "On Loan"
            rows.append((
                username, student_num, email, title, author,
                borrowed, due_date, loan_id, loan_status,
            ))

    rows.sort(key=lambda row: (
        0 if database.is_date_overdue(row[6]) else 1,
        database.parse_loan_date(row[6]) or date.max,
    ))
    return rows


def get_overdue_students():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT users.username, users.student_number, books.title,
                   loans.due_date, loans.id
            FROM loans
            JOIN users ON loans.user_id = users.id
            JOIN books ON loans.book_id = books.id
            WHERE loans.status='borrowed'
            AND loans.returned=0
            AND loans.due_date IS NOT NULL
        """)
        rows = [row for row in cursor.fetchall() if database.is_date_overdue(row[3])]

    rows.sort(key=lambda row: database.parse_loan_date(row[3]) or date.max)
    return rows


def get_overdue_students_grouped():
    """Return one entry per student with all their overdue borrowed books."""
    grouped = {}
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT users.id, users.username, users.student_number, users.email,
                   books.title, loans.due_date, loans.id
            FROM loans
            JOIN users ON loans.user_id = users.id
            JOIN books ON loans.book_id = books.id
            WHERE loans.status='borrowed'
            AND loans.returned=0
            AND loans.due_date IS NOT NULL
        """)
        for uid, username, student_num, email, title, due_date, loan_id in cursor.fetchall():
            if not database.is_date_overdue(due_date):
                continue
            due = database.parse_loan_date(due_date)
            days_late = max((date.today() - due).days, 0) if due else 0
            fine_amount = days_late * database.FINE_PER_DAY
            if uid not in grouped:
                grouped[uid] = {
                    "user_id": uid,
                    "username": username,
                    "student_number": student_num or "-",
                    "email": (email or "").strip(),
                    "books": [],
                }
            grouped[uid]["books"].append({
                "loan_id": loan_id,
                "title": title,
                "due_date": due_date,
                "days_late": days_late,
                "fine_amount": fine_amount,
            })

    students = list(grouped.values())
    students.sort(key=lambda s: sum(b["fine_amount"] for b in s["books"]), reverse=True)
    return students


def get_most_borrowed_books(limit=10):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT books.title, books.author, COUNT(loans.id) AS borrow_count
            FROM loans
            JOIN books ON loans.book_id = books.id
            GROUP BY books.id
            ORDER BY borrow_count DESC
            LIMIT ?
        """, (limit,))
        return cursor.fetchall()


def get_low_stock_books():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT title, author, shelf, quantity, available
            FROM books
            WHERE available <= ?
            ORDER BY available ASC, title ASC
        """, (database.LOW_STOCK_THRESHOLD,))
        return cursor.fetchall()


def get_student_loans(user_id, only_active=False):
    with database.connect() as conn:
        cursor = conn.cursor()
        query = """
            SELECT books.title, books.author, loans.borrow_date,
                   loans.due_date, loans.status, loans.returned
            FROM loans
            JOIN books ON loans.book_id = books.id
            WHERE loans.user_id = ?
        """
        if only_active:
            query += " AND loans.status='borrowed' AND loans.returned=0"
        query += " ORDER BY loans.borrow_date DESC"
        cursor.execute(query, (user_id,))
        return cursor.fetchall()


def get_book_loans(book_id, only_active=False):
    with database.connect() as conn:
        cursor = conn.cursor()
        query = """
            SELECT loans.id, users.username, loans.borrow_date,
                   loans.due_date, loans.status, loans.returned
            FROM loans
            JOIN users ON loans.user_id = users.id
            WHERE loans.book_id=?
        """
        if only_active:
            query += " AND loans.status='borrowed' AND loans.returned=0"
        cursor.execute(query, (book_id,))
        return cursor.fetchall()