import os
import re
import socket
import sqlite3
from datetime import date, datetime

DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "library.db")

MAX_BORROW_LIMIT = 3
FINE_PER_DAY = 5
LOW_STOCK_THRESHOLD = 1
MINOR_DAMAGE_FEE = 50
PROCESSING_FEE = 100
DEFAULT_REPLACEMENT_COST = 500


def connect():
    conn = sqlite3.connect(DB_PATH, timeout=10)
    conn.execute("PRAGMA journal_mode=WAL;")
    return conn


def parse_loan_date(date_str):
    """Parse loan dates stored as YYYY-MM-DD or YYYY-M-D."""
    if not date_str:
        return None

    text = str(date_str).strip()
    if not text:
        return None

    try:
        return datetime.strptime(text, "%Y-%m-%d").date()
    except ValueError:
        pass

    for sep in ("-", "/"):
        if sep in text:
            parts = text.split(sep)
            if len(parts) == 3:
                try:
                    return date(int(parts[0]), int(parts[1]), int(parts[2]))
                except (TypeError, ValueError):
                    return None
    return None


def normalize_date_str(date_str):
    parsed = parse_loan_date(date_str)
    return parsed.strftime("%Y-%m-%d") if parsed else None


def is_date_overdue(date_str, on_date=None):
    due = parse_loan_date(date_str)
    if not due:
        return False
    ref = on_date or date.today()
    return due < ref


def _add_column_if_missing(cursor, table, column, definition):
    cursor.execute(f"PRAGMA table_info({table})")
    columns = {row[1] for row in cursor.fetchall()}
    if column not in columns:
        cursor.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")


