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

# Custom CSS for modern styling
CUSTOM_CSS = """
.gradio-container {
    max-width: 1200px !important;
    margin: auto !important;
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%) !important;
    padding: 20px !important;
    border-radius: 20px !important;
    box-shadow: 0 20px 40px rgba(0,0,0,0.15) !important;
}

.card {
    background: rgba(255, 255, 255, 0.95) !important;
    border-radius: 15px !important;
    padding: 25px !important;
    margin: 15px 5px !important;
    box-shadow: 0 8px 32px rgba(0,0,0,0.12) !important;
    border: 1px solid rgba(255, 255, 255, 0.2) !important;
    backdrop-filter: blur(10px) !important;
}

.main-header {
    text-align: center;
    color: white !important;
    font-size: 2.8em !important;
    font-weight: bold !important;
    margin-bottom: 10px !important;
    text-shadow: 2px 2px 4px rgba(0,0,0,0.3) !important;
}

.status-indicator {
    text-align: center;
    color: rgba(255,255,255,0.9) !important;
    font-size: 1.1em !important;
    margin-bottom: 25px !important;
}

.form-section {
    background: rgba(255, 255, 255, 0.08) !important;
    border-radius: 12px !important;
    padding: 20px !important;
    margin: 15px 0 !important;
    border: 1px solid rgba(255, 255, 255, 0.15) !important;
}

.stats-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
    gap: 20px;
    margin: 20px 0;
}

.stat-card {
    background: rgba(255, 255, 255, 0.15) !important;
    border-radius: 12px !important;
    padding: 20px !important;
    text-align: center !important;
    backdrop-filter: blur(10px) !important;
    border: 1px solid rgba(255, 255, 255, 0.2) !important;
}

.stat-number {
    font-size: 2.2em !important;
    font-weight: bold !important;
    color: #FFE082 !important;
    display: block !important;
}

.stat-label {
    color: rgba(255,255,255,0.8) !important;
    font-size: 0.95em !important;
    margin-top: 5px !important;
    text-transform: uppercase !important;
    letter-spacing: 1px !important;
}

.button-primary {
    background: linear-gradient(135deg, #FF6B6B, #FF8E8E) !important;
    border: none !important;
    border-radius: 25px !important;
    color: white !important;
    font-weight: bold !important;
    padding: 12px 24px !important;
    transition: all 0.3s ease !important;
}

.button-secondary {
    background: linear-gradient(135deg, #4ECDC4, #44A08D) !important;
    border: none !important;
    border-radius: 25px !important;
    color: white !important;
    font-weight: bold !important;
    padding: 12px 24px !important;
}

.dataframe-container {
    border-radius: 12px !important;
    overflow: hidden !important;
    box-shadow: 0 4px 20px rgba(0,0,0,0.1) !important;
    background: white !important;
}

.footer-info {
    text-align: center;
    margin-top: 30px;
    padding: 20px;
    background: rgba(255,255,255,0.1);
    border-radius: 15px;
    color: rgba(255,255,255,0.8);
}
"""

