import streamlit as st
import sqlite3
from datetime import datetime

# ------------------------- DATABASE ------------------------- #
conn = sqlite3.connect('library.db', check_same_thread=False)
c = conn.cursor()

def init_db():
    c.execute('''CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT,
        author TEXT,
        genre TEXT,
        isbn TEXT UNIQUE,
        status TEXT DEFAULT 'available'
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS members (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        email TEXT UNIQUE,
        phone TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_id INTEGER,
        member_id INTEGER,
        action TEXT,
        date TEXT,
        FOREIGN KEY(book_id) REFERENCES books(id),
        FOREIGN KEY(member_id) REFERENCES members(id)
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS reservations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_id INTEGER,
        member_id INTEGER,
        reserved_at TEXT,
        FOREIGN KEY(book_id) REFERENCES books(id),
        FOREIGN KEY(member_id) REFERENCES members(id)
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE,
        password TEXT,
        role TEXT DEFAULT 'member'
    )''')

    conn.commit()

# ------------------------- FUNCTIONS ------------------------- #
def add_book(title, author, genre, isbn):
    c.execute("INSERT INTO books (title, author, genre, isbn) VALUES (?, ?, ?, ?)", (title, author, genre, isbn))
    conn.commit()

def get_books():
    c.execute("SELECT * FROM books")
    return c.fetchall()

def issue_book(book_id, member_id):
    c.execute("UPDATE books SET status='issued' WHERE id=?", (book_id,))
    c.execute("INSERT INTO transactions (book_id, member_id, action, date) VALUES (?, ?, 'issued', ?)", (book_id, member_id, datetime.now()))
    conn.commit()

def return_book(book_id, member_id):
    c.execute("UPDATE books SET status='available' WHERE id=?", (book_id,))
    c.execute("INSERT INTO transactions (book_id, member_id, action, date) VALUES (?, ?, 'returned', ?)", (book_id, member_id, datetime.now()))
    conn.commit()

def reserve_book(book_id, member_id):
    c.execute("INSERT INTO reservations (book_id, member_id, reserved_at) VALUES (?, ?, ?)", (book_id, member_id, datetime.now()))
    conn.commit()

def add_member(name, email, phone):
    c.execute("INSERT INTO members (name, email, phone) VALUES (?, ?, ?)", (name, email, phone))
    conn.commit()

def get_members():
    c.execute("SELECT * FROM members")
    return c.fetchall()

def add_user(username, password, role='member'):
    c.execute("INSERT INTO users (username, password, role) VALUES (?, ?, ?)", (username, password, role))
    conn.commit()

def authenticate_user(username, password):
    c.execute("SELECT * FROM users WHERE username=? AND password=?", (username, password))
    return c.fetchone()

def get_transactions():
    c.execute("""SELECT t.id, b.title, m.name, t.action, t.date FROM transactions t 
              JOIN books b ON t.book_id = b.id 
              JOIN members m ON t.member_id = m.id ORDER BY t.date DESC""")
    return c.fetchall()

def get_reservations():
    c.execute("""SELECT r.id, b.title, m.name, r.reserved_at FROM reservations r
              JOIN books b ON r.book_id = b.id
              JOIN members m ON r.member_id = m.id""")
    return c.fetchall()

def get_most_borrowed_books():
    c.execute("SELECT b.title, COUNT(*) as borrow_count FROM transactions t JOIN books b ON t.book_id = b.id WHERE t.action='issued' GROUP BY b.title ORDER BY borrow_count DESC LIMIT 5")
    return c.fetchall()

# ------------------------- STREAMLIT UI ------------------------- #
st.set_page_config(page_title="Library Management System", layout="wide")

if 'logged_in' not in st.session_state:
    st.session_state.logged_in = False
    st.session_state.role = ''

init_db()