def setup_database():
    with connect() as conn:
        cursor = conn.cursor()

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE,
            password TEXT,
            role TEXT,
            student_number TEXT UNIQUE,
            email TEXT,
            is_suspended INTEGER DEFAULT 0,
            suspension_reason TEXT
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT,
            author TEXT,
            shelf TEXT,
            description TEXT DEFAULT '',
            quantity INTEGER DEFAULT 1,
            available INTEGER DEFAULT 1
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS loans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_id INTEGER,
            user_id INTEGER,
            borrow_date TEXT,
            due_date TEXT,
            returned INTEGER DEFAULT 0,
            status TEXT DEFAULT 'pending',
            fine_paid INTEGER DEFAULT 0
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS reservations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_id INTEGER,
            user_id INTEGER,
            status TEXT DEFAULT 'waiting',
            created_at TEXT,
            notified_at TEXT
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS notifications (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            title TEXT,
            message TEXT,
            notification_type TEXT DEFAULT 'general',
            is_read INTEGER DEFAULT 0,
            email_sent INTEGER DEFAULT 0,
            created_at TEXT,
            related_loan_id INTEGER
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS book_incidents (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            loan_id INTEGER,
            user_id INTEGER,
            book_id INTEGER,
            incident_type TEXT,
            damage_classification TEXT,
            description TEXT,
            reported_at TEXT,
            reported_when TEXT,
            penalty_amount REAL DEFAULT 0,
            processing_fee REAL DEFAULT 0,
            replacement_cost REAL DEFAULT 0,
            status TEXT DEFAULT 'open',
            settlement_method TEXT,
            donation_verified INTEGER DEFAULT 0,
            resolved_at TEXT,
            admin_notes TEXT
        )
        """)

        _add_column_if_missing(cursor, "loans", "fine_paid", "INTEGER DEFAULT 0")
        _add_column_if_missing(cursor, "users", "email", "TEXT")
        _add_column_if_missing(cursor, "users", "is_suspended", "INTEGER DEFAULT 0")
        _add_column_if_missing(cursor, "users", "suspension_reason", "TEXT")

        cursor.execute("SELECT id, due_date FROM loans WHERE due_date IS NOT NULL AND due_date != ''")
        for loan_id, due_date in cursor.fetchall():
            normalized = normalize_date_str(due_date)
            if normalized and normalized != due_date:
                cursor.execute("UPDATE loans SET due_date=? WHERE id=?", (normalized, loan_id))

        conn.commit()


def _is_valid_email(email):
    email = (email or "").strip().lower()
    if not email or len(email) > 254:
        return False, "Email is required."

    pattern = re.compile(
        r"^[a-zA-Z0-9](?:[a-zA-Z0-9._%+-]{0,62}[a-zA-Z0-9])?"
        r"@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?"
        r"\.[a-zA-Z]{2,}$"
    )
    if not pattern.match(email):
        return False, "Invalid email format. Example: name@school.edu"

    local, domain = email.rsplit("@", 1)
    blocked_domains = {
        "example.com", "example.org", "example.net", "test.com",
        "localhost", "invalid", "fake.com", "noemail.com",
    }
    if domain in blocked_domains:
        return False, "Please use a real email address, not a placeholder."

    if domain.endswith(".edu") or domain.endswith(".edu.ph") or ".ac." in domain:
        return True, email

    fake_locals = {"test", "fake", "noemail", "none", "asdf", "xxx"}
    if local in fake_locals and domain in {"gmail.com", "yahoo.com", "test.com", "email.com"}:
        return False, "Please use your real school email address."

    if ".." in email or email.startswith(".") or "@" not in email:
        return False, "Invalid email address."

    try:
        socket.getaddrinfo(domain, None)
    except socket.gaierror:
        return False, f"Email domain '{domain}' could not be verified. Use an active email."

    return True, email


def create_default_users():
    users = [
        ("admin", "admin123", "admin", None, None),
        ("student", "student123", "student", "S123456", "student@school.edu"),
    ]

    with connect() as conn:
        cursor = conn.cursor()
        for user in users:
            try:
                cursor.execute(
                    "INSERT INTO users (username, password, role, student_number, email) VALUES (?, ?, ?, ?, ?)",
                    user,
                )
            except sqlite3.IntegrityError:
                pass
        conn.commit()


def _normalize_username(username):
    return username.strip()


def _find_user_by_username(cursor, username):
    cursor.execute(
        "SELECT id, username, role FROM users WHERE LOWER(username)=LOWER(?)",
        (_normalize_username(username),),
    )
    return cursor.fetchone()


def username_exists(username):
    with connect() as conn:
        cursor = conn.cursor()
        return _find_user_by_username(cursor, username) is not None


def student_number_exists(student_number):
    student_number = student_number.strip()
    if not student_number:
        return False
    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT id FROM users WHERE student_number=? COLLATE NOCASE",
            (student_number,),
        )
        return cursor.fetchone() is not None


def email_exists(email):
    email = (email or "").strip().lower()
    if not email:
        return False
    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT id FROM users WHERE LOWER(email)=LOWER(?)",
            (email,),
        )
        return cursor.fetchone() is not None


def register_user(username, password, role, student_number=None, email=None):
    username = _normalize_username(username)
    password = password.strip()
    role = role.strip().lower()
    student_number = student_number.strip() if student_number else None
    email = (email or "").strip() if email else None

    if not username or not password:
        return False, "Username and password are required."

    if len(username) < 3:
        return False, "Username must be at least 3 characters."

    if len(password) < 4:
        return False, "Password must be at least 4 characters."

    if role not in ("student", "admin"):
        return False, "Invalid account type."

    if role == "student" and not student_number:
        return False, "Student number is required."

    if role == "student":
        if not email:
            return False, "Email is required for student accounts."
        valid, email_or_error = _is_valid_email(email)
        if not valid:
            return False, email_or_error
        email = email_or_error

    if username_exists(username):
        return False, f"Username '{username}' is already taken."

    if role == "student" and student_number_exists(student_number):
        return False, f"Student number '{student_number}' is already registered."

    if role == "student" and email_exists(email):
        return False, f"Email '{email}' is already registered."

    with connect() as conn:
        cursor = conn.cursor()
        try:
            cursor.execute(
                "INSERT INTO users (username, password, role, student_number, email) VALUES (?, ?, ?, ?, ?)",
                (username, password, role, student_number, email),
            )
            conn.commit()
        except sqlite3.IntegrityError:
            return False, "Username, student number, or email already exists."

    return True, f"Account '{username}' created. You can now sign in."


def register_student(username, password, student_number, email=None):
    return register_user(username, password, "student", student_number, email)


def register_admin(username, password):
    return register_user(username, password, "admin", None, None)


def authenticate(username, password):
    username = _normalize_username(username)
    password = password.strip()

    if not username or not password:
        return None

    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT id, role FROM users WHERE LOWER(username)=LOWER(?) AND password=?",
            (username, password),
        )
        return cursor.fetchone()


def get_all_users():
    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, username, role, student_number, email, is_suspended
            FROM users
            ORDER BY role DESC, username ASC
        """)
        return cursor.fetchall()