class LibraryManagement:
    def __init__(self):
        """Initialize Supabase connection for library management"""
        import os
        
        self.url = "https://bnjblzcqaumctpehgoid.supabase.co"
        
        # Try to get the API key from environment variable (Hugging Face secret)
        # Fall back to the original key for local development
        self.key = os.getenv('Superbase', 
                            "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImJuamJsemNxYXVtY3RwZWhnb2lkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgyOTA0OTQsImV4cCI6MjA3Mzg2NjQ5NH0.L4wPzuBj9dlSKpYxO1eDX-57KcP0mbNfN8stmTB-STM")
        
        try:
            self.supabase: Client = create_client(self.url, self.key)
            key_source = "🔐 Using HF Secret" if os.getenv('Superbase') else "🔑 Using Local Key"
            self.connection_status = f"🟢 Connected to Library Database ({key_source})"
        except Exception as e:
            self.supabase = None
            self.connection_status = f"🔴 Connection failed: {str(e)}"
    
    def get_dashboard_stats(self):
        """Get dashboard statistics"""
        try:
            members_count = len(self.supabase.table("member").select("id").execute().data)
            books_count = len(self.supabase.table("book").select("id").execute().data)
            active_borrows = len(self.supabase.table("borrow").select("id").is_("return_date", "null").execute().data)
            return members_count, books_count, active_borrows
        except:
            return 0, 0, 0
    
    def get_all_members(self):
        """Get all library members with enhanced formatting"""
        try:
            result = self.supabase.table("member").select("*").order("name").execute()
            df = pd.DataFrame(result.data)
            if not df.empty:
                df.index = range(1, len(df) + 1)
                df.columns = ['🆔 ID', '👤 Name', '📧 Email']
            return df
        except Exception as e:
            return pd.DataFrame([{"❌ Error": str(e)}])
    
    def get_all_books(self):
        """Get all books with enhanced formatting"""
        try:
            result = self.supabase.table("book").select("*").order("title").execute()
            df = pd.DataFrame(result.data)
            if not df.empty:
                df.index = range(1, len(df) + 1)
                df.columns = ['🆔 ID', '📖 Title', '✍️ Author', '📅 Year']
            return df
        except Exception as e:
            return pd.DataFrame([{"❌ Error": str(e)}])
    
    def get_current_borrows(self):
        """Get current borrows with enhanced formatting"""
        try:
            borrows = self.supabase.table("borrow").select("*").is_("return_date", "null").execute()
            
            if not borrows.data:
                return pd.DataFrame([{"📌 Status": "No active borrows"}])
            
            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}
            
            result_data = []
            for borrow in borrows.data:
                member = member_dict.get(borrow['member_id'], {})
                book = book_dict.get(borrow['book_id'], {})
                
                borrow_date = datetime.strptime(borrow['borrow_date'], '%Y-%m-%d').date()
                days_borrowed = (date.today() - borrow_date).days
                
                result_data.append({
                    '🆔 ID': borrow['id'],
                    '👤 Member': member.get('name', 'Unknown'),
                    '📧 Email': member.get('email', 'Unknown'),
                    '📖 Book': book.get('title', 'Unknown'),
                    '✍️ Author': book.get('author', 'Unknown'),
                    '📅 Date': borrow['borrow_date'],
                    '⏱️ Days': days_borrowed
                })
            
            df = pd.DataFrame(result_data)
            df.index = range(1, len(df) + 1)
            return df
            
        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"
            
            existing = self.supabase.table("borrow").select("*").eq("book_id", book_id).is_("return_date", "null").execute()
            
            if existing.data:
                return "❌ Book is already borrowed"
            
            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"
            
            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']}", 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:
            borrows = self.supabase.table("borrow").select("*").is_("return_date", "null").execute()
            
            if not borrows.data:
                return [("No active borrows", 0)]
            
            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')}"
                choices.append((label, borrow['id']))
            
            return choices
        except:
            return [("Error loading borrows", 0)]

# Initialize the library management system
library = LibraryManagement()

# Interface functions
def refresh_dashboard():
    """Refresh dashboard stats"""
    members_count, books_count, active_borrows = library.get_dashboard_stats()
    
    members_html = f'<div class="stat-card"><span class="stat-number">{members_count}</span><span class="stat-label">👥 Members</span></div>'
    books_html = f'<div class="stat-card"><span class="stat-number">{books_count}</span><span class="stat-label">📚 Books</span></div>'
    borrows_html = f'<div class="stat-card"><span class="stat-number">{active_borrows}</span><span class="stat-label">📖 Active Loans</span></div>'
    
    return members_html, books_html, borrows_html

def add_new_member(name, email):
    status = library.add_member(name, email)
    members_df = library.get_all_members()
    return status, members_df, "", ""  # Clear form

def add_new_book(title, author, year):
    status = library.add_book(title, author, year)
    books_df = library.get_all_books()
    return status, books_df, "", "", 2024  # Clear form

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)
    borrows_df = library.get_current_borrows()
    return status, borrows_df

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

