import gradio as gr
import pandas as pd
from datetime import datetime, date
from supabase import create_client, Client

class LibraryManagement:
    def __init__(self):
        """Initialize Supabase connection for library management"""
        self.url = "https://bnjblzcqaumctpehgoid.supabase.co"
        self.key = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImJuamJsemNxYXVtY3RwZWhnb2lkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgyOTA0OTQsImV4cCI6MjA3Mzg2NjQ5NH0.L4wPzuBj9dlSKpYxO1eDX-57KcP0mbNfN8stmTB-STM"
        
        try:
            self.supabase: Client = create_client(self.url, self.key)
            self.connection_status = "✅ Connected to Library Database!"
        except Exception as e:
            self.supabase = None
            self.connection_status = f"❌ Connection failed: {str(e)}"
    
    def get_all_members(self):
        """Get all library members"""
        try:
            result = self.supabase.table("member").select("*").order("name").execute()
            return pd.DataFrame(result.data)
        except Exception as e:
            return pd.DataFrame([{"Error": str(e)}])
    
    def get_all_books(self):
        """Get all books"""
        try:
            result = self.supabase.table("book").select("*").order("title").execute()
            return pd.DataFrame(result.data)
        except Exception as e:
            return pd.DataFrame([{"Error": str(e)}])
    
    def get_current_borrows(self):
        """Get all current (unreturned) book borrows with member and book details"""
        try:
            # Note: Supabase doesn't support complex JOINs in the Python client directly
            # We'll get the data separately and merge it
            
            # Get active borrows
            borrows = self.supabase.table("borrow").select("*").is_("return_date", "null").execute()
            
            if not borrows.data:
                return pd.DataFrame([{"Message": "No current borrows"}])
            
            # Get members and books to join the data
            members = self.supabase.table("member").select("*").execute()
            books = self.supabase.table("book").select("*").execute()
            
            # Create lookup dictionaries
            member_dict = {m['id']: m for m in members.data}
            book_dict = {b['id']: b for b in books.data}
            
            # Build the result
            result_data = []
            for borrow in borrows.data:
                member = member_dict.get(borrow['member_id'], {})
                book = book_dict.get(borrow['book_id'], {})
                
                result_data.append({
                    'borrow_id': borrow['id'],
                    'member_name': member.get('name', 'Unknown'),
                    'member_email': member.get('email', 'Unknown'),
                    'book_title': book.get('title', 'Unknown'),
                    'book_author': book.get('author', 'Unknown'),
                    'borrow_date': borrow['borrow_date']
                })
            
            return pd.DataFrame(result_data)
            
        except Exception as e:
            return pd.DataFrame([{"Error": str(e)}])
    
    def add_member(self, name: str, email: str):
        """Add a new member"""
        try:
            if not name or not email:
                return "❌ Name and email are required"
            
            data = {"name": name, "email": email}
            result = self.supabase.table("member").insert(data).execute()
            
            if result.data:
                return f"✅ Successfully added member: {name}"
            else:
                return "❌ Failed to add member"
                
        except Exception as e:
            if "duplicate key" in str(e):
                return "❌ Email already exists"
            return f"❌ Error: {str(e)}"
    
    def add_book(self, title: str, author: str, year: int):
        """Add a new book"""
        try:
            if not title or not author:
                return "❌ Title and author are required"
            
            if year < 0 or year > datetime.now().year + 1:
                return "❌ Invalid year"
            
            data = {"title": title, "author": author, "year": year}
            result = self.supabase.table("book").insert(data).execute()
            
            if result.data:
                return f"✅ Successfully added book: {title}"
            else:
                return "❌ Failed to add book"
                
        except Exception as e:
            return f"❌ Error: {str(e)}"
    
    def borrow_book(self, member_id: int, book_id: int):
        """Create a new borrow record"""
        try:
            if not member_id or not book_id:
                return "❌ Please select both member and book"
            
            # Check if book is already borrowed
            existing = self.supabase.table("borrow").select("*").eq("book_id", book_id).is_("return_date", "null").execute()
            
            if existing.data:
                return "❌ Book is already borrowed and not returned"
            
            data = {
                "member_id": member_id,
                "book_id": book_id,
                "borrow_date": date.today().isoformat()
            }
            
            result = self.supabase.table("borrow").insert(data).execute()
            
            if result.data:
                return f"✅ Book borrowed successfully!"
            else:
                return "❌ Failed to create borrow record"
                
        except Exception as e:
            return f"❌ Error: {str(e)}"
    
    def return_book(self, borrow_id: int):
        """Return a borrowed book"""
        try:
            if not borrow_id:
                return "❌ Please select a borrow record"
            
            # Update the borrow record with return date
            data = {"return_date": date.today().isoformat()}
            result = self.supabase.table("borrow").update(data).eq("id", borrow_id).execute()
            
            if result.data:
                return f"✅ Book returned successfully!"
            else:
                return "❌ Failed to return book"
                
        except Exception as e:
            return f"❌ Error: {str(e)}"
    
    def get_member_choices(self):
        """Get member choices for dropdown"""
        try:
            result = self.supabase.table("member").select("id, name").order("name").execute()
            return [(f"{m['name']} (ID: {m['id']})", m['id']) for m in result.data]
        except:
            return [("No members found", 0)]
    
    def get_book_choices(self):
        """Get book choices for dropdown"""
        try:
            result = self.supabase.table("book").select("id, title, author").order("title").execute()
            return [(f"{b['title']} by {b['author']} (ID: {b['id']})", b['id']) for b in result.data]
        except:
            return [("No books found", 0)]
    
    def get_borrow_choices(self):
        """Get active borrow choices for dropdown"""
        try:
            # Get active borrows with member and book info
            borrows = self.supabase.table("borrow").select("*").is_("return_date", "null").execute()
            
            if not borrows.data:
                return [("No active borrows", 0)]
            
            # Get members and books for display
            members = self.supabase.table("member").select("*").execute()
            books = self.supabase.table("book").select("*").execute()
            
            member_dict = {m['id']: m for m in members.data}
            book_dict = {b['id']: b for b in books.data}
            
            choices = []
            for borrow in borrows.data:
                member = member_dict.get(borrow['member_id'], {})
                book = book_dict.get(borrow['book_id'], {})
                
                label = f"{member.get('name', 'Unknown')} - {book.get('title', 'Unknown')} (Borrow ID: {borrow['id']})"
                choices.append((label, borrow['id']))
            
            return choices
        except:
            return [("Error loading borrows", 0)]