def get_user_profile(user_id):
    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT username, email, student_number, is_suspended, suspension_reason FROM users WHERE id=?",
            (user_id,),
        )
        return cursor.fetchone()


def delete_student_account(user_id):
    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT role, username FROM users WHERE id=?", (user_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Account not found."
        role, username = row
        if role != "student":
            return False, "Only student accounts can be deleted. Admin accounts cannot be removed here."

        cursor.execute("""
            SELECT COUNT(*) FROM loans
            WHERE user_id=? AND returned=0 AND status IN ('borrowed', 'pending')
        """, (user_id,))
        if cursor.fetchone()[0] > 0:
            return False, (
                f"Cannot delete '{username}': student still has borrowed books or pending requests. "
                "Mark books returned first."
            )

        cursor.execute("""
            SELECT COUNT(*) FROM book_incidents
            WHERE user_id=? AND status NOT IN ('settled', 'warning_issued')
        """, (user_id,))
        if cursor.fetchone()[0] > 0:
            return False, f"Cannot delete '{username}': student has unresolved lost/damage cases."

        cursor.execute("DELETE FROM notifications WHERE user_id=?", (user_id,))
        cursor.execute("DELETE FROM reservations WHERE user_id=?", (user_id,))
        cursor.execute("DELETE FROM book_incidents WHERE user_id=?", (user_id,))
        cursor.execute("DELETE FROM loans WHERE user_id=?", (user_id,))
        cursor.execute("DELETE FROM users WHERE id=?", (user_id,))
        if cursor.rowcount == 0:
            return False, "Account could not be deleted. Please try again."

        conn.commit()

        cursor.execute("SELECT id FROM users WHERE id=?", (user_id,))
        if cursor.fetchone():
            return False, "Account could not be removed from the database."

    return True, f"Student account '{username}' has been deleted."


def block_student_account(user_id, reason=""):
    reason = (reason or "Account blocked by librarian — unpaid lost/damage case.").strip()
    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT role, username FROM users WHERE id=?", (user_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Account not found."
        role, username = row
        if role != "student":
            return False, "Only student accounts can be blocked."
        cursor.execute(
            "UPDATE users SET is_suspended=1, suspension_reason=? WHERE id=?",
            (reason, user_id),
        )
        conn.commit()

    import notifications
    notifications.notify_suspension(user_id, reason)
    return True, f"Student '{username}' has been blocked."


def unblock_student_account(user_id):
    with connect() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT role, username FROM users WHERE id=?", (user_id,))
        row = cursor.fetchone()
        if not row:
            return False, "Account not found."
        role, username = row
        if role != "student":
            return False, "Only student accounts can be unblocked."
        cursor.execute(
            "UPDATE users SET is_suspended=0, suspension_reason=NULL WHERE id=?",
            (user_id,),
        )
        conn.commit()

    import notifications
    notifications.create_notification(
        user_id,
        "Account Unblocked",
        "Your account has been unblocked. You may borrow books again.",
        notification_type="general",
    )
    return True, f"Student '{username}' has been unblocked."