import gradio as gr
import pandas as pd
import requests
import json
import os
from datetime import datetime, date

# Custom CSS for modern styling
CUSTOM_CSS = """
.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.15) !important;
}

.welcome-section {
    background: rgba(255, 255, 255, 0.95) !important;
    border-radius: 20px !important;
    padding: 30px !important;
    margin: 20px 0 !important;
    box-shadow: 0 10px 30px rgba(0,0,0,0.15) !important;
    text-align: center !important;
}

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

.stat-card {
    background: linear-gradient(135deg, rgba(255,255,255,0.25), rgba(255,255,255,0.15)) !important;
    border-radius: 15px !important;
    padding: 25px !important;
    text-align: center !important;
    backdrop-filter: blur(10px) !important;
    border: 1px solid rgba(255,255,255,0.3) !important;
    box-shadow: 0 8px 25px rgba(0,0,0,0.1) !important;
}

.stat-number {
    font-size: 2.5em !important;
    font-weight: bold !important;
    color: #FFE082 !important;
    display: block !important;
    margin-bottom: 8px !important;
    text-shadow: 2px 2px 4px rgba(0,0,0,0.3) !important;
}

.stat-label {
    color: white !important;
    font-size: 1.1em !important;
    font-weight: 500 !important;
    text-transform: uppercase !important;
    letter-spacing: 1px !important;
}

.main-header {
    text-align: center;
    color: white !important;
    font-size: 3.2em !important;
    font-weight: bold !important;
    margin-bottom: 15px !important;
    text-shadow: 3px 3px 6px rgba(0,0,0,0.4) !important;
}

.subtitle {
    text-align: center;
    color: rgba(255,255,255,0.9) !important;
    font-size: 1.3em !important;
    margin-bottom: 30px !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;
}

.form-section {
    background: rgba(102, 126, 234, 0.1) !important;
    border-radius: 12px !important;
    padding: 20px !important;
    margin: 15px 0 !important;
    border: 1px solid rgba(102, 126, 234, 0.2) !important;
}

.data-preview {
    background: linear-gradient(135deg, #f8f9fa, #e9ecef) !important;
    border-radius: 15px !important;
    padding: 20px !important;
    margin: 20px 0 !important;
    box-shadow: inset 0 2px 10px rgba(0,0,0,0.05) !important;
}

.connection-status {
    text-align: center;
    padding: 15px;
    margin: 20px 0;
    border-radius: 10px;
    font-weight: bold;
    font-size: 1.1em;
}

.status-connected {
    background: linear-gradient(135deg, #4CAF50, #45a049);
    color: white;
}

.status-error {
    background: linear-gradient(135deg, #f44336, #d32f2f);
    color: white;
}

.footer-info {
    text-align: center;
    margin-top: 40px;
    padding: 25px;
    background: rgba(255,255,255,0.15);
    border-radius: 20px;
    color: white;
    backdrop-filter: blur(10px);
    border: 1px solid rgba(255,255,255,0.2);
}
"""

