from datetime import datetime
import database
import notifications


def _now():
    return datetime.now().strftime("%Y-%m-%d %H:%M:%S")


def _message_with_admin_notes(base_message, admin_notes=""):
    notes = (admin_notes or "").strip()
    if not notes:
        return base_message
    return f"What you need to do:\n{notes}\n\n{base_message}"


def is_user_suspended(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT is_suspended, suspension_reason FROM users WHERE id=?", (user_id,))
        row = cursor.fetchone()
        if not row:
            return False, ""
        return bool(row[0]), row[1] or ""


def suspend_user(user_id, reason):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "UPDATE users SET is_suspended=1, suspension_reason=? WHERE id=?",
            (reason, user_id),
        )
        conn.commit()
    notifications.notify_suspension(user_id, reason)


def lift_suspension(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "UPDATE users SET is_suspended=0, suspension_reason=NULL WHERE id=?",
            (user_id,),
        )
        conn.commit()


def _get_loan_details(loan_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.id, loans.user_id, loans.book_id, loans.status, loans.returned,
                   books.title, books.quantity, books.available, users.username
            FROM loans
            JOIN books ON loans.book_id = books.id
            JOIN users ON loans.user_id = users.id
            WHERE loans.id=?
        """, (loan_id,))
        return cursor.fetchone()


def report_damage(loan_id, user_id, description, reported_when="return"):
    row = _get_loan_details(loan_id)
    if not row:
        return False, "Loan not found."
    if row[1] != user_id:
        return False, "This is not your loan."
    if row[4]:
        return False, "This loan is already closed."

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO book_incidents (
                loan_id, user_id, book_id, incident_type, description,
                reported_at, reported_when, status, processing_fee
            ) VALUES (?, ?, ?, 'damaged', ?, ?, ?, 'open', ?)
        """, (
            loan_id, user_id, row[2], description.strip(), _now(),
            reported_when, database.PROCESSING_FEE,
        ))
        conn.commit()

    notifications.notify_incident_update(
        user_id,
        "Damage Report Filed",
        f"Damage reported for '{row[5]}'. The librarian will review your report and "
        "tell you the payment amount or if you must donate a replacement.",
    )
    return True, "Damage report submitted. Wait for the librarian to review it."


