from datetime import datetime, date
import database
import email_service


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


def _today():
    return datetime.now().strftime("%Y-%m-%d")


def create_notification(user_id, title, message, notification_type="general", related_loan_id=None):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO notifications (user_id, title, message, notification_type, created_at, related_loan_id)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (user_id, title, message, notification_type, _now(), related_loan_id))
        conn.commit()
        return cursor.lastrowid


def get_user_notifications(user_id, unread_only=False):
    with database.connect() as conn:
        cursor = conn.cursor()
        query = """
            SELECT id, title, message, notification_type, is_read, email_sent, created_at
            FROM notifications
            WHERE user_id=?
        """
        if unread_only:
            query += " AND is_read=0"
        query += " ORDER BY created_at DESC"
        cursor.execute(query, (user_id,))
        return cursor.fetchall()


def get_unread_count(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT COUNT(*) FROM notifications WHERE user_id=? AND is_read=0",
            (user_id,),
        )
        return cursor.fetchone()[0]


def mark_read(notification_id, user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "UPDATE notifications SET is_read=1 WHERE id=? AND user_id=?",
            (notification_id, user_id),
        )
        conn.commit()
        return cursor.rowcount > 0


def mark_all_read(user_id):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "UPDATE notifications SET is_read=1 WHERE user_id=? AND is_read=0",
            (user_id,),
        )
        conn.commit()


def _already_notified_today(user_id, loan_id, notification_type):
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id FROM notifications
            WHERE user_id=? AND related_loan_id=? AND notification_type=?
            AND created_at >= ?
        """, (user_id, loan_id, notification_type, _today()))
        return cursor.fetchone() is not None


def sync_overdue_alerts(user_id, send_email=True):
    """Create in-app alerts and optionally email for overdue borrowed books."""
    import circulation

    today = date.today()
    overdue_items = []
    with database.connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT loans.id, books.title, loans.due_date
            FROM loans
            JOIN books ON loans.book_id = books.id
            WHERE loans.user_id=?
            AND loans.status='borrowed'
            AND loans.returned=0
            AND loans.due_date IS NOT NULL
        """, (user_id,))
        rows = cursor.fetchall()

    for loan_id, title, due_date in rows:
        due = database.parse_loan_date(due_date)
        if not due or due >= today:
            continue

        days_late, fine_amount = circulation.calculate_fine(due_date)
        if days_late <= 0:
            continue

        overdue_items.append({
            "loan_id": loan_id,
            "title": title,
            "due_date": due.strftime("%Y-%m-%d"),
            "days_late": days_late,
            "fine_amount": fine_amount,
        })

        if not _already_notified_today(user_id, loan_id, "overdue"):
            message = (
                f"Your borrowed book '{title}' is overdue. "
                f"Due date was {due.strftime('%Y-%m-%d')} ({days_late} day(s) late). "
                f"Current fine: ₱{fine_amount:.0f}. Please return it immediately."
            )
            create_notification(
                user_id,
                f"OVERDUE: {title}",
                message,
                notification_type="overdue",
                related_loan_id=loan_id,
            )

    if send_email and overdue_items:
        email_service.send_overdue_reminder(user_id, overdue_items)

    return overdue_items


def notify_suspension(user_id, reason):
    create_notification(
        user_id,
        "Borrowing Privileges Suspended",
        reason,
        notification_type="suspension",
    )


def notify_incident_update(user_id, title, message):
    create_notification(user_id, title, message, notification_type="incident")