import gradio as gr
import os
from datetime import datetime
from supabase import create_client, Client

# ============ SUPABASE CONFIGURATION ============
SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_KEY = os.getenv("SUPABASE_KEY")

if not SUPABASE_URL or not SUPABASE_KEY:
    raise ValueError("❌ SUPABASE_URL or SUPABASE_KEY not set in Space Secrets!")

supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)

# ============ SUPABASE HELPER FUNCTIONS ============
def fetch_books():
    """Fetch all books from Supabase"""
    try:
        res = supabase.table("books").select("*").execute()
        return res.data or []
    except Exception as e:
        print(f"Error fetching books: {e}")
        return []

def fetch_borrows():
    """Fetch all active borrow records from Supabase"""
    try:
        res = supabase.table("borrows").select("*").execute()
        return res.data or []
    except Exception as e:
        print(f"Error fetching borrows: {e}")
        return []

def fetch_history():
    """Fetch all transactions from Supabase"""
    try:
        res = supabase.table("transactions").select("entry").execute()
        entries = [row["entry"] for row in (res.data or [])]
        return entries[-30:]  # Return last 30 entries
    except Exception as e:
        print(f"Error fetching history: {e}")
        return []

def book_titles():
    """Get list of book titles for dropdowns - ALWAYS FRESH FROM DB"""
    books = fetch_books()
    return [b["title"] for b in books]

# ============ DASHBOARD FUNCTIONS ============
def get_dashboard_stats():
    books = fetch_books()
    total_books = len(books)
    total_copies = sum(b["total"] for b in books)
    available_copies = sum(b["copies"] for b in books)
    borrowed_copies = total_copies - available_copies
    
    return f"""
    📊 **Library Dashboard**
    
    📖 Total Books: **{total_books}**
    ✅ Available Copies: **{available_copies}**
    📤 Borrowed Copies: **{borrowed_copies}**
    📦 Total Copies: **{total_copies}**
    """

def get_library_table():
    rows = []
    for b in fetch_books():
        status = "✅ Available" if b["copies"] > 0 else "❌ Out of Stock"
        rows.append([b["title"], b["author"], b["copies"], b["total"], status])
    return rows

# ============ BROWSE FUNCTIONS ============
def browse_books(search_query="", sort_by="Title"):
    books_list = []
    for b in fetch_books():
        if search_query.lower() in b["title"].lower() or search_query.lower() in b["author"].lower():
            books_list.append({
                "Title": b["title"],
                "Author": b["author"],
                "Available": b["copies"],
                "Total": b["total"],
                "Status": "✅ Available" if b["copies"] > 0 else "❌ Out of Stock"
            })
    
    if sort_by == "Title":
        books_list.sort(key=lambda x: x["Title"])
    elif sort_by == "Author":
        books_list.sort(key=lambda x: x["Author"])
    elif sort_by == "Availability":
        books_list.sort(key=lambda x: x["Available"], reverse=True)
    
    return books_list

# ============ BORROW FUNCTION ============
def borrow_book(selected_book, num_copies, name, phone, address):
    if not selected_book or selected_book == "":
        return "❌ Please select a book first"
    
    if not name or not phone or not address:
        return "❌ Please fill in all personal details (Name, Phone, Address)"
    
    try:
        num_copies = int(num_copies) if num_copies else 1
        if num_copies < 1:
            return "❌ Number of copies must be at least 1"
    except Exception as e:
        return f"❌ Invalid number of copies: {e}"
    
    try:
        # Get book from database
        res = supabase.table("books").select("*").eq("title", selected_book).execute()
        books = res.data
        if not books:
            return "❌ Book not found in library"
        
        book = books[0]
        available = book["copies"]
        
        if available < num_copies:
            return f"😔 Sorry, only {available} copies available. You requested {num_copies}"
        
        # Update copies in database
        supabase.table("books").update(
            {"copies": available - num_copies}
        ).eq("id", book["id"]).execute()
        
        # Record borrow
        now = datetime.now().strftime('%Y-%m-%d %H:%M')
        supabase.table("borrows").insert({
            "book_title": selected_book,
            "name": name,
            "phone": phone,
            "address": address,
            "copies": num_copies,
            "date": now
        }).execute()
        
        # Add to transaction history
        entry = f"[{now}] BORROWED - Book: {selected_book} | Copies: {num_copies} | Borrower: {name} | Phone: {phone} | Address: {address}"
        supabase.table("transactions").insert({"entry": entry}).execute()
        
        return f"✅ {name} successfully borrowed {num_copies} copy/copies of '{selected_book}'! Enjoy reading! 📖"
    
    except Exception as e:
        return f"❌ Error processing borrow: {e}"