def report_lost(loan_id, user_id):
    row = _get_loan_details(loan_id)
    if not row:
        return False, "Loan not found."
    if row[1] != user_id:
        return False, "This is not your loan."
    if row[4]:
        return False, "This loan is already closed."

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO book_incidents (
                loan_id, user_id, book_id, incident_type, description,
                reported_at, reported_when, status, processing_fee
            ) VALUES (?, ?, ?, 'lost', 'Book reported lost by student', ?, 'return', 'open', ?)
        """, (
            loan_id, user_id, row[2], _now(), database.PROCESSING_FEE,
        ))
        conn.commit()

    notifications.notify_incident_update(
        user_id,
        "Lost Book Report Filed",
        f"You reported '{row[5]}' as lost. The librarian will review your report and "
        "tell you whether to pay or donate a replacement copy.",
    )
    return True, "Lost book report submitted. Wait for the librarian to review it."


def set_payment_required(incident_id, amount, suspend_account=True, admin_notes=""):
    try:
        amount = float(amount)
    except (TypeError, ValueError):
        return False, "Payment amount must be a number."
    if amount <= 0:
        return False, "Payment amount must be greater than zero."

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT user_id, book_id, incident_type, books.title
            FROM book_incidents
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.id=? AND book_incidents.status NOT IN ('settled', 'warning_issued')
        """, (incident_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Report not found or already resolved."

        user_id, book_id, incident_type, title = row
        cursor.execute("""
            UPDATE book_incidents
            SET penalty_amount=?, status='payment_pending', settlement_method='cash',
                admin_notes=?
            WHERE id=?
        """, (amount, admin_notes.strip(), incident_id))

        if incident_type in ("lost", "damaged"):
            cursor.execute(
                "UPDATE loans SET status='returned', returned=1 WHERE id=(SELECT loan_id FROM book_incidents WHERE id=?)",
                (incident_id,),
            )
            if incident_type == "lost":
                cursor.execute("SELECT quantity, available FROM books WHERE id=?", (book_id,))
                qty_row = cursor.fetchone()
                if qty_row:
                    cursor.execute(
                        "UPDATE books SET quantity=?, available=? WHERE id=?",
                        (max(qty_row[0] - 1, 0), max(qty_row[1] - 1, 0), book_id),
                    )
        conn.commit()

    if suspend_account:
        suspend_user(
            user_id,
            f"Unresolved {incident_type} book case: '{title}'. Pay ₱{amount:.0f} to continue borrowing.",
        )
    notifications.notify_incident_update(
        user_id,
        f"Payment Required: {title}",
        _message_with_admin_notes(
            f"The librarian reviewed your {incident_type} book report for '{title}'. "
            f"You must pay ₱{amount:.0f} at the library. Borrowing is blocked until this is settled.",
            admin_notes,
        ),
    )
    return True, f"Payment of ₱{amount:.0f} required. Student notified."


def require_donation(incident_id, admin_notes=""):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT user_id, book_id, incident_type, books.title
            FROM book_incidents
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.id=? AND book_incidents.status NOT IN ('settled', 'warning_issued')
        """, (incident_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Report not found or already resolved."

        user_id, book_id, incident_type, title = row
        replacement_cost = database.DEFAULT_REPLACEMENT_COST
        processing_fee = database.PROCESSING_FEE
        total = replacement_cost + processing_fee

        cursor.execute("""
            UPDATE book_incidents
            SET status='donation_pending', settlement_method='donation',
                replacement_cost=?, processing_fee=?, penalty_amount=?,
                admin_notes=?, donation_verified=0
            WHERE id=?
        """, (replacement_cost, processing_fee, total, admin_notes.strip(), incident_id))

        if incident_type in ("lost", "damaged"):
            cursor.execute(
                "UPDATE loans SET status='returned', returned=1 WHERE id=(SELECT loan_id FROM book_incidents WHERE id=?)",
                (incident_id,),
            )
            if incident_type == "lost":
                cursor.execute("SELECT quantity, available FROM books WHERE id=?", (book_id,))
                qty_row = cursor.fetchone()
                if qty_row:
                    cursor.execute(
                        "UPDATE books SET quantity=?, available=? WHERE id=?",
                        (max(qty_row[0] - 1, 0), max(qty_row[1] - 1, 0), book_id),
                    )
        conn.commit()

    suspend_user(
        user_id,
        f"Donate a replacement copy for '{title}' before borrowing again.",
    )
    notifications.notify_incident_update(
        user_id,
        f"Donation Required: {title}",
        _message_with_admin_notes(
            f"The librarian requires you to donate a new copy of '{title}' (same title/edition). "
            "Bring the book to the library for verification. Borrowing is blocked until approved.",
            admin_notes,
        ),
    )
    return True, "Donation required. Student notified and account blocked."


def classify_damage(incident_id, classification, admin_notes=""):
    if classification not in ("minor", "major"):
        return False, "Classification must be minor or major."

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT user_id, book_id, incident_type FROM book_incidents WHERE id=? AND status='open'",
            (incident_id,),
        )
        row = cursor.fetchone()
        if not row:
            return False, "Incident not found or already resolved."

        user_id, book_id, incident_type = row
        if incident_type != "damaged":
            return False, "Only damage incidents can be classified."

        if classification == "minor":
            penalty = database.MINOR_DAMAGE_FEE
            status = "warning_issued"
            suspend = False
        else:
            penalty = database.DEFAULT_REPLACEMENT_COST + database.PROCESSING_FEE
            status = "payment_pending"
            suspend = True

        cursor.execute("""
            UPDATE book_incidents
            SET damage_classification=?, penalty_amount=?, replacement_cost=?,
                status=?, admin_notes=?, processing_fee=?
            WHERE id=?
        """, (
            classification,
            penalty,
            database.DEFAULT_REPLACEMENT_COST if classification == "major" else 0,
            status,
            admin_notes.strip(),
            database.PROCESSING_FEE,
            incident_id,
        ))

        if classification == "major":
            cursor.execute("SELECT quantity, available FROM books WHERE id=?", (book_id,))
            qty_row = cursor.fetchone()
            if qty_row:
                cursor.execute(
                    "UPDATE books SET quantity=?, available=? WHERE id=?",
                    (max(qty_row[0] - 1, 0), max(qty_row[1] - 1, 0), book_id),
                )
            cursor.execute(
                "UPDATE loans SET status='returned', returned=1 WHERE id=(SELECT loan_id FROM book_incidents WHERE id=?)",
                (incident_id,),
            )

        conn.commit()

    if classification == "minor":
        notifications.notify_incident_update(
            user_id,
            "Minor Damage — Written Warning",
            _message_with_admin_notes(
                f"A written warning has been recorded. Fee: ₱{database.MINOR_DAMAGE_FEE:.0f}. "
                "Pay at the library when you can.",
                admin_notes,
            ),
        )
        return True, "Classified as minor damage. Student notified."
    else:
        suspend_user(
            user_id,
            "Major book damage — replacement required before borrowing resumes.",
        )
        notifications.notify_incident_update(
            user_id,
            "Major Damage — Replacement Required",
            _message_with_admin_notes(
                f"Major damage confirmed. Pay ₱{penalty:.0f} at the library or donate a replacement copy.",
                admin_notes,
            ),
        )
        return True, "Classified as major damage. Student notified and account blocked."


def settle_incident(incident_id, method, admin_notes=""):
    if method not in ("cash", "donation"):
        return False, "Settlement must be cash or donation."

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT book_incidents.user_id, book_incidents.incident_type,
                   book_incidents.status, books.title, book_incidents.penalty_amount
            FROM book_incidents
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.id=?
        """, (incident_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Incident not found."
        user_id, incident_type, status, title, penalty = row
        if status == "settled":
            return False, "Already settled."

        if method == "donation":
            cursor.execute("""
                UPDATE book_incidents
                SET settlement_method='donation', status='donation_pending',
                    admin_notes=?, donation_verified=0
                WHERE id=?
            """, (admin_notes.strip(), incident_id))
            conn.commit()
            notifications.notify_incident_update(
                user_id,
                "Replacement Donation Required",
                f"Please bring a replacement copy of '{title}' to the library for verification.",
            )
            return True, "Donation recorded. Verify the replacement copy before approving."

        cursor.execute("""
            UPDATE book_incidents
            SET settlement_method='cash', status='settled', resolved_at=?, admin_notes=?
            WHERE id=?
        """, (_now(), admin_notes.strip(), incident_id))
        cursor.execute(
            "UPDATE loans SET status='returned', returned=1 WHERE id=(SELECT loan_id FROM book_incidents WHERE id=?)",
            (incident_id,),
        )
        conn.commit()

    _try_lift_suspension(user_id)
    notifications.notify_incident_update(
        user_id,
        f"Payment Received: {title}",
        f"Your payment of ₱{(penalty or 0):.0f} for the {incident_type} book case has been recorded. Thank you!",
    )
    return True, "Cash payment recorded. Case settled and student notified."


def verify_donation(incident_id, approved, admin_notes=""):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT user_id, book_id, status FROM book_incidents WHERE id=?",
            (incident_id,),
        )
        row = cursor.fetchone()
        if not row:
            return False, "Incident not found."
        user_id, book_id, status = row
        if status not in ("donation_pending", "open", "payment_pending"):
            return False, "Incident is not awaiting donation verification."

        if not approved:
            cursor.execute("""
                UPDATE book_incidents
                SET donation_verified=0, status='payment_pending', admin_notes=?
                WHERE id=?
            """, (admin_notes.strip() or "Donation rejected — does not meet standards.", incident_id))
            conn.commit()
            notifications.notify_incident_update(
                user_id,
                "Donation Rejected",
                "Replacement copy rejected. Pay replacement cost instead (Article II, Section 3).",
            )
            return True, "Donation rejected. Student must pay replacement cost."

        cursor.execute("""
            UPDATE book_incidents
            SET donation_verified=1, status='settled', resolved_at=?, admin_notes=?
            WHERE id=?
        """, (_now(), admin_notes.strip(), incident_id))
        cursor.execute(
            "UPDATE books SET quantity = quantity + 1, available = available + 1 WHERE id=?",
            (book_id,),
        )
        cursor.execute(
            "UPDATE loans SET status='returned', returned=1 WHERE id=(SELECT loan_id FROM book_incidents WHERE id=?)",
            (incident_id,),
        )
        conn.commit()

    _try_lift_suspension(user_id)
    notifications.notify_incident_update(
        user_id,
        "Donation Approved",
        "Your replacement copy was verified and accepted. Case resolved.",
    )
    return True, "Donation verified. Book added back to catalog. Case settled."


def _try_lift_suspension(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT COUNT(*) FROM book_incidents
            WHERE user_id=? AND status NOT IN ('settled', 'warning_issued')
        """, (user_id,))
        open_count = cursor.fetchone()[0]
        if open_count == 0:
            lift_suspension(user_id)


def submit_admin_review(incident_id, action, amount=None, admin_notes=""):
    """Review a new (open) student report and set consequences for the student."""
    notes = (admin_notes or "").strip()

    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT incident_type, status FROM book_incidents WHERE id=?",
            (incident_id,),
        )
        row = cursor.fetchone()
        if not row:
            return False, "Report not found."
        incident_type, status = row
        if status != "open":
            return False, "This report was already reviewed. Use the settlement options below."

    if action == "minor_damage":
        if incident_type != "damaged":
            return False, "Minor damage only applies to damage reports."
        return classify_damage(incident_id, "minor", notes)

    if action == "major_damage_pay":
        if incident_type != "damaged":
            return False, "This action is for damage reports only."
        return classify_damage(incident_id, "major", notes)

    if action in ("major_damage_donate", "lost_donate"):
        if action == "major_damage_donate" and incident_type != "damaged":
            return False, "This action is for damage reports only."
        if action == "lost_donate" and incident_type != "lost":
            return False, "This action is for lost book reports."
        return require_donation(incident_id, notes)

    if action == "lost_pay":
        if incident_type != "lost":
            return False, "This action is for lost book reports."
        try:
            pay_amount = float(amount)
        except (TypeError, ValueError):
            return False, "Payment amount must be a number."
        if pay_amount <= 0:
            return False, "Payment amount must be greater than zero."
        return set_payment_required(incident_id, pay_amount, True, notes)

    return False, "Invalid review action."


def get_incident_by_id(incident_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT book_incidents.id, users.username, users.student_number,
                   books.title, book_incidents.incident_type,
                   book_incidents.damage_classification, book_incidents.status,
                   book_incidents.penalty_amount, book_incidents.reported_at,
                   book_incidents.description, book_incidents.user_id,
                   book_incidents.admin_notes, book_incidents.settlement_method
            FROM book_incidents
            JOIN users ON book_incidents.user_id = users.id
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.id=?
        """, (incident_id,))
        return cursor.fetchone()


def get_open_incidents():
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT book_incidents.id, users.username, users.student_number,
                   books.title, book_incidents.incident_type,
                   book_incidents.damage_classification, book_incidents.status,
                   book_incidents.penalty_amount, book_incidents.reported_at,
                   book_incidents.description, book_incidents.user_id
            FROM book_incidents
            JOIN users ON book_incidents.user_id = users.id
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.status NOT IN ('settled', 'warning_issued')
            ORDER BY book_incidents.reported_at DESC
        """)
        return cursor.fetchall()


def get_all_incident_penalties():
    rows = []
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT book_incidents.id, users.username, users.student_number,
                   books.title, book_incidents.incident_type,
                   book_incidents.penalty_amount, book_incidents.status,
                   book_incidents.settlement_method
            FROM book_incidents
            JOIN users ON book_incidents.user_id = users.id
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.status NOT IN ('settled', 'warning_issued')
            AND COALESCE(book_incidents.penalty_amount, 0) > 0
            ORDER BY book_incidents.reported_at DESC
        """)
        for inc_id, username, student_num, title, itype, amount, status, method in cursor.fetchall():
            rows.append({
                "incident_id": inc_id,
                "username": username,
                "student_number": student_num or "-",
                "title": title,
                "incident_type": itype,
                "amount": amount or 0,
                "status": status,
                "settlement_method": method or "pending",
            })
    return rows


