#!/usr/bin/env python3
"""
Setup library database schema in Supabase
Creates member, book, and borrow tables with sample data
"""

from supabase import create_client, Client
import pandas as pd

def setup_library_database():
    """Create the library database schema in Supabase"""
    
    url = "https://bnjblzcqaumctpehgoid.supabase.co"
    key = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImJuamJsemNxYXVtY3RwZWhnb2lkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgyOTA0OTQsImV4cCI6MjA3Mzg2NjQ5NH0.L4wPzuBj9dlSKpYxO1eDX-57KcP0mbNfN8stmTB-STM"
    
    print("🚀 Setting up Library Database in Supabase")
    print("=" * 50)
    
    try:
        # Create Supabase client
        supabase: Client = create_client(url, key)
        print("✅ Connected to Supabase!")
        
        # Note: Direct SQL execution requires the service role key
        # With anon key, we'll insert data into tables (assuming they exist)
        
        print("\n📝 Creating sample data using Supabase API...")
        
        # Insert members
        print("👥 Adding members...")
        try:
            member_data = [
                {"name": "Aisha Khan", "email": "aisha@example.org"},
                {"name": "Omar Ali", "email": "omar@example.org"}
            ]
            
            # Try to insert members
            result = supabase.table("member").insert(member_data).execute()
            print(f"✅ Added {len(result.data)} members")
            
        except Exception as e:
            print(f"⚠️ Members insertion note: {str(e)}")
            print("   (This is expected if tables don't exist yet)")
        
        # Insert books
        print("📚 Adding books...")
        try:
            book_data = [
                {"title": "Clean Data", "author": "J. Doe", "year": 2019},
                {"title": "Relational Basics", "author": "S. Smith", "year": 2021}
            ]
            
            result = supabase.table("book").insert(book_data).execute()
            print(f"✅ Added {len(result.data)} books")
            
        except Exception as e:
            print(f"⚠️ Books insertion note: {str(e)}")
        
        # Insert borrow records
        print("📖 Adding borrow records...")
        try:
            borrow_data = [
                {"member_id": 1, "book_id": 2, "borrow_date": "2025-09-01"},
                {"member_id": 2, "book_id": 1, "borrow_date": "2025-09-02"}
            ]
            
            result = supabase.table("borrow").insert(borrow_data).execute()
            print(f"✅ Added {len(result.data)} borrow records")
            
        except Exception as e:
            print(f"⚠️ Borrow records insertion note: {str(e)}")
        
        print("\n🔍 Checking what tables are accessible...")
        
        # Check which tables we can access
        tables_to_check = ["member", "book", "borrow"]
        accessible_tables = []
        
        for table_name in tables_to_check:
            try:
                result = supabase.table(table_name).select("*").limit(1).execute()
                accessible_tables.append(table_name)
                print(f"✅ Table '{table_name}' is accessible")
                
                # Show sample data
                if result.data:
                    print(f"   Sample row: {result.data[0]}")
                    
            except Exception as e:
                if "PGRST205" in str(e):
                    print(f"❌ Table '{table_name}' not found - needs to be created in Supabase SQL editor")
                else:
                    print(f"⚠️ Table '{table_name}' access issue: {str(e)}")
        
        if accessible_tables:
            print(f"\n🎉 Found {len(accessible_tables)} accessible tables!")
            
            # Show some sample queries
            print("\n📊 Sample queries:")
            
            if "member" in accessible_tables:
                try:
                    members = supabase.table("member").select("*").execute()
                    print(f"Total members: {len(members.data)}")
                    if members.data:
                        df = pd.DataFrame(members.data)
                        print("Members:")
                        print(df.to_string(index=False))
                except Exception as e:
                    print(f"Error querying members: {e}")
            
            if "book" in accessible_tables:
                try:
                    books = supabase.table("book").select("*").execute()
                    print(f"\nTotal books: {len(books.data)}")
                    if books.data:
                        df = pd.DataFrame(books.data)
                        print("Books:")
                        print(df.to_string(index=False))
                except Exception as e:
                    print(f"Error querying books: {e}")
                    
        else:
            print("\n📋 To create the tables, run this SQL in your Supabase SQL editor:")
            print("""
-- Create tables
CREATE TABLE member (
  id         BIGSERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(120) UNIQUE NOT NULL
);

CREATE TABLE book (
  id     BIGSERIAL PRIMARY KEY,
  title  VARCHAR(200) NOT NULL,
  author VARCHAR(120) NOT NULL,
  year   INT CHECK (year >= 0)
);

CREATE TABLE borrow (
  id           BIGSERIAL PRIMARY KEY,
  member_id    BIGINT NOT NULL REFERENCES member(id) ON DELETE CASCADE,
  book_id      BIGINT NOT NULL REFERENCES book(id)   ON DELETE CASCADE,
  borrow_date  DATE   NOT NULL DEFAULT CURRENT_DATE,
  return_date  DATE,
  CONSTRAINT borrow_unique_open UNIQUE (member_id, book_id, borrow_date)
);

-- Indexes
CREATE INDEX idx_borrow_member ON borrow(member_id);
CREATE INDEX idx_borrow_book   ON borrow(book_id);
CREATE INDEX idx_book_title    ON book(title);

-- Insert sample data
INSERT INTO member (name, email) VALUES
  ('Aisha Khan','aisha@example.org'),
  ('Omar Ali','omar@example.org');

INSERT INTO book (title, author, year) VALUES
  ('Clean Data','J. Doe',2019),
  ('Relational Basics','S. Smith',2021);

INSERT INTO borrow (member_id, book_id, borrow_date) VALUES
  (1, 2, '2025-09-01'),
  (2, 1, '2025-09-02');
""")
        
        return accessible_tables
        
    except Exception as e:
        print(f"❌ Error setting up database: {str(e)}")
        return []

if __name__ == "__main__":
    tables = setup_library_database()
    
    print(f"\n🚀 Ready to launch Gradio interface!")
    print("Run: source venv/bin/activate && python app_supabase.py")