if not st.session_state.logged_in:
    st.title("🔐 Login")
    with st.form("login"):
        username = st.text_input("Username")
        password = st.text_input("Password", type="password")
        submitted = st.form_submit_button("Login")
        if submitted:
            user = authenticate_user(username, password)
            if user:
                st.session_state.logged_in = True
                st.session_state.username = username
                st.session_state.role = user[3]  # role
                st.experimental_rerun()
            else:
                st.error("Invalid credentials")
else:
    st.title("📚 Library Management System")
    menu = ["Dashboard", "Add Book", "Issue/Return Book", "Reserve Book", "Audit Logs"]
    if st.session_state.role == 'admin':
        menu.append("Users")
    choice = st.sidebar.selectbox("Menu", menu)

    if choice == "Dashboard":
        st.subheader("Library Overview")
        books = get_books()
        members = get_members()
        st.metric("Total Books", len(books))
        st.metric("Total Members", len(members))
        st.metric("Issued Books", sum(1 for b in books if b[5] == 'issued'))
        st.metric("Available Books", sum(1 for b in books if b[5] == 'available'))

        st.subheader("📊 Most Borrowed Books")
        popular = get_most_borrowed_books()
        for title, count in popular:
            st.write(f"🔹 {title}: {count} times")

    elif choice == "Add Book":
        st.subheader("Add a New Book")
        with st.form("add_book"):
            title = st.text_input("Title")
            author = st.text_input("Author")
            genre = st.text_input("Genre")
            isbn = st.text_input("ISBN")
            submitted = st.form_submit_button("Add Book")
            if submitted:
                add_book(title, author, genre, isbn)
                st.success("✅ Book added successfully")

    elif choice == "Issue/Return Book":
        st.subheader("Manage Book Transactions")
        books = get_books()
        members = get_members()
        book_dict = {f"{b[1]} ({b[4]})": b[0] for b in books}
        member_dict = {f"{m[1]} ({m[2]})": m[0] for m in members}
        tab1, tab2 = st.tabs(["📦 Issue", "📥 Return"])

        with tab1:
            selected_book = st.selectbox("Select Book to Issue", list(book_dict.keys()))
            selected_member = st.selectbox("Select Member", list(member_dict.keys()))
            if st.button("Issue Book"):
                issue_book(book_dict[selected_book], member_dict[selected_member])
                st.success("📦 Book Issued")

        with tab2:
            selected_book = st.selectbox("Select Book to Return", list(book_dict.keys()), key='return')
            selected_member = st.selectbox("Select Member", list(member_dict.keys()), key='return_mem')
            if st.button("Return Book"):
                return_book(book_dict[selected_book], member_dict[selected_member])
                st.success("📥 Book Returned")

    elif choice == "Reserve Book":
        st.subheader("Reserve a Book")
        books = get_books()
        members = get_members()
        available_books = [b for b in books if b[5] == 'available']
        book_dict = {f"{b[1]} ({b[4]})": b[0] for b in available_books}
        member_dict = {f"{m[1]} ({m[2]})": m[0] for m in members}
        if book_dict and member_dict:
            selected_book = st.selectbox("Select Book", list(book_dict.keys()))
            selected_member = st.selectbox("Select Member", list(member_dict.keys()))
            if st.button("Reserve Book"):
                reserve_book(book_dict[selected_book], member_dict[selected_member])
                st.success("🔒 Book Reserved")
        else:
            st.warning("No available books or members to reserve")

    elif choice == "Audit Logs":
        st.subheader("Transaction History")
        logs = get_transactions()
        st.dataframe(logs, use_container_width=True)
        st.subheader("Reservation Logs")
        res = get_reservations()
        st.dataframe(res, use_container_width=True)

    elif choice == "Users" and st.session_state.role == 'admin':
        st.subheader("User Management")
        with st.form("add_user"):
            username = st.text_input("Username")
            password = st.text_input("Password", type="password")
            role = st.selectbox("Role", ["admin", "librarian", "member"])
            if st.form_submit_button("Add User"):
                add_user(username, password, role)
                st.success("👤 User Added")

    st.sidebar.write("---")
    if st.sidebar.button("Logout"):
        st.session_state.logged_in = False
        st.experimental_rerun()