import gradio as gr
import pandas as pd
from datetime import datetime, date
from supabase import create_client, Client
import plotly.express as px
import plotly.graph_objects as go

# Custom CSS for modern styling
CUSTOM_CSS = """
/* Main theme and layout */
.gradio-container {
    max-width: 1400px !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.1) !important;
}

/* Header styling */
.main-header {
    text-align: center;
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    -webkit-background-clip: text;
    -webkit-text-fill-color: transparent;
    font-size: 3em !important;
    font-weight: bold !important;
    margin-bottom: 20px !important;
    text-shadow: 2px 2px 4px rgba(0,0,0,0.3);
}

/* Card styling */
.card {
    background: rgba(255, 255, 255, 0.95) !important;
    border-radius: 15px !important;
    padding: 20px !important;
    margin: 10px !important;
    box-shadow: 0 8px 32px rgba(0,0,0,0.1) !important;
    border: 1px solid rgba(255, 255, 255, 0.2) !important;
    backdrop-filter: blur(10px) !important;
}

/* Button styling */
.primary-btn {
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%) !important;
    border: none !important;
    border-radius: 25px !important;
    padding: 12px 24px !important;
    color: white !important;
    font-weight: bold !important;
    transition: all 0.3s ease !important;
    box-shadow: 0 4px 15px rgba(0,0,0,0.2) !important;
}

.primary-btn:hover {
    transform: translateY(-2px) !important;
    box-shadow: 0 6px 20px rgba(0,0,0,0.3) !important;
}

/* Status indicators */
.status-success {
    background: linear-gradient(135deg, #4CAF50, #45a049) !important;
    color: white !important;
    border-radius: 10px !important;
    padding: 10px !important;
    text-align: center !important;
}

.status-error {
    background: linear-gradient(135deg, #f44336, #d32f2f) !important;
    color: white !important;
    border-radius: 10px !important;
    padding: 10px !important;
    text-align: center !important;
}

/* Tab styling */
.tab-nav {
    background: rgba(255, 255, 255, 0.1) !important;
    border-radius: 15px !important;
    padding: 5px !important;
}

/* Table styling */
.dataframe {
    border-radius: 10px !important;
    overflow: hidden !important;
    box-shadow: 0 4px 15px rgba(0,0,0,0.1) !important;
}

/* Form styling */
.form-container {
    background: rgba(255, 255, 255, 0.05) !important;
    border-radius: 15px !important;
    padding: 20px !important;
    margin: 15px 0 !important;
    border: 1px solid rgba(255, 255, 255, 0.1) !important;
}

/* Stats cards */
.stats-card {
    background: linear-gradient(135deg, rgba(255,255,255,0.2), rgba(255,255,255,0.1)) !important;
    border-radius: 15px !important;
    padding: 20px !important;
    text-align: center !important;
    margin: 10px !important;
    backdrop-filter: blur(10px) !important;
    border: 1px solid rgba(255,255,255,0.2) !important;
}

.stats-number {
    font-size: 2.5em !important;
    font-weight: bold !important;
    color: #667eea !important;
    margin-bottom: 5px !important;
}

.stats-label {
    color: #666 !important;
    font-size: 1.1em !important;
    text-transform: uppercase !important;
    letter-spacing: 1px !important;
}
"""