# Create the modern Gradio interface
with gr.Blocks(title="🏛️ Modern Library Management System", css=CUSTOM_CSS, theme=gr.themes.Soft()) as app:
    
    # Header
    gr.HTML('<h1 class="main-header">🏛️ Modern Library Management System</h1>')
    gr.HTML(f'<div class="status-indicator">📡 Status: {library.connection_status}</div>')
    
    # Dashboard
    gr.HTML('<div class="card">')
    gr.Markdown("### 📊 Dashboard Overview")
    
    with gr.Row():
        members_stat = gr.HTML()
        books_stat = gr.HTML()
        borrows_stat = gr.HTML()
    
    refresh_dashboard_btn = gr.Button("🔄 Refresh Stats", variant="primary")
    gr.HTML('</div>')
    
    with gr.Tabs():
        # Members Tab
        with gr.Tab("👥 Members"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 👥 Library Members")
            
            refresh_members_btn = gr.Button("🔄 Refresh Members", variant="secondary")
            members_table = gr.Dataframe(label="📋 All Members", interactive=False, wrap=True)
            
            gr.HTML('<div class="form-section">')
            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)
            gr.HTML('</div>')
            gr.HTML('</div>')
        
        # Books Tab
        with gr.Tab("📚 Books"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 📚 Library Books")
            
            refresh_books_btn = gr.Button("🔄 Refresh Books", variant="secondary")
            books_table = gr.Dataframe(label="📋 All Books", interactive=False, wrap=True)
            
            gr.HTML('<div class="form-section">')
            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)
            gr.HTML('</div>')
            gr.HTML('</div>')
        
        # Borrowing Tab
        with gr.Tab("📝 Borrowing"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 📖 Current Borrowings")
            
            refresh_borrows_btn = gr.Button("🔄 Refresh Borrowings", variant="secondary")
            borrows_table = gr.Dataframe(label="📋 Active Borrowings", interactive=False, wrap=True)
            
            gr.HTML('<div class="form-section">')
            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("🔄 Refresh Options", size="sm")
            borrow_btn = gr.Button("📚 Borrow Book", variant="primary")
            borrow_status = gr.Textbox(label="📝 Borrow Status", interactive=False)
            gr.HTML('</div>')
            
            gr.HTML('<div class="form-section">')
            gr.Markdown("#### ↩️ Return a Book")
            
            borrow_dropdown = gr.Dropdown(
                choices=library.get_borrow_choices(),
                label="📚 Select Book to Return"
            )
            
            refresh_return_btn = gr.Button("🔄 Refresh Returns", size="sm")
            return_btn = gr.Button("↩️ Return Book", variant="secondary")
            return_status = gr.Textbox(label="📝 Return Status", interactive=False)
            gr.HTML('</div>')
            gr.HTML('</div>')
    
    # Event handlers
    app.load(refresh_dashboard, outputs=[members_stat, books_stat, borrows_stat])
    app.load(library.get_all_members, outputs=members_table)
    app.load(library.get_all_books, outputs=books_table)
    app.load(library.get_current_borrows, outputs=borrows_table)
    
    refresh_dashboard_btn.click(refresh_dashboard, outputs=[members_stat, books_stat, borrows_stat])
    refresh_members_btn.click(library.get_all_members, outputs=members_table)
    refresh_books_btn.click(library.get_all_books, outputs=books_table)
    refresh_borrows_btn.click(library.get_current_borrows, outputs=borrows_table)
    
    add_member_btn.click(
        add_new_member,
        inputs=[member_name, member_email],
        outputs=[member_status, members_table, member_name, member_email]
    )
    
    add_book_btn.click(
        add_new_book,
        inputs=[book_title, book_author, book_year],
        outputs=[book_status, books_table, book_title, book_author, book_year]
    )
    
    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]
    )
    
    # Footer
    gr.HTML('''
    <div class="footer-info">
        <p><strong>🏛️ Modern Library Management System</strong></p>
        <p>Built with ❤️ using Gradio & Supabase | Real-time Database Integration</p>
        <p>🚀 Ready for production use with beautiful UI and full functionality</p>
    </div>
    ''')

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