# jokes_v4.py
import sqlite3
import random
from datetime import datetime

def get_user_sentiment_for_joke(db, joke_id):
    """Get the average user sentiment for a specific joke"""
    cursor = db.execute('''
        SELECT 
            CASE 
                WHEN AVG(CASE WHEN user_sentiment = 'up' THEN 1 
                             WHEN user_sentiment = 'down' THEN -1 
                             ELSE 0 END) > 0.1 THEN '👍 Up'
                WHEN AVG(CASE WHEN user_sentiment = 'up' THEN 1 
                             WHEN user_sentiment = 'down' THEN -1 
                             ELSE 0 END) < -0.1 THEN '👎 Down'
                ELSE '😐 Neutral'
            END as avg_sentiment,
            COUNT(*) as total_votes
        FROM user_sentiments 
        WHERE joke_id = ?
    ''', (joke_id,))
    
    result = cursor.fetchone()
    avg_sentiment, total_votes = result if result else ('😐 Neutral', 0)
    return avg_sentiment, total_votes

def add_user_sentiment(db, joke_id, user_choice):
    """Add user sentiment for a specific joke"""
    try:
        db.execute('''
            INSERT INTO user_sentiments (joke_id, user_sentiment) 
            VALUES (?, ?)
        ''', (joke_id, user_choice))
        db.commit()
        return True
    except Exception as e:
        print(f"❌ Error saving sentiment: {e}")
        return False

def main():
    db = sqlite3.connect('jokes.db')
    
    while True:
        print("\n" + "="*30)
        print("🤖 JOKE BOT 🤖")
        print("="*30)
        print("1. Get random joke")
        print("2. Add new joke")
        print("3. Quit")
        
        choice = input("\nYour choice: ").strip()
        
        if choice == "1":
            # Get random approved joke
            cursor = db.execute('''
                SELECT id, joke, contributor 
                FROM jokes 
                WHERE approved = 1
                ORDER BY RANDOM() 
                LIMIT 1
            ''')
            joke_data = cursor.fetchone()
            
            if joke_data:
                joke_id, joke, contributor = joke_data
                print(f"\n🤣 {joke}")
                print(f"   👤 Contributor: {contributor}")
                
                # Get and display user sentiment stats
                avg_sentiment, total_votes = get_user_sentiment_for_joke(db, joke_id)
                if total_votes > 0:
                    print(f"   👥 Community Rating: {avg_sentiment} ({total_votes} votes)")
                
                # Ask user for their sentiment
                print(f"\n🎯 Rate this joke: 👍 (U)p, 👎 (D)own, or (N)eutral?")
                user_input = input("Your choice (u/d/n): ").strip().lower()
                user_choice = 'up' if user_input in ['u', 'up'] else 'down' if user_input in ['d', 'down'] else 'neutral'
                
                if add_user_sentiment(db, joke_id, user_choice):
                    print(f"✅ Your rating ({'👍 Up' if user_choice == 'up' else '👎 Down' if user_choice == 'down' else '😐 Neutral'}) recorded!")
                else:
                    print("❌ Could not save your rating.")
            else:
                print("篓 No approved jokes in the database yet!")
        
        elif choice == "2":
            new_joke = input("Enter your joke: ").strip()
            if not new_joke:
                print("❌ Joke cannot be empty!")
                continue
                
            name = input("Your name (or press Enter for 'Anonymous'): ").strip() or "Anonymous"
            
            # Get current date/time
            current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            
            try:
                cursor = db.cursor()
                cursor.execute('''
                    INSERT INTO jokes (joke, contributor, created_date, approved) 
                    VALUES (?, ?, ?, 0)
                ''', (new_joke, name, current_time))
                
                # Get the ID of the newly inserted joke
                new_joke_id = cursor.lastrowid
                
                db.commit()
                print("✅ Submitted for approval!")
            except Exception as e:
                print(f"❌ Error saving joke: {e}")
        
        elif choice == "3":
            print("\n👋 Goodbye!")
            break
        
        else:
            print("❌ Invalid choice. Please select 1-3.")

    db.close()

if __name__ == "__main__":
    main()