# ============ RETURN FUNCTION ============
def return_book(selected_book, returner_name):
    if not selected_book or selected_book == "":
        return "❌ Please select a book first"
    
    if not returner_name:
        return "❌ Please enter your name"
    
    try:
        # Find borrow record
        res = supabase.table("borrows").select("*") \
            .eq("book_title", selected_book) \
            .eq("name", returner_name).execute()
        records = res.data or []
        
        if not records:
            return f"❌ No record found for {returner_name} borrowing '{selected_book}'"
        
        record = records[0]
        copies = record["copies"]
        
        # Get book
        res = supabase.table("books").select("*").eq("title", selected_book).execute()
        books = res.data
        if not books:
            return "😑 This book doesn't belong to our library"
        
        book = books[0]
        
        # Update book copies
        supabase.table("books").update(
            {"copies": book["copies"] + copies}
        ).eq("id", book["id"]).execute()
        
        # Delete borrow record
        supabase.table("borrows").delete().eq("id", record["id"]).execute()
        
        # Add to transaction history
        now = datetime.now().strftime('%Y-%m-%d %H:%M')
        entry = f"[{now}] RETURNED - Book: {selected_book} | Copies: {copies} | Returner: {returner_name} | Phone: {record['phone']} | Address: {record['address']}"
        supabase.table("transactions").insert({"entry": entry}).execute()
        
        return f"✅ Thank you {returner_name} for returning {copies} copy/copies of '{selected_book}'! 😊"
    
    except Exception as e:
        return f"❌ Error processing return: {e}"

# ============ ADD BOOK FUNCTION ============
def add_book(title, author, copies):
    if not title or not author or not copies:
        return "❌ Please fill all fields correctly"
    
    try:
        copies = int(copies)
        if copies < 1:
            return "❌ Number of copies must be at least 1"
        
        # Check if book exists
        res = supabase.table("books").select("id").eq("title", title).execute()
        if res.data:
            return f"❌ Book '{title}' already exists in the library"
        
        # Insert new book
        supabase.table("books").insert({
            "title": title,
            "author": author,
            "copies": copies,
            "total": copies
        }).execute()
        
        # Add to transaction history
        entry = f"[{datetime.now().strftime('%Y-%m-%d %H:%M')}] ADDED - Book: {title} by {author} ({copies} copies)"
        supabase.table("transactions").insert({"entry": entry}).execute()
        
        return f"✅ '{title}' successfully added to library! 📚"
    
    except Exception as e:
        return f"❌ Error adding book: {e}"

# ============ HISTORY FUNCTION ============
def get_history():
    rows = fetch_history()
    if not rows:
        return "No transactions yet."
    return "\n".join(rows)

# ============ GET BORROWER DETAILS FUNCTION ============
def get_borrower_details():
    records = fetch_borrows()
    if not records:
        return "No active borrowing records."
    
    details = "📋 **Active Borrowing Records**\n\n"
    by_book = {}
    for r in records:
        by_book.setdefault(r["book_title"], []).append(r)
    
    for title, recs in by_book.items():
        details += f"**📖 {title}**\n"
        for r in recs:
            details += f"  • Name: {r['name']}\n"
            details += f"  • Phone: {r['phone']}\n"
            details += f"  • Address: {r['address']}\n"
            details += f"  • Copies: {r['copies']}\n"
            details += f"  • Borrowed on: {r['date']}\n\n"
    
    return details

# ============ GRADIO INTERFACE ============
demo = gr.Blocks(title="📚 Library Management System")