def get_student_open_penalties(user_id):
    penalties = []
    total = 0
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT book_incidents.id, books.title, book_incidents.incident_type,
                   book_incidents.status, book_incidents.penalty_amount,
                   book_incidents.settlement_method, book_incidents.reported_at
            FROM book_incidents
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.user_id=?
            AND book_incidents.status NOT IN ('settled', 'warning_issued')
            AND COALESCE(book_incidents.penalty_amount, 0) > 0
            ORDER BY book_incidents.reported_at DESC
        """, (user_id,))
        for inc_id, title, itype, status, amount, method, reported_at in cursor.fetchall():
            penalties.append({
                "incident_id": inc_id,
                "title": title,
                "incident_type": itype,
                "status": status,
                "amount": amount or 0,
                "settlement_method": method or "pending",
                "reported_at": reported_at,
            })
            total += amount or 0
    return penalties, total


def get_student_incidents(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT book_incidents.id, books.title, book_incidents.incident_type,
                   book_incidents.damage_classification, book_incidents.status,
                   book_incidents.penalty_amount, book_incidents.reported_at,
                   book_incidents.admin_notes, book_incidents.description,
                   book_incidents.settlement_method
            FROM book_incidents
            JOIN books ON book_incidents.book_id = books.id
            WHERE book_incidents.user_id=?
            ORDER BY book_incidents.reported_at DESC
        """, (user_id,))
        return cursor.fetchall()


def get_active_loans_with_ids(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.id, books.title, books.author, loans.due_date, loans.status
            FROM loans
            JOIN books ON loans.book_id = books.id
            WHERE loans.user_id=? AND loans.status='borrowed' AND loans.returned=0
            ORDER BY loans.due_date ASC
        """, (user_id,))
        return cursor.fetchall()