import sqlite3
import pandas as pd
from datetime import datetime
from database import query_db, DB_NAME
# UPDATED: Changed from sentiment2 to sentiment
from sentiment import analyze_sentiment, client 

def get_products_by_category(category):
    results = query_db("SELECT name FROM products WHERE category = ?", (category,), fetch=True)
    return [row[0] for row in results]

def save_review(product_name, rating, review_text):
    """
    Validates the input, runs sentiment analysis, and saves to SQLite.
    """
    # 1. Validation: No empty strings
    if not review_text or not review_text.strip():
        return "⚠️ Error: Review text cannot be empty!"
    
    # 2. Validation: Minimum 3 words (Matches UI logic)
    word_count = len(review_text.strip().split())
    if word_count < 3:
        return "⚠️ Error: Please write at least 3 words."

    # 3. Database lookup for product ID
    result = query_db("SELECT id FROM products WHERE name = ?", (product_name,), fetch=True)
    
    if result:
        product_id = result[0][0]
        
        # 4. Sentiment Analysis (using logic from sentiment.py)
        sentiment = analyze_sentiment(review_text, rating=int(rating))
        
        # 5. Save to Database
        date_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        query_db("""INSERT INTO reviews (product_id, rating, review_text, sentiment, date) 
                    VALUES (?, ?, ?, ?, ?)""",
                 (product_id, int(rating), review_text, sentiment, date_str))
        
        return f"✅ Saved! Sentiment detected: {sentiment}"
    
    return "❌ Error: Product not found."

def generate_business_report():
    """Fetches all reviews and sends them to Groq for a summary report."""
    conn = sqlite3.connect(DB_NAME)
    query = """
    SELECT p.name, r.rating, r.sentiment, r.review_text 
    FROM products p JOIN reviews r ON p.id = r.product_id
    """
    df = pd.read_sql_query(query, conn)
    conn.close()

    if df.empty:
        return "No data found. Collect some reviews first!"

    # Create a string of all reviews for the AI to read
    review_summary = ""
    for _, row in df.iterrows():
        review_summary += f"- {row['name']} ({row['rating']} stars, {row['sentiment']}): {row['review_text']}\n"

    prompt = f"Analyze these clothing reviews and provide a summary of satisfaction, top issues, and an action plan:\n\n{review_summary}"

    try:
        # Uses the client imported from sentiment.py
        completion = client.chat.completions.create(
            messages=[
                {"role": "system", "content": "You are a concise business consultant."},
                {"role": "user", "content": prompt}
            ],
            model="llama-3.1-8b-instant",
            temperature=0.5,
            max_tokens=500
        )
        return completion.choices[0].message.content
    except Exception as e:
        return f"Error: {str(e)}"

def get_all_reviews():
    """Returns a DataFrame for the Owner's table."""
    conn = sqlite3.connect(DB_NAME)
    try:
        query = """
        SELECT p.category, p.name as product_name, r.rating, r.sentiment, r.review_text, r.date 
        FROM products p 
        JOIN reviews r ON p.id = r.product_id
        ORDER BY r.date DESC
        """
        return pd.read_sql_query(query, conn)
    finally:
        conn.close()