class SupabaseRestClient:
    def __init__(self):
        """Initialize Supabase REST API client"""
        self.url = "https://bnjblzcqaumctpehgoid.supabase.co"
        
        # Get API key from environment (HF secret) or fallback
        self.api_key = os.getenv('SUPABASE_KEY', 
                                "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImJuamJsemNxYXVtY3RwZWhnb2lkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgyOTA0OTQsImV4cCI6MjA3Mzg2NjQ5NH0.L4wPzuBj9dlSKpYxO1eDX-57KcP0mbNfN8stmTB-STM")
        
        self.headers = {
            "apikey": self.api_key,
            "Authorization": f"Bearer {self.api_key}",
            "Content-Type": "application/json"
        }
        
        # Test connection
        self.connection_status = self.test_connection()
    
    def test_connection(self):
        """Test the Supabase REST API connection"""
        try:
            response = requests.get(
                f"{self.url}/rest/v1/member?select=count",
                headers=self.headers,
                timeout=10
            )
            
            if response.status_code == 200:
                key_source = "🔐 HF Secret" if os.getenv('SUPABASE_KEY') else "🔑 Local Key"
                return f"🟢 Connected to Supabase Database ({key_source})"
            else:
                return f"🔴 Connection failed: HTTP {response.status_code}"
                
        except Exception as e:
            return f"🔴 Connection error: {str(e)}"
    
    def get_table_data(self, table_name, limit=100):
        """Get data from a Supabase table"""
        try:
            response = requests.get(
                f"{self.url}/rest/v1/{table_name}?limit={limit}",
                headers=self.headers,
                timeout=10
            )
            
            if response.status_code == 200:
                return response.json()
            else:
                return []
                
        except Exception as e:
            print(f"Error fetching {table_name}: {e}")
            return []
    
    def insert_data(self, table_name, data):
        """Insert data into a Supabase table"""
        try:
            response = requests.post(
                f"{self.url}/rest/v1/{table_name}",
                headers=self.headers,
                json=data,
                timeout=10
            )
            
            return response.status_code == 201
            
        except Exception as e:
            print(f"Error inserting into {table_name}: {e}")
            return False
    
    def update_data(self, table_name, id_field, id_value, data):
        """Update data in a Supabase table"""
        try:
            response = requests.patch(
                f"{self.url}/rest/v1/{table_name}?{id_field}=eq.{id_value}",
                headers=self.headers,
                json=data,
                timeout=10
            )
            
            return response.status_code == 204
            
        except Exception as e:
            print(f"Error updating {table_name}: {e}")
            return False

class LibraryManagement:
    def __init__(self):
        """Initialize library management with Supabase REST API"""
        self.supabase = SupabaseRestClient()
        self.connection_status = self.supabase.connection_status
    
    def get_dashboard_stats(self):
        """Get dashboard statistics"""
        try:
            members_data = self.supabase.get_table_data("member")
            books_data = self.supabase.get_table_data("book")
            borrows_data = self.supabase.get_table_data("borrow")
            
            # Count active borrows (where return_date is null)
            active_borrows = len([b for b in borrows_data if b.get('return_date') is None])
            
            return len(members_data), len(books_data), active_borrows
        except:
            return 0, 0, 0
    
    def get_all_members(self):
        """Get all library members with enhanced formatting"""
        try:
            data = self.supabase.get_table_data("member")
            if data:
                df = pd.DataFrame(data)
                df.columns = ['🆔 ID', '👤 Name', '📧 Email']
                return df
            else:
                return pd.DataFrame([{"📌 Status": "No members found or connection issue"}])
        except Exception as e:
            return pd.DataFrame([{"❌ Error": str(e)}])
    
    def get_all_books(self):
        """Get all books with enhanced formatting"""
        try:
            data = self.supabase.get_table_data("book")
            if data:
                df = pd.DataFrame(data)
                df.columns = ['🆔 ID', '📖 Title', '✍️ Author', '📅 Year']
                return df
            else:
                return pd.DataFrame([{"📌 Status": "No books found or connection issue"}])
        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_data = self.supabase.get_table_data("borrow")
            active_borrows = [b for b in borrows_data if b.get('return_date') is None]
            
            if not active_borrows:
                return pd.DataFrame([{"📌 Status": "No active borrows"}])
            
            # Get members and books for joining
            members_data = self.supabase.get_table_data("member")
            books_data = self.supabase.get_table_data("book")
            
            # 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 active_borrows:
                member = member_dict.get(borrow['member_id'], {})
                book = book_dict.get(borrow['book_id'], {})
                
                # Calculate days borrowed
                try:
                    borrow_date = datetime.strptime(borrow['borrow_date'], '%Y-%m-%d').date()
                    days_borrowed = (date.today() - borrow_date).days
                except:
                    days_borrowed = 0
                
                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
                })
            
            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}
            success = self.supabase.insert_data("member", data)
            
            if success:
                return f"✅ Successfully added member: {name}"
            else:
                return "❌ Failed to add member"
                
        except Exception as e:
            if "duplicate" in str(e).lower():
                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 < 1900 or year > datetime.now().year + 1:
                return "❌ Invalid year"
            
            data = {"title": title, "author": author, "year": year}
            success = self.supabase.insert_data("book", data)
            
            if success:
                return f"✅ Successfully added book: {title}"
            else:
                return "❌ Failed to add book"
                
        except Exception as e:
            return f"❌ Error: {str(e)}"
    
    def get_member_choices(self):
        """Get member choices for dropdown"""
        try:
            data = self.supabase.get_table_data("member")
            return [(f"👤 {m['name']} (ID: {m['id']})", m['id']) for m in data]
        except:
            return [("No members found", 0)]
    
    def get_book_choices(self):
        """Get book choices for dropdown"""
        try:
            books_data = self.supabase.get_table_data("book")
            borrows_data = self.supabase.get_table_data("borrow")
            
            # Get currently borrowed book IDs
            borrowed_book_ids = set()
            for borrow in borrows_data:
                if borrow.get('return_date') is None:
                    borrowed_book_ids.add(borrow['book_id'])
            
            # Filter available books
            available_books = [b for b in books_data if b['id'] not in borrowed_book_ids]
            
            return [(f"📖 {b['title']} by {b['author']}", b['id']) for b in available_books]
        except:
            return [("No books found", 0)]
    
    def get_borrow_choices(self):
        """Get active borrow choices for dropdown"""
        try:
            borrows_data = self.supabase.get_table_data("borrow")
            members_data = self.supabase.get_table_data("member")
            books_data = self.supabase.get_table_data("book")
            
            # Create lookup dictionaries
            member_dict = {m['id']: m for m in members_data}
            book_dict = {b['id']: b for b in books_data}
            
            active_borrows = [b for b in borrows_data if b.get('return_date') is None]
            
            choices = []
            for borrow in active_borrows:
                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 if choices else [("No active borrows", 0)]
        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 borrow_book_action(member_choice, book_choice):
    """Create a new borrow record"""
    try:
        if not member_choice or not book_choice:
            return "❌ Please select both member and book", library.get_current_borrows()
        
        data = {
            "member_id": member_choice,
            "book_id": book_choice,
            "borrow_date": date.today().isoformat()
        }
        
        success = library.supabase.insert_data("borrow", data)
        
        if success:
            return "✅ Book borrowed successfully! 📚", library.get_current_borrows()
        else:
            return "❌ Failed to create borrow record", library.get_current_borrows()
            
    except Exception as e:
        return f"❌ Error: {str(e)}", library.get_current_borrows()