with demo:
    gr.Markdown("# 📚 Library Management System")
    gr.Markdown("*Your Smart Library Management Solution (Powered by Supabase)*")
    
    # ===== TAB 1: DASHBOARD =====
    with gr.Tab("📊 Dashboard"):
        dashboard_output = gr.Markdown(get_dashboard_stats())
        gr.Markdown("### 📖 Book Collection")
        
        def update_dashboard():
            return get_dashboard_stats()
        
        def refresh_dashboard_table():
            return get_library_table()
        
        demo.load(update_dashboard, outputs=dashboard_output)
        
        library_table = gr.Dataframe(
            value=get_library_table(),
            headers=["Title", "Author", "Available", "Total", "Status"],
            interactive=False,
            label="📚 Books in Library"
        )
        
        refresh_dashboard_btn = gr.Button("🔄 Refresh Dashboard")
        refresh_dashboard_btn.click(update_dashboard, outputs=dashboard_output)
        refresh_dashboard_btn.click(refresh_dashboard_table, outputs=library_table)
    
    # ===== TAB 2: BROWSE BOOKS =====
    with gr.Tab("🔍 Browse Books"):
        search_input = gr.Textbox(label="Search by title or author", placeholder="e.g., 'Atomic' or 'James Clear'")
        sort_dropdown = gr.Dropdown(
            choices=["Title", "Author", "Availability"],
            value="Title",
            label="Sort by"
        )
        
        browse_output = gr.Dataframe(
            headers=["Title", "Author", "Available", "Total", "Status"],
            interactive=False,
            label="🔎 Search Results"
        )
        
        def update_browse(search, sort):
            results = browse_books(search, sort)
            if results:
                return [[r["Title"], r["Author"], r["Available"], r["Total"], r["Status"]] for r in results]
            return []
        
        search_input.change(update_browse, inputs=[search_input, sort_dropdown], outputs=browse_output)
        sort_dropdown.change(update_browse, inputs=[search_input, sort_dropdown], outputs=browse_output)
    
    # ===== TAB 3: BORROW BOOK =====
    with gr.Tab("📤 Borrow Book"):
        gr.Markdown("### Borrow a book from the library")
        
        books_list = book_titles()
        
        with gr.Row():
            with gr.Column(scale=1):
                borrow_select = gr.Dropdown(
                    choices=books_list if books_list else ["No books available"],
                    value=books_list[0] if books_list else None,
                    label="Select Book *",
                    interactive=True
                )
                
                borrow_copies = gr.Slider(
                    label="Number of Copies to Borrow *",
                    minimum=1,
                    maximum=10,
                    step=1,
                    value=1
                )
            
            with gr.Column(scale=1):
                borrower_name = gr.Textbox(
                    label="Your Name *",
                    placeholder="e.g., 'John Doe'"
                )
                
                borrower_phone = gr.Textbox(
                    label="Your Phone Number *",
                    placeholder="e.g., '9876543210'"
                )
        
        borrower_address = gr.Textbox(
            label="Your Address *",
            placeholder="e.g., '123 Main St, City'"
        )
        
        borrow_output = gr.Textbox(label="Result", interactive=False)
        borrow_btn = gr.Button("📖 Borrow Books")
        refresh_borrow_btn = gr.Button("🔄 Refresh Book List")
        
        borrow_btn.click(
            borrow_book,
            inputs=[borrow_select, borrow_copies, borrower_name, borrower_phone, borrower_address],
            outputs=borrow_output
        )
        
        def refresh_borrow_list():
            titles = book_titles()
            return gr.Dropdown.update(
                choices=titles if titles else ["No books available"],
                value=titles[0] if titles else None
            )
        
        refresh_borrow_btn.click(refresh_borrow_list, outputs=borrow_select)
    
    # ===== TAB 4: RETURN BOOK =====
    with gr.Tab("↩️ Return Book"):
        gr.Markdown("### Return a borrowed book")
        
        books_list = book_titles()
        
        with gr.Row():
            return_select = gr.Dropdown(
                choices=books_list if books_list else ["No books available"],
                value=books_list[0] if books_list else None,
                label="Select Book to Return *",
                interactive=True,
                scale=1
            )
            
            returner_name = gr.Textbox(
                label="Your Name (as per borrow record) *",
                placeholder="e.g., 'John Doe'",
                scale=1
            )
        
        return_output = gr.Textbox(label="Result", interactive=False)
        return_btn = gr.Button("↩️ Return This Book")
        refresh_return_btn = gr.Button("🔄 Refresh Book List")
        
        return_btn.click(
            return_book,
            inputs=[return_select, returner_name],
            outputs=return_output
        )
        
        def refresh_return_list():
            titles = book_titles()
            return gr.Dropdown.update(
                choices=titles if titles else ["No books available"],
                value=titles[0] if titles else None
            )
        
        refresh_return_btn.click(refresh_return_list, outputs=return_select)
    
    # ===== TAB 5: ADD BOOK =====
    with gr.Tab("➕ Add Book"):
        gr.Markdown("### Add a new book to the library")
        
        with gr.Row():
            title_input = gr.Textbox(label="Book Title *", placeholder="e.g., 'The Alchemist'", scale=1)
            author_input = gr.Textbox(label="Author Name *", placeholder="e.g., 'Paulo Coelho'", scale=1)
        
        copies_input = gr.Slider(label="Number of Copies *", minimum=1, maximum=100, step=1, value=1)
        
        add_output = gr.Textbox(label="Result", interactive=False)
        add_btn = gr.Button("➕ Add Book to Library")
        
        add_btn.click(add_book, inputs=[title_input, author_input, copies_input], outputs=add_output)
    
    # ===== TAB 6: BORROWER DETAILS =====
    with gr.Tab("👥 Active Borrowers"):
        gr.Markdown("### Currently active borrowing records")
        borrower_details = gr.Markdown(get_borrower_details())
        
        refresh_borrowers_btn = gr.Button("🔄 Refresh Records")
        refresh_borrowers_btn.click(get_borrower_details, outputs=borrower_details)
    
    # ===== TAB 7: HISTORY =====
    with gr.Tab("📜 Transaction History"):
        gr.Markdown("### All transactions (Borrow, Return, Add)")
        history_output = gr.Textbox(
            label="History",
            value=get_history(),
            interactive=False,
            lines=20
        )
        
        refresh_btn = gr.Button("🔄 Refresh History")
        refresh_btn.click(get_history, outputs=history_output)

# Launch the app
if __name__ == "__main__":
    demo.launch()