# Initialize the library management system
library = LibraryManagement()

# Gradio interface functions
def refresh_members():
    return library.get_all_members()

def refresh_books():
    return library.get_all_books()

def refresh_borrows():
    return library.get_current_borrows()

def add_new_member(name, email):
    status = library.add_member(name, email)
    return status, library.get_all_members()

def add_new_book(title, author, year):
    status = library.add_book(title, author, year)
    return status, library.get_all_books()

def create_borrow(member_choice, book_choice):
    member_id = member_choice if isinstance(member_choice, int) else 0
    book_id = book_choice if isinstance(book_choice, int) else 0
    status = library.borrow_book(member_id, book_id)
    return status, library.get_current_borrows()

def process_return(borrow_choice):
    borrow_id = borrow_choice if isinstance(borrow_choice, int) else 0
    status = library.return_book(borrow_id)
    return status, library.get_current_borrows()

def refresh_member_dropdown():
    return gr.Dropdown(choices=library.get_member_choices())

def refresh_book_dropdown():
    return gr.Dropdown(choices=library.get_book_choices())

def refresh_borrow_dropdown():
    return gr.Dropdown(choices=library.get_borrow_choices())

# Create the Gradio interface
with gr.Blocks(title="Library Management System", theme=gr.themes.Soft()) as app:
    gr.Markdown("# 📚 Library Management System")
    gr.Markdown("Manage your library members, books, and borrowing records")
    
    # Connection status
    with gr.Row():
        gr.Markdown(f"**Status:** {library.connection_status}")
    
    with gr.Tabs():
        # Members Tab
        with gr.Tab("👥 Members"):
            gr.Markdown("### Library Members")
            
            with gr.Row():
                refresh_members_btn = gr.Button("🔄 Refresh Members", size="sm")
            
            members_table = gr.Dataframe(label="All Members", value=library.get_all_members())
            
            gr.Markdown("---")
            gr.Markdown("**Add New Member:**")
            
            with gr.Row():
                member_name = gr.Textbox(label="Name", placeholder="Enter member name")
                member_email = gr.Textbox(label="Email", placeholder="Enter email address")
            
            add_member_btn = gr.Button("➕ Add Member", variant="primary")
            member_status = gr.Textbox(label="Status", interactive=False)
            
            refresh_members_btn.click(refresh_members, outputs=members_table)
            add_member_btn.click(
                add_new_member,
                inputs=[member_name, member_email],
                outputs=[member_status, members_table]
            )
        
        # Books Tab
        with gr.Tab("📖 Books"):
            gr.Markdown("### Library Books")
            
            with gr.Row():
                refresh_books_btn = gr.Button("🔄 Refresh Books", size="sm")
            
            books_table = gr.Dataframe(label="All Books", value=library.get_all_books())
            
            gr.Markdown("---")
            gr.Markdown("**Add New Book:**")
            
            with gr.Row():
                book_title = gr.Textbox(label="Title", placeholder="Enter book title")
                book_author = gr.Textbox(label="Author", placeholder="Enter author name")
                book_year = gr.Number(label="Year", value=2024, minimum=1900, maximum=2030)
            
            add_book_btn = gr.Button("➕ Add Book", variant="primary")
            book_status = gr.Textbox(label="Status", interactive=False)
            
            refresh_books_btn.click(refresh_books, outputs=books_table)
            add_book_btn.click(
                add_new_book,
                inputs=[book_title, book_author, book_year],
                outputs=[book_status, books_table]
            )
        
        # Borrowing Tab
        with gr.Tab("📝 Borrowing"):
            gr.Markdown("### Current Borrows")
            
            with gr.Row():
                refresh_borrows_btn = gr.Button("🔄 Refresh Borrows", size="sm")
            
            borrows_table = gr.Dataframe(label="Current Borrows", value=library.get_current_borrows())
            
            gr.Markdown("---")
            gr.Markdown("**Borrow a Book:**")
            
            with gr.Row():
                member_dropdown = gr.Dropdown(
                    choices=library.get_member_choices(),
                    label="Select Member"
                )
                book_dropdown = gr.Dropdown(
                    choices=library.get_book_choices(),
                    label="Select Book"
                )
                refresh_dropdowns_btn = gr.Button("🔄", size="sm")
            
            borrow_btn = gr.Button("📚 Borrow Book", variant="primary")
            borrow_status = gr.Textbox(label="Borrow Status", interactive=False)
            
            gr.Markdown("---")
            gr.Markdown("**Return a Book:**")
            
            with gr.Row():
                borrow_dropdown = gr.Dropdown(
                    choices=library.get_borrow_choices(),
                    label="Select Borrow Record to Return"
                )
                refresh_return_btn = gr.Button("🔄", size="sm")
            
            return_btn = gr.Button("↩️ Return Book", variant="secondary")
            return_status = gr.Textbox(label="Return Status", interactive=False)
            
            # Event handlers
            refresh_borrows_btn.click(refresh_borrows, outputs=borrows_table)
            
            refresh_dropdowns_btn.click(
                lambda: [gr.Dropdown(choices=library.get_member_choices()), 
                        gr.Dropdown(choices=library.get_book_choices())],
                outputs=[member_dropdown, book_dropdown]
            )
            
            refresh_return_btn.click(
                lambda: gr.Dropdown(choices=library.get_borrow_choices()),
                outputs=borrow_dropdown
            )
            
            borrow_btn.click(
                create_borrow,
                inputs=[member_dropdown, book_dropdown],
                outputs=[borrow_status, borrows_table]
            )
            
            return_btn.click(
                process_return,
                inputs=[borrow_dropdown],
                outputs=[return_status, borrows_table]
            )
        
        # Reports Tab
        with gr.Tab("📊 Reports"):
            gr.Markdown("### Library Reports")
            
            with gr.Row():
                gr.Markdown("""
                **Available Data:**
                - Members: View and manage library members
                - Books: Browse the book collection
                - Current Borrows: See who has borrowed what
                - Add/Return: Manage borrowing transactions
                
                **Database Tables:**
                - `member`: Library members with contact info
                - `book`: Book catalog with title, author, year
                - `borrow`: Borrowing records with dates
                """)
    
    gr.Markdown("---")
    gr.Markdown("💡 **Your Library Database is Ready!** Use the tabs above to manage members, books, and borrowing.")

# Launch the app
if __name__ == "__main__":
    app.launch(
        server_name="0.0.0.0",
        server_port=7860,
        share=False
    )