def return_book_action(borrow_choice):
    """Return a borrowed book"""
    try:
        if not borrow_choice:
            return "❌ Please select a borrow record", library.get_current_borrows()
        
        data = {"return_date": date.today().isoformat()}
        success = library.supabase.update_data("borrow", "id", borrow_choice, data)
        
        if success:
            return "✅ Book returned successfully! 📚➡️", library.get_current_borrows()
        else:
            return "❌ Failed to return book", library.get_current_borrows()
            
    except Exception as e:
        return f"❌ Error: {str(e)}", library.get_current_borrows()

# Create the 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('<div class="subtitle">📚 Your Complete Digital Library Solution</div>')
    
    # Connection status
    status_class = "status-connected" if "🟢" in library.connection_status else "status-error"
    gr.HTML(f'<div class="connection-status {status_class}">📡 {library.connection_status}</div>')
    
    # Welcome section with immediate data display
    with gr.Column():
        gr.HTML('<div class="welcome-section">')
        gr.Markdown("## 🌟 Welcome to Your Library!")
        gr.Markdown("### 📊 Live Dashboard")
        
        with gr.Row():
            members_stat = gr.HTML()
            books_stat = gr.HTML()
            borrows_stat = gr.HTML()
        
        refresh_dashboard_btn = gr.Button("🔄 Refresh Dashboard", variant="primary", size="lg")
        gr.HTML('</div>')
        
        # Immediate data preview
        gr.HTML('<div class="data-preview">')
        gr.Markdown("### 📋 Current Library Data")
        
        with gr.Row():
            with gr.Column():
                gr.Markdown("#### 👥 Library Members")
                members_preview = gr.Dataframe(
                    label="Members",
                    interactive=False,
                    height=250
                )
            
            with gr.Column():
                gr.Markdown("#### 📚 Book Catalog")
                books_preview = gr.Dataframe(
                    label="Books",
                    interactive=False,
                    height=250
                )
        
        with gr.Row():
            gr.Markdown("#### 📖 Active Borrowings")
            borrows_preview = gr.Dataframe(
                label="Current Borrowings",
                interactive=False,
                height=200
            )
        
        gr.HTML('</div>')
    
    with gr.Tabs():
        # Members Management
        with gr.Tab("👥 Members Management"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 👥 Complete Members Management")
            
            refresh_members_btn = gr.Button("🔄 Refresh Members", variant="secondary")
            members_table = gr.Dataframe(label="📋 All Members", interactive=False, wrap=True, height=300)
            
            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", size="lg")
            member_status = gr.Textbox(label="📝 Status", interactive=False)
            gr.HTML('</div>')
            gr.HTML('</div>')
        
        # Books Management
        with gr.Tab("📚 Books Management"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 📚 Complete Books Management")
            
            refresh_books_btn = gr.Button("🔄 Refresh Books", variant="secondary")
            books_table = gr.Dataframe(label="📋 All Books", interactive=False, wrap=True, height=300)
            
            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", size="lg")
            book_status = gr.Textbox(label="📝 Status", interactive=False)
            gr.HTML('</div>')
            gr.HTML('</div>')
        
        # Borrowing Management
        with gr.Tab("📝 Borrowing Management"):
            gr.HTML('<div class="card">')
            gr.Markdown("### 📖 Complete Borrowing Management")
            
            refresh_borrows_btn = gr.Button("🔄 Refresh Borrowings", variant="secondary")
            borrows_table = gr.Dataframe(label="📋 All Borrowings", interactive=False, wrap=True, height=300)
            
            gr.HTML('<div class="form-section">')
            gr.Markdown("#### 📚 Issue New Book")
            
            with gr.Row():
                member_dropdown = gr.Dropdown(label="👤 Select Member", value=None)
                book_dropdown = gr.Dropdown(label="📖 Select Available Book", value=None)
            
            refresh_dropdowns_btn = gr.Button("🔄 Refresh Options", size="sm")
            borrow_btn = gr.Button("📚 Issue Book", variant="primary", size="lg")
            borrow_status = gr.Textbox(label="📝 Issue Status", interactive=False)
            gr.HTML('</div>')
            
            gr.HTML('<div class="form-section">')
            gr.Markdown("#### ↩️ Return Book")
            
            borrow_dropdown = gr.Dropdown(label="📚 Select Book to Return", value=None)
            refresh_return_btn = gr.Button("🔄 Refresh Returns", size="sm")
            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 initial data
    app.load(
        lambda: [
            *refresh_dashboard(),
            library.get_all_members(),
            library.get_all_books(),
            library.get_current_borrows(),
            gr.Dropdown(choices=library.get_member_choices()),
            gr.Dropdown(choices=library.get_book_choices()),
            gr.Dropdown(choices=library.get_borrow_choices())
        ],
        outputs=[
            members_stat, books_stat, borrows_stat,
            members_preview, books_preview, borrows_preview,
            member_dropdown, book_dropdown, borrow_dropdown
        ]
    )
    
    # Event handlers
    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(
        borrow_book_action,
        inputs=[member_dropdown, book_dropdown],
        outputs=[borrow_status, borrows_table]
    )
    
    return_btn.click(
        return_book_action,
        inputs=[borrow_dropdown],
        outputs=[return_status, borrows_table]
    )
    
    # Footer
    gr.HTML('''
    <div class="footer-info">
        <h3>🏛️ Modern Library Management System</h3>
        <p><strong>✨ Features:</strong> Real-time dashboard • Member management • Book catalog • Borrowing system</p>
        <p><strong>🔗 Connection:</strong> Direct Supabase REST API | Production Ready</p>
        <p><strong>🚀 Status:</strong> Deployed on Hugging Face Spaces | Mobile Responsive | Secure</p>
    </div>
    ''')

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