class ModernLibraryManagement:
    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_dashboard_stats(self):
        """Get dashboard statistics"""
        try:
            # Get counts
            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 Exception as e:
            return 0, 0, 0
    
    def get_borrowing_chart(self):
        """Create a borrowing trends chart"""
        try:
            # Get borrow data
            borrows = self.supabase.table("borrow").select("*").execute().data
            
            if not borrows:
                # Return empty chart
                fig = go.Figure()
                fig.add_annotation(text="No borrowing data available", 
                                 xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False)
                fig.update_layout(title="Borrowing Activity", height=300)
                return fig
            
            # Process data for chart
            df = pd.DataFrame(borrows)
            df['borrow_date'] = pd.to_datetime(df['borrow_date'])
            df['month'] = df['borrow_date'].dt.strftime('%Y-%m')
            
            # Count borrows by month
            monthly_borrows = df.groupby('month').size().reset_index(name='count')
            
            # Create chart
            fig = px.bar(monthly_borrows, x='month', y='count', 
                        title="📊 Monthly Borrowing Activity",
                        color='count',
                        color_continuous_scale="Viridis")
            
            fig.update_layout(
                height=300,
                showlegend=False,
                plot_bgcolor='rgba(0,0,0,0)',
                paper_bgcolor='rgba(0,0,0,0)',
                font=dict(color='#333')
            )
            
            return fig
            
        except Exception as e:
            fig = go.Figure()
            fig.add_annotation(text=f"Error: {str(e)}", 
                             xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False)
            fig.update_layout(title="Borrowing Activity", height=300)
            return fig
    
    def get_popular_books_chart(self):
        """Create a popular books chart"""
        try:
            # Get borrow data with book info
            borrows = self.supabase.table("borrow").select("book_id").execute().data
            books = self.supabase.table("book").select("*").execute().data
            
            if not borrows or not books:
                fig = go.Figure()
                fig.add_annotation(text="No data available", 
                                 xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False)
                fig.update_layout(title="Popular Books", height=300)
                return fig
            
            # Count borrows by book
            book_counts = {}
            for borrow in borrows:
                book_id = borrow['book_id']
                book_counts[book_id] = book_counts.get(book_id, 0) + 1
            
            # Get book titles
            book_dict = {book['id']: book['title'] for book in books}
            
            # Create data for chart
            titles = []
            counts = []
            for book_id, count in book_counts.items():
                title = book_dict.get(book_id, f"Book {book_id}")
                titles.append(title[:30] + "..." if len(title) > 30 else title)
                counts.append(count)
            
            # Sort by popularity
            sorted_data = sorted(zip(titles, counts), key=lambda x: x[1], reverse=True)[:10]
            if sorted_data:
                titles, counts = zip(*sorted_data)
            
            # Create horizontal bar chart
            fig = go.Figure(data=go.Bar(
                y=list(titles),
                x=list(counts),
                orientation='h',
                marker_color='rgba(102, 126, 234, 0.8)'
            ))
            
            fig.update_layout(
                title="📚 Most Popular Books",
                height=300,
                plot_bgcolor='rgba(0,0,0,0)',
                paper_bgcolor='rgba(0,0,0,0)',
                font=dict(color='#333'),
                margin=dict(l=0, r=0, t=40, b=0)
            )
            
            return fig
            
        except Exception as e:
            fig = go.Figure()
            fig.add_annotation(text=f"Error: {str(e)}", 
                             xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False)
            fig.update_layout(title="Popular Books", height=300)
            return fig
    
    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:
                # Add some styling info
                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:
            # Get active borrows
            borrows = self.supabase.table("borrow").select("*").is_("return_date", "null").execute()
            
            if not borrows.data:
                return pd.DataFrame([{"📌 Status": "No active borrows"}])
            
            # Get members and books
            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 enhanced result
            result_data = []
            for borrow in borrows.data:
                member = member_dict.get(borrow['member_id'], {})
                book = book_dict.get(borrow['book_id'], {})
                
                # Calculate days borrowed
                borrow_date = datetime.strptime(borrow['borrow_date'], '%Y-%m-%d').date()
                days_borrowed = (date.today() - borrow_date).days
                
                result_data.append({
                    '🆔 Borrow ID': borrow['id'],
                    '👤 Member': member.get('name', 'Unknown'),
                    '📧 Email': member.get('email', 'Unknown'),
                    '📖 Book': book.get('title', 'Unknown'),
                    '✍️ Author': book.get('author', 'Unknown'),
                    '📅 Borrowed 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 with enhanced feedback"""
        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 with enhanced feedback"""
        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"
            
            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:
            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')} (ID: {borrow['id']})"
                choices.append((label, borrow['id']))
            
            return choices
        except:
            return [("Error loading borrows", 0)]

# Initialize the library management system
library = ModernLibraryManagement()

# Enhanced interface functions
def refresh_dashboard():
    """Refresh dashboard with stats and charts"""
    members_count, books_count, active_borrows = library.get_dashboard_stats()
    borrowing_chart = library.get_borrowing_chart()
    popular_books_chart = library.get_popular_books_chart()
    
    return (
        gr.HTML(f'<div class="stats-card"><div class="stats-number">{members_count}</div><div class="stats-label">👥 Members</div></div>'),
        gr.HTML(f'<div class="stats-card"><div class="stats-number">{books_count}</div><div class="stats-label">📚 Books</div></div>'),
        gr.HTML(f'<div class="stats-card"><div class="stats-number">{active_borrows}</div><div class="stats-label">📖 Active Loans</div></div>'),
        borrowing_chart,
        popular_books_chart
    )

def refresh_all_data():
    """Refresh all data displays"""
    return (
        library.get_all_members(),
        library.get_all_books(),
        library.get_current_borrows()
    )

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 style="text-align: center; color: #666; margin-bottom: 30px; font-size: 1.2em;">📡 Status: {library.connection_status}</div>')
    
    with gr.Tabs():
        # Dashboard Tab
        with gr.Tab("📊 Dashboard"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 📈 Library Overview")
            
            with gr.Row():
                members_stat = gr.HTML('<div class="stats-card"><div class="stats-number">0</div><div class="stats-label">👥 Members</div></div>')
                books_stat = gr.HTML('<div class="stats-card"><div class="stats-number">0</div><div class="stats-label">📚 Books</div></div>')
                borrows_stat = gr.HTML('<div class="stats-card"><div class="stats-number">0</div><div class="stats-label">📖 Active Loans</div></div>')
            
            refresh_dashboard_btn = gr.Button("🔄 Refresh Dashboard", variant="primary", size="lg")
            
            with gr.Row():
                with gr.Column():
                    borrowing_chart = gr.Plot(label="📊 Borrowing Trends")
                with gr.Column():
                    popular_books_chart = gr.Plot(label="📚 Popular Books")
            
            gr.HTML('</div>')
            
            # Load dashboard on startup
            app.load(refresh_dashboard, outputs=[members_stat, books_stat, borrows_stat, borrowing_chart, popular_books_chart])
            refresh_dashboard_btn.click(refresh_dashboard, outputs=[members_stat, books_stat, borrows_stat, borrowing_chart, popular_books_chart])
        
        # Members Tab
        with gr.Tab("👥 Members Management"):
            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)
            
            gr.HTML('<div class="form-container">')
            gr.Markdown("#### ➕ Add New Member")
            
            with gr.Row():
                member_name = gr.Textbox(label="👤 Name", placeholder="Enter member name", scale=2)
                member_email = gr.Textbox(label="📧 Email", placeholder="Enter email address", scale=2)
                add_member_btn = gr.Button("➕ Add Member", variant="primary", scale=1)
            
            member_status = gr.Textbox(label="📝 Status", interactive=False)
            gr.HTML('</div>')
            gr.HTML('</div>')
            
            # Load members on startup
            app.load(library.get_all_members, outputs=members_table)
            refresh_members_btn.click(library.get_all_members, outputs=members_table)
            add_member_btn.click(
                add_new_member,
                inputs=[member_name, member_email],
                outputs=[member_status, members_table, member_name, member_email]
            )
        
        # Books Tab
        with gr.Tab("📚 Books Catalog"):
            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)
            
            gr.HTML('<div class="form-container">')
            gr.Markdown("#### ➕ Add New Book")
            
            with gr.Row():
                book_title = gr.Textbox(label="📖 Title", placeholder="Enter book title", scale=2)
                book_author = gr.Textbox(label="✍️ Author", placeholder="Enter author name", scale=2)
            
            with gr.Row():
                book_year = gr.Number(label="📅 Year", value=2024, minimum=1900, maximum=2030, scale=2)
                add_book_btn = gr.Button("➕ Add Book", variant="primary", scale=1)
            
            book_status = gr.Textbox(label="📝 Status", interactive=False)
            gr.HTML('</div>')
            gr.HTML('</div>')
            
            # Load books on startup
            app.load(library.get_all_books, outputs=books_table)
            refresh_books_btn.click(library.get_all_books, outputs=books_table)
            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]
            )
        
        # Borrowing Tab
        with gr.Tab("📝 Borrowing Management"):
            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)
            
            gr.HTML('<div class="form-container">')
            gr.Markdown("#### 📚 Borrow a Book")
            
            with gr.Row():
                member_dropdown = gr.Dropdown(
                    choices=library.get_member_choices(),
                    label="👤 Select Member",
                    scale=2
                )
                book_dropdown = gr.Dropdown(
                    choices=library.get_book_choices(),
                    label="📖 Select Book",
                    scale=2
                )
                refresh_dropdowns_btn = gr.Button("🔄", size="sm", scale=1)
            
            borrow_btn = gr.Button("📚 Borrow Book", variant="primary", size="lg")
            borrow_status = gr.Textbox(label="📝 Borrow Status", interactive=False)
            gr.HTML('</div>')
            
            gr.HTML('<div class="form-container">')
            gr.Markdown("#### ↩️ Return a Book")
            
            with gr.Row():
                borrow_dropdown = gr.Dropdown(
                    choices=library.get_borrow_choices(),
                    label="📚 Select Book to Return",
                    scale=2
                )
                refresh_return_btn = gr.Button("🔄", size="sm", scale=1)
            
            return_btn = gr.Button("↩️ Return Book", variant="secondary", size="lg")
            return_status = gr.Textbox(label="📝 Return Status", interactive=False)
            gr.HTML('</div>')
            gr.HTML('</div>')
            
            # Load borrows on startup
            app.load(library.get_current_borrows, outputs=borrows_table)
            refresh_borrows_btn.click(library.get_current_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]
            )
        
        # Analytics Tab
        with gr.Tab("📊 Analytics & Reports"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 📈 Library Analytics")
            
            with gr.Row():
                analytics_refresh_btn = gr.Button("🔄 Refresh Analytics", variant="primary")
            
            with gr.Row():
                analytics_borrowing_chart = gr.Plot(label="📊 Detailed Borrowing Trends")
                analytics_popular_chart = gr.Plot(label="📚 Book Popularity Rankings")
            
            gr.Markdown("### 📋 Quick Stats")
            with gr.Row():
                all_members_df = gr.Dataframe(label="👥 All Members", interactive=False)
                all_books_df = gr.Dataframe(label="📚 All Books", interactive=False)
            
            gr.HTML('</div>')
            
            analytics_refresh_btn.click(
                lambda: [library.get_borrowing_chart(), library.get_popular_books_chart(), 
                        library.get_all_members(), library.get_all_books()],
                outputs=[analytics_borrowing_chart, analytics_popular_chart, all_members_df, all_books_df]
            )
    
    # Footer
    gr.HTML('''
    <div style="text-align: center; margin-top: 40px; padding: 20px; 
                background: rgba(255,255,255,0.1); border-radius: 15px; 
                color: #666;">
        <p><strong>🏛️ Modern Library Management System</strong></p>
        <p>Built with ❤️ using Gradio & Supabase | Real-time Database Integration</p>
    </div>
    ''')

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