import sys
import logging
import os
import subprocess
from flask import Flask, render_template, request, redirect, url_for, session, flash, send_from_directory, jsonify, Response
import httpx
from bs4 import BeautifulSoup
import sqlite3
import re
import json
import uuid
from dotenv import load_dotenv
import tempfile
import time
from datetime import datetime
from youtube_transcript_api import YouTubeTranscriptApi
from youtube_transcript_api.proxies import GenericProxyConfig
import click
from celery_app import celery_app
from keepa_deals.db_utils import (
    DB_PATH,
    create_user_restrictions_table_if_not_exists,
    create_user_credentials_table_if_not_exists,
    create_deals_table_if_not_exists,
    save_user_credentials,
    get_all_user_credentials,
    get_system_state,
    set_system_state,
    create_system_state_table_if_not_exists
)
from keepa_deals.janitor import _clean_stale_deals_logic
from keepa_deals.ava_advisor import generate_ava_advice, generate_tooltip_advice, get_mentor_config, load_strategies, load_intelligence, query_xai_api
from keepa_deals.maintenance_tasks import homogenize_intelligence_task
from keepa_deals.inventory_import import fetch_existing_inventory_task, process_bulk_cost_upload, export_missing_costs_csv
from keepa_deals.sp_api_tasks import fetch_amazon_orders_task
import redis
# from keepa_deals.recalculator import recalculate_deals # This causes a hang
# from keepa_deals.Keepa_Deals import run_keepa_script

log_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'app.log')
logging.basicConfig(filename=log_file_path, level=logging.DEBUG, format='%(asctime)s %(levelname)s %(name)s %(threadName)s : %(message)s')
logging.getLogger('app').info(f"Starting wsgi_handler.py from /var/www/agentarbitrage/wsgi_handler.py")
logging.getLogger('app').info(f"Python version: {sys.version}")
logging.getLogger('app').info(f"Python path: {sys.path}")

load_dotenv(os.path.join(os.path.dirname(os.path.abspath(__file__)), '.env'))
logging.getLogger('app').info(f"Loaded wsgi_handler.py from /var/www/agentarbitrage/wsgi_handler.py at {os.getpid()}")

app = Flask(__name__)
app.secret_key = 'supersecretkey'

# Use DB_PATH from db_utils for consistency
# DATABASE_URL = os.getenv("DATABASE_URL", os.path.join(os.path.dirname(os.path.abspath(__file__)), 'deals.db'))

STRATEGIES_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'strategies.json')
INTELLIGENCE_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'intelligence.json')
SETTINGS_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'settings.json')

# --- xAI API Configuration ---
XAI_API_KEY = os.getenv("XAI_TOKEN")
XAI_API_URL = "https://api.x.ai/v1/chat/completions"

# --- Keepa API Configuration ---
KEEPA_API_KEY = os.getenv("KEEPA_API_KEY")

# --- Amazon SP-API Configuration ---
SP_API_CLIENT_ID = os.getenv("SP_API_CLIENT_ID")
SP_API_CLIENT_SECRET = os.getenv("SP_API_CLIENT_SECRET")
SP_API_APP_ID = os.getenv("SP_API_APP_ID") # Often the same as Client ID
AMAZON_AUTH_URL = "https://sellercentral.amazon.com/apps/authorize/consent"
AMAZON_TOKEN_URL = "https://api.amazon.com/auth/o2/token"


app.logger.info(f"Loaded XAI_TOKEN: {'*' * len(XAI_API_KEY) if XAI_API_KEY else 'Not found'}")
app.logger.info(f"Loaded KEEPA_API_KEY: {'*' * len(KEEPA_API_KEY) if KEEPA_API_KEY else 'Not found'}")
app.logger.info(f"Loaded SP_API_CLIENT_ID: {'*' * len(SP_API_CLIENT_ID) if SP_API_CLIENT_ID else 'Not found'}")
app.logger.info(f"Loaded SP_API_AWS_REGION: {os.getenv('SP_API_AWS_REGION', 'us-east-1')}")

def extract_strategies(full_text):
    prompt = f"""
    From the following text, extract key strategies, parameters, and "tricks" for online book arbitrage.
    Convert them into a structured JSON format.

    **Output Format:**
    Return ONLY a JSON array of objects. Do not include markdown formatting.
    Each object must follow this schema:
    {{
      "id": "generate a unique string ID",
      "category": "One of: Buying, Pricing, Risk, Seasonality, General",
      "trigger": "A short condition description (e.g., 'Sales Rank > 100,000')",
      "advice": "The actionable advice",
      "confidence": "High",
      "source": "The original text snippet"
    }}

    **Instructions:**
    1.  Focus on specific numbers, ranges, and conditions.
    2.  Capture inferential strategies.
    3.  If no strategies are found, return an empty array [].

    **Text to Analyze:**
    {full_text}
    """

    # Attempt 1: Primary Model (xAI)
    xai_payload = {
        "messages": [
            {
                "role": "system",
                "content": "You are an expert in online book arbitrage. Your task is to extract key strategies and parameters from the provided text and present them as a list of clear, actionable rules."
            },
            {
                "role": "user",
                "content": prompt
            }
        ],
        "model": "grok-4-fast-reasoning",
        "stream": False,
        "temperature": 0.2
    }
    
    primary_data = query_xai_api(xai_payload)

    if primary_data and 'choices' in primary_data and primary_data['choices']:
        content = primary_data['choices'][0].get('message', {}).get('content')
        if content:
            # Strip markdown formatting if present
            content = re.sub(r'^```json\s*|\s*```$', '', content.strip(), flags=re.MULTILINE)
            app.logger.info("Successfully extracted strategies using xAI API.")
            return content
    
    # If xAI API fails, report the error directly.
    error_message = f"Strategy extraction failed. The primary model (xAI) returned an error: {primary_data.get('error', 'Unknown Error')}"
    app.logger.error(error_message)
    return "Could not extract strategies. Please check the logs for details."


def extract_conceptual_ideas(full_text):
    prompt = f"""
    From the following text about online book arbitrage, extract high-level conceptual ideas, mental models, and overarching methodologies.
    Do not focus on specific, quantitative rules (e.g., "sales rank > 10,000"). Instead, focus on the "why" behind the actions.
    Present them as a list of insightful concepts.

    **Instructions:**
    1.  Identify the core principles or philosophies for sourcing, pricing, and selling.
    2.  Look for explanations of market dynamics (e.g., "why prices spike when Amazon goes out of stock").
    3.  Extract ideas about risk management, inventory strategy, and long-term thinking.
    4.  Only use the information from the text provided. Do not add any external knowledge.
    5.  If the text contains no conceptual ideas, respond with the single phrase: "No conceptual ideas found in the provided text."

    **Example of a Conceptual Idea to capture:**
    *   "The core arbitrage model is to capitalize on pricing inefficiencies between different fulfillment methods (FBM vs. FBA), buying from merchant-fulfilled sellers and reselling through Amazon's FBA network to command a higher price due to the Prime badge."
    *   "A long-term inventory strategy involves balancing fast-selling, low-ROI books with slow-selling, high-ROI 'long-tail' books to ensure consistent cash flow while building long-term value."

    **Text to Analyze:**
    {full_text}
    """

    xai_payload = {
        "messages": [
            {
                "role": "system",
                "content": "You are a strategic analyst. Your task is to extract high-level concepts, mental models, and methodologies from the provided text."
            },
            {
                "role": "user",
                "content": prompt
            }
        ],
        "model": "grok-4-fast-reasoning",
        "stream": False,
        "temperature": 0.3
    }
    
    response_data = query_xai_api(xai_payload)

    if response_data and 'choices' in response_data and response_data['choices']:
        content = response_data['choices'][0].get('message', {}).get('content')
        if content:
            # Strip markdown formatting if present
            content = re.sub(r'^```json\s*|\s*```$', '', content.strip(), flags=re.MULTILINE)
            app.logger.info("Successfully extracted conceptual ideas using xAI API.")
            return content
    
    error_message = f"Conceptual idea extraction failed. The model returned an error: {response_data.get('error', 'Unknown Error')}"
    app.logger.error(error_message)
    return "Could not extract conceptual ideas. Please check the logs for details."

# Credentials from README.md
USERS = {
    'tester': {
        'password': 'OnceUponaBurgerTree-12monkeys',
        'role': 'admin'
    },
    'AristotleLogic': {
        'password': 'virtueLiesInGoldenMean',
        'role': 'user'
    }
}

def ensure_sp_api_session():
    """
    Checks if the session has SP-API credentials. If not, attempts to re-hydrate
    them from the persistent database (user_credentials).
    """
    if not session.get('sp_api_connected'):
        try:
            creds = get_all_user_credentials()
            if creds:
                # We have at least one connected user in the persistent DB.
                # Since this is a single-tenant app, we'll use the first one.
                user_record = creds[0]
                session['sp_api_connected'] = True
                session['sp_api_user_id'] = user_record['user_id']
                # We don't necessarily need to put the refresh token in session if background tasks handle it,
                # but for consistency with the connect flow, we can.
                session['sp_api_refresh_token'] = user_record['refresh_token']
                app.logger.info(f"Re-hydrated SP-API session for user: {user_record['user_id']}")
        except Exception as e:
            app.logger.error(f"Error checking DB for credentials during session hydration: {e}")

@app.route('/')
def index():
    if session.get('logged_in'):
        return redirect(url_for('dashboard'))
    return render_template('index.html')

@app.route('/login', methods=['POST'])
def login():
    username = request.form.get('username')
    password = request.form.get('password')

    if username in USERS and USERS[username]['password'] == password:
        session.clear()  # Clear all session data
        session['logged_in'] = True
        session['username'] = username
        session['role'] = USERS[username]['role']

        ensure_sp_api_session()

        return redirect(url_for('dashboard'))
    else:
        return 'Invalid credentials', 401

@app.route('/logout')
def logout():
    session.clear()
    flash('You have been successfully logged out.', 'success')
    return redirect(url_for('index'))

@app.route('/guided_learning')
def guided_learning():
    if not session.get('logged_in'):
        return redirect(url_for('index'))

    if session.get('role') != 'admin':
        flash("You are not authorized to view this page.", "error")
        return redirect(url_for('dashboard'))

    return render_template('guided_learning.html')

@app.route('/strategies')
def strategies():
    if not session.get('logged_in'):
        return redirect(url_for('index'))
    
    if session.get('role') != 'admin':
        flash("You are not authorized to view this page.", "error")
        return redirect(url_for('dashboard'))

    strategies_list = []
    total_count = 0
    new_today_count = 0
    today_str = datetime.now().strftime('%Y-%m-%d')

    app.logger.info(f"Attempting to read strategies from: {STRATEGIES_FILE}")
    if os.path.exists(STRATEGIES_FILE):
        app.logger.info(f"Strategies file found.")
        try:
            with open(STRATEGIES_FILE, 'r', encoding='utf-8') as f:
                strategies_list = json.load(f)

            total_count = len(strategies_list)
            for s in strategies_list:
                if isinstance(s, dict) and s.get('date_added') == today_str:
                    new_today_count += 1
                # If s is string (legacy), it has no date, so not new today unless converted.

            app.logger.info(f"Successfully loaded {len(strategies_list)} strategies.")
        except (IOError, json.JSONDecodeError) as e:
            app.logger.error(f"Error reading strategies file: {e}", exc_info=True)
            flash("Error reading the strategies file.", "error")
    else:
        app.logger.warning(f"Strategies file not found at: {STRATEGIES_FILE}")

    return render_template('strategies.html', strategies=strategies_list, total_count=total_count, new_today_count=new_today_count)


@app.route('/intelligence')
def intelligence():
    if not session.get('logged_in'):
        return redirect(url_for('index'))

    if session.get('role') != 'admin':
        flash("You are not authorized to view this page.", "error")
        return redirect(url_for('dashboard'))
    
    ideas_list = []
    total_count = 0
    new_today_count = 0
    today_str = datetime.now().strftime('%Y-%m-%d')

    app.logger.info(f"Attempting to read Intelligence from: {INTELLIGENCE_FILE}")
    if os.path.exists(INTELLIGENCE_FILE):
        app.logger.info(f"Intelligence file found.")
        try:
            with open(INTELLIGENCE_FILE, 'r', encoding='utf-8') as f:
                ideas_list = json.load(f)

            total_count = len(ideas_list)
            for i in ideas_list:
                # Intelligence items are now objects with date_added
                if isinstance(i, dict) and i.get('date_added') == today_str:
                    new_today_count += 1
                # Fallback for unexpected strings if migration didn't catch something
                elif not isinstance(i, dict):
                    pass

            app.logger.info(f"Successfully loaded {len(ideas_list)} ideas from Intelligence.")
        except (IOError, json.JSONDecodeError) as e:
            app.logger.error(f"Error reading Intelligence file: {e}", exc_info=True)
            flash("Error reading the Intelligence file.", "error")
    else:
        app.logger.warning(f"Intelligence file not found at: {INTELLIGENCE_FILE}")

    return render_template('intelligence.html', ideas=ideas_list, total_count=total_count, new_today_count=new_today_count)

@app.route('/dashboard')
def dashboard():
    if not session.get('logged_in'):
        return redirect(url_for('index'))
    ensure_sp_api_session()
    return render_template('dashboard.html')

@app.route('/tracking')
def tracking():
    if not session.get('logged_in'):
        return redirect(url_for('index'))
    return render_template('tracking.html')

@app.route('/api/inventory', methods=['GET'])
def get_inventory():
    """Legacy endpoint: Redirects or returns simplified structure if needed."""
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    # Still returning potential buys as a fallback for now, but clients should use new endpoints
    try:
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM inventory_ledger WHERE status = 'POTENTIAL' ORDER BY created_at DESC")
            potential = [dict(row) for row in cursor.fetchall()]
            return jsonify({'potential': potential, 'active': [], 'sales': []}) # Deprecated active/sales here
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/tracking/potential', methods=['GET'])
def get_potential_inventory():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    try:
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM inventory_ledger WHERE status = 'POTENTIAL' ORDER BY created_at DESC")
            data = [dict(row) for row in cursor.fetchall()]
            return jsonify({'data': data})
    except Exception as e:
        app.logger.error(f"Error fetching potential inventory: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/tracking/active', methods=['GET'])
def get_active_inventory():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    page = request.args.get('page', 1, type=int)
    limit = request.args.get('limit', 50, type=int)
    offset = (page - 1) * limit

    try:
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()

            # Count Total
            cursor.execute("SELECT COUNT(*) FROM inventory_ledger WHERE status = 'PURCHASED' AND quantity_remaining > 0")
            total = cursor.fetchone()[0]

            # Fetch Page
            cursor.execute("""
                SELECT * FROM inventory_ledger
                WHERE status = 'PURCHASED' AND quantity_remaining > 0
                ORDER BY purchase_date DESC
                LIMIT ? OFFSET ?
            """, (limit, offset))
            data = [dict(row) for row in cursor.fetchall()]

            return jsonify({
                'data': data,
                'pagination': {
                    'total': total,
                    'page': page,
                    'limit': limit,
                    'pages': (total + limit - 1) // limit
                }
            })
    except Exception as e:
        app.logger.error(f"Error fetching active inventory: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/tracking/sales', methods=['GET'])
def get_sales_history():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    page = request.args.get('page', 1, type=int)
    limit = request.args.get('limit', 50, type=int)
    offset = (page - 1) * limit

    try:
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()

            # Count Total
            cursor.execute("SELECT COUNT(*) FROM sales_ledger")
            total = cursor.fetchone()[0]

            # Fetch Page
            # We explicitly select columns to ensure 'sale_price' is clearly available
            cursor.execute("""
                SELECT
                    amazon_order_id, order_item_id, asin, sku, sale_date,
                    sale_price, amazon_fees, quantity_sold, order_status,
                    reconciliation_status
                FROM sales_ledger
                ORDER BY sale_date DESC
                LIMIT ? OFFSET ?
            """, (limit, offset))
            data = [dict(row) for row in cursor.fetchall()]

            return jsonify({
                'data': data,
                'pagination': {
                    'total': total,
                    'page': page,
                    'limit': limit,
                    'pages': (total + limit - 1) // limit
                }
            })
    except Exception as e:
        app.logger.error(f"Error fetching sales history: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/inventory/potential', methods=['POST'])
def add_potential_buy():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    try:
        data = request.json
        asin = data.get('asin')
        title = data.get('title')
        price = data.get('price')

        if not asin:
            return jsonify({'error': 'ASIN required'}), 400

        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO inventory_ledger (asin, title, buy_cost, status, source)
                VALUES (?, ?, ?, 'POTENTIAL', 'Dashboard')
            """, (asin, title, price))
            conn.commit()

        return jsonify({'status': 'success', 'message': 'Added to potential buys'})
    except Exception as e:
        app.logger.error(f"Error adding potential buy: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/inventory/confirm', methods=['POST'])
def confirm_purchase():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    try:
        data = request.json
        ledger_id = data.get('id')
        buy_cost = data.get('buy_cost')
        qty = data.get('quantity')
        sku = data.get('sku')
        purchase_date = data.get('purchase_date')

        if not ledger_id or not buy_cost or not qty or not sku:
            return jsonify({'error': 'Missing required fields'}), 400

        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE inventory_ledger
                SET status = 'PURCHASED', buy_cost = ?, quantity_purchased = ?, quantity_remaining = ?, sku = ?, purchase_date = ?
                WHERE id = ?
            """, (buy_cost, qty, qty, sku, purchase_date, ledger_id))
            conn.commit()

        return jsonify({'status': 'success'})
    except Exception as e:
        app.logger.error(f"Error confirming purchase: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/inventory/dismiss', methods=['POST'])
def dismiss_potential():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    try:
        data = request.json
        ledger_id = data.get('id')

        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE inventory_ledger SET status = 'DISMISSED' WHERE id = ?", (ledger_id,))
            conn.commit()

        return jsonify({'status': 'success'})
    except Exception as e:
        app.logger.error(f"Error dismissing potential buy: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/inventory/import', methods=['POST'])
def trigger_inventory_import():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    try:
        # Trigger Inventory Task
        task_inv = fetch_existing_inventory_task.delay()
        # Trigger Orders Task (Chain or Parallel)
        task_orders = fetch_amazon_orders_task.delay()

        return jsonify({'status': 'success', 'task_id_inv': task_inv.id, 'task_id_orders': task_orders.id})
    except Exception as e:
        app.logger.error(f"Error triggering inventory import: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/inventory/update_item', methods=['POST'])
def update_inventory_item():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    try:
        data = request.json
        ledger_id = data.get('id')
        buy_cost = data.get('buy_cost')
        qty = data.get('quantity')
        purchase_date = data.get('purchase_date')

        if not ledger_id:
            return jsonify({'error': 'Missing ID'}), 400

        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            # Construct dynamic update query based on provided fields
            updates = []
            params = []

            if buy_cost is not None and buy_cost != '':
                updates.append("buy_cost = ?")
                params.append(buy_cost)

            if qty is not None and qty != '':
                updates.append("quantity_remaining = ?")
                updates.append("quantity_purchased = ?") # Assuming edit updates both for simplicity unless we track separately strictly
                params.append(qty)
                params.append(qty)

            if purchase_date:
                updates.append("purchase_date = ?")
                params.append(purchase_date)

            if not updates:
                return jsonify({'status': 'no_change'})

            params.append(ledger_id)
            sql = f"UPDATE inventory_ledger SET {', '.join(updates)} WHERE id = ?"

            cursor.execute(sql, params)
            conn.commit()

        return jsonify({'status': 'success'})
    except Exception as e:
        app.logger.error(f"Error updating item: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/inventory/upload-costs', methods=['POST'])
def upload_costs():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    if 'file' not in request.files:
        return jsonify({'error': 'No file part'}), 400

    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': 'No selected file'}), 400

    try:
        content = file.read()
        updated_count = process_bulk_cost_upload(content)
        return jsonify({'status': 'success', 'updated_count': updated_count})
    except Exception as e:
        app.logger.error(f"Error uploading costs: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/inventory/export-missing-costs', methods=['GET'])
def export_missing_costs():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401

    try:
        csv_content = export_missing_costs_csv()

        return Response(
            csv_content,
            mimetype="text/csv",
            headers={"Content-disposition": "attachment; filename=missing_costs_template.csv"}
        )
    except Exception as e:
        app.logger.error(f"Error exporting missing costs: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500


@app.route('/learn', methods=['POST'])
def learn():
    if not session.get('logged_in'):
        return redirect(url_for('index'))
    
    # Clean up old temp files
    for key in ['scraped_text_file', 'extracted_strategies_file', 'extracted_ideas_file']:
        if key in session:
            try:
                os.remove(session[key])
            except OSError:
                pass
            session.pop(key, None)

    session.pop('original_input', None)
    
    app.logger.info("Inside learn route")
    app.logger.info(f"Request form: {request.form}")
    if 'learning_text' in request.form:
        learning_text = request.form['learning_text']
        app.logger.info(f"Received learning text: {learning_text}")
        session['original_input'] = learning_text[:5000]
    else:
        app.logger.warning("learning_text not in request.form")
        learning_text = ""
        session['original_input'] = ""
    
    scraped_text = ""
    # Regex to find YouTube video ID from various URL formats
    youtube_regex = r'(?:https?:\/\/)?(?:www\.)?(?:youtube\.com|youtu\.be)\/(?:watch\?v=)?(?:embed\/)?(?:v\/)?(?:shorts\/)?([\w-]{11})(?:\S+)?'
    youtube_match = re.match(youtube_regex, learning_text)

    if youtube_match:
        scraped_text = get_youtube_transcript(learning_text)
    elif re.match(r'http[s]?://', learning_text):
        app.logger.info("Non-YouTube URL detected. Scraping page.")
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36',
                'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8',
                'Accept-Language': 'en-US,en;q=0.9',
                'Connection': 'keep-alive',
            }
            with httpx.Client(headers=headers, follow_redirects=True) as client:
                response = client.get(learning_text)
                response.raise_for_status()
                soup = BeautifulSoup(response.text, 'html.parser')
                for element in soup(["script", "style", "nav", "footer", "header"]):
                    element.extract()
                scraped_text = soup.get_text(separator='\n', strip=True)
        except httpx.HTTPStatusError as e:
            scraped_text = f"Error scraping URL: {e.response.status_code} {e.response.reason_phrase} for url: {e.request.url}"
            app.logger.error(scraped_text)
        except httpx.RequestError as e:
            scraped_text = f"Error scraping URL: {e}"
            app.logger.error(scraped_text)
    else:
        app.logger.info("Plain text input detected.")
        scraped_text = learning_text

    scraped_text = scraped_text[:50000]

    with tempfile.NamedTemporaryFile(mode='w+', delete=False, encoding='utf-8') as f:
        f.write(scraped_text)
        session['scraped_text_file'] = f.name

    # The summarization step has been removed. We now pass the scraped_text directly.
    # A thread pool is used to run the two extraction API calls concurrently to improve performance.
    from concurrent.futures import ThreadPoolExecutor

    with ThreadPoolExecutor() as executor:
        future_strategies = executor.submit(extract_strategies, scraped_text)
        future_ideas = executor.submit(extract_conceptual_ideas, scraped_text)
        
        extracted_strategies = future_strategies.result()
        extracted_ideas = future_ideas.result()

    # Flash messages in the main request context
    if "Could not extract" in extracted_strategies:
        flash("Error: Could not extract strategies. The primary model failed.", "error")
    else:
        flash("Successfully extracted strategies.", "success")

    if "Could not extract" in extracted_ideas:
        flash("Error: Could not extract conceptual ideas.", "error")
    else:
        flash("Successfully extracted conceptual ideas.", "success")

    with tempfile.NamedTemporaryFile(mode='w+', delete=False, encoding='utf-8') as f:
        f.write(extracted_strategies)
        session['extracted_strategies_file'] = f.name

    with tempfile.NamedTemporaryFile(mode='w+', delete=False, encoding='utf-8') as f:
        f.write(extracted_ideas)
        session['extracted_ideas_file'] = f.name

    return redirect(url_for('results'))

@app.route('/results')
def results():
    if not session.get('logged_in'):
        return redirect(url_for('index'))

    original_input = session.get('original_input', '')
    
    scraped_text = ""
    if 'scraped_text_file' in session:
        try:
            with open(session['scraped_text_file'], 'r', encoding='utf-8') as f:
                scraped_text = f.read()
        except FileNotFoundError:
            scraped_text = "Could not find scraped text."

    extracted_strategies = ""
    if 'extracted_strategies_file' in session:
        try:
            with open(session['extracted_strategies_file'], 'r', encoding='utf-8') as f:
                extracted_strategies = f.read()
        except FileNotFoundError:
            extracted_strategies = "Could not find extracted strategies."

    extracted_ideas = ""
    if 'extracted_ideas_file' in session:
        try:
            with open(session['extracted_ideas_file'], 'r', encoding='utf-8') as f:
                extracted_ideas = f.read()
        except FileNotFoundError:
            extracted_ideas = "Could not find extracted ideas."

    return render_template('results.html', original_input=original_input, scraped_text=scraped_text, extracted_strategies=extracted_strategies, extracted_ideas=extracted_ideas)

def _deduplicate_strategies():
    """Helper to deduplicate strategies.json."""
    if not os.path.exists(STRATEGIES_FILE):
        return 0

    try:
        with open(STRATEGIES_FILE, 'r', encoding='utf-8') as f:
            strategies = json.load(f)

        unique_strategies = []
        seen_content = set()

        # Deduplicate based on content hash/string representation
        # Strategy can be a dict or a string (legacy)
        for s in strategies:
            if isinstance(s, dict):
                # Create a normalized content string for checking duplicates
                # We ignore ID for deduplication, we want to remove identical logic
                content_key = f"{s.get('category')}|{s.get('trigger')}|{s.get('advice')}"
                if content_key not in seen_content:
                    seen_content.add(content_key)
                    unique_strategies.append(s)
            else:
                # String case
                content_key = str(s).strip()
                if content_key not in seen_content:
                    seen_content.add(content_key)
                    unique_strategies.append(s)

        removed_count = len(strategies) - len(unique_strategies)

        if removed_count > 0:
            with open(STRATEGIES_FILE, 'w', encoding='utf-8') as f:
                json.dump(unique_strategies, f, indent=4)

        return removed_count
    except Exception as e:
        app.logger.error(f"Error deduplicating strategies: {e}")
        raise e

def _deduplicate_intelligence():
    """Helper to deduplicate intelligence.json (Exact Match)."""
    if not os.path.exists(INTELLIGENCE_FILE):
        return 0

    try:
        with open(INTELLIGENCE_FILE, 'r', encoding='utf-8') as f:
            intelligence = json.load(f)

        unique_intelligence = []
        seen_content = set()

        for i in intelligence:
            # Check if intelligence is an object (new format) or string (old format/fallback)
            if isinstance(i, dict) and 'content' in i:
                content_key = str(i['content']).strip()
            else:
                content_key = str(i).strip()

            if content_key not in seen_content:
                seen_content.add(content_key)
                unique_intelligence.append(i)

        removed_count = len(intelligence) - len(unique_intelligence)

        if removed_count > 0:
            with open(INTELLIGENCE_FILE, 'w', encoding='utf-8') as f:
                json.dump(unique_intelligence, f, indent=4)

        return removed_count
    except Exception as e:
        app.logger.error(f"Error deduplicating intelligence: {e}")
        raise e

@app.route('/api/homogenize/intelligence', methods=['POST'])
def homogenize_intelligence():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 403

    try:
        # Trigger Celery Task Asynchronously
        homogenize_intelligence_task.delay()
        return jsonify({'status': 'started', 'message': 'Homogenization started in background.'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/homogenize/status')
def homogenize_status():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 403

    try:
        # Connect to Redis using default or env URL
        redis_url = os.getenv('REDIS_URL', 'redis://localhost:6379/0')
        r = redis.from_url(redis_url)

        status_json = r.get("homogenization_status")
        if status_json:
            return jsonify(json.loads(status_json))
        else:
            return jsonify({"status": "Idle"})

    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/remove-duplicates/strategies', methods=['POST'])
def remove_duplicates_strategies():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 403

    try:
        count = _deduplicate_strategies()
        return jsonify({'status': 'success', 'removed_count': count, 'message': f'Removed {count} duplicate strategies.'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/remove-duplicates/intelligence', methods=['POST'])
def remove_duplicates_intelligence():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 403

    try:
        count = _deduplicate_intelligence()
        return jsonify({'status': 'success', 'removed_count': count, 'message': f'Removed {count} duplicate ideas.'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/remove-duplicates/all', methods=['POST'])
def remove_duplicates_all():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 403

    try:
        strat_count = _deduplicate_strategies()
        intel_count = _deduplicate_intelligence()
        total = strat_count + intel_count
        return jsonify({
            'status': 'success',
            'removed_count': total,
            'message': f'Removed {strat_count} duplicate strategies and {intel_count} duplicate ideas.'
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/approve', methods=['POST'])
def approve():
    if not session.get('logged_in'):
        return redirect(url_for('index'))
    
    approved_strategies = request.form.get('approved_strategies')
    approved_ideas = request.form.get('approved_ideas')
    
    app.logger.info("Approved Strategies:")
    app.logger.info(approved_strategies)

    app.logger.info("Approved Conceptual Ideas:")
    app.logger.info(approved_ideas)

    # Save the approved strategies to a file
    if approved_strategies:
        try:
            # Load existing strategies
            if os.path.exists(STRATEGIES_FILE):
                with open(STRATEGIES_FILE, 'r', encoding='utf-8') as f:
                    strategies = json.load(f)
            else:
                strategies = []
            
            # Parse the approved strategies from JSON string
            try:
                # Ensure no markdown formatting lingers
                clean_strategies_json = re.sub(r'^```json\s*|\s*```$', '', approved_strategies.strip(), flags=re.MULTILINE)
                new_strategies = json.loads(clean_strategies_json)
                if not isinstance(new_strategies, list):
                    # Fallback if it's not a list (single object?)
                    new_strategies = [new_strategies]
            except json.JSONDecodeError:
                # Fallback for legacy text format (just in case)
                app.logger.warning("Failed to parse approved_strategies as JSON. Treating as text.")
                # If JSON parsing fails, we assume it's a newline-separated list of strings.
                # We need to be careful not to create garbage from markdown artifacts if the regex failed or wasn't enough.
                lines = approved_strategies.strip().split('\n')
                clean_lines = [line.strip() for line in lines if line.strip() and not line.strip().startswith('```')]

                new_strategies = [{"id": str(uuid.uuid4()), "advice": s, "trigger": "Manual Entry", "category": "General"}
                                  for s in clean_lines]

            # Build set of existing content for deduplication
            existing_content = set()
            for s in strategies:
                if isinstance(s, dict):
                    content_key = f"{s.get('category')}|{s.get('trigger')}|{s.get('advice')}"
                else:
                    content_key = str(s).strip()
                existing_content.add(content_key)

            added_count = 0
            skipped_count = 0
            today_str = datetime.now().strftime('%Y-%m-%d')

            for ns in new_strategies:
                # Determine content key for the new strategy
                if isinstance(ns, dict):
                    # Ensure ID exists
                    if not ns.get('id'):
                        ns['id'] = str(uuid.uuid4())

                    # Ensure date_added exists for new items
                    if not ns.get('date_added'):
                        ns['date_added'] = today_str

                    ns_content_key = f"{ns.get('category')}|{ns.get('trigger')}|{ns.get('advice')}"
                else:
                    # Shouldn't happen given parsing logic above, but handle safely
                    ns_content_key = str(ns).strip()
                    ns = {
                         "id": str(uuid.uuid4()),
                         "advice": str(ns),
                         "trigger": "Manual Entry",
                         "category": "General",
                         "date_added": today_str
                    }

                if ns_content_key not in existing_content:
                    strategies.append(ns)
                    existing_content.add(ns_content_key)
                    added_count += 1
                else:
                    skipped_count += 1

            with open(STRATEGIES_FILE, 'w', encoding='utf-8') as f:
                json.dump(strategies, f, indent=4)
            
            msg = f"Saved {added_count} new strategies."
            if skipped_count > 0:
                msg += f" Skipped {skipped_count} duplicates."
            flash(msg, "success")

        except Exception as e:
            app.logger.error(f"Error saving strategies: {e}", exc_info=True)
            flash("An error occurred while saving the strategies.", "error")

    # Save the approved ideas to the intelligence.json file
    if approved_ideas:
        try:
            if os.path.exists(INTELLIGENCE_FILE):
                with open(INTELLIGENCE_FILE, 'r', encoding='utf-8') as f:
                    ideas = json.load(f)
            else:
                ideas = []
            
            new_ideas = [i.strip() for i in approved_ideas.strip().split('\n') if i.strip()]
            
            added_ideas_count = 0
            skipped_ideas_count = 0
            today_str = datetime.now().strftime('%Y-%m-%d')

            # Use a set for faster lookup of existing ideas
            # ideas list now contains objects after migration, but might contain strings if fresh DB
            existing_ideas_set = set()
            for i in ideas:
                if isinstance(i, dict) and 'content' in i:
                    existing_ideas_set.add(str(i['content']).strip())
                else:
                    existing_ideas_set.add(str(i).strip())

            for idea_content in new_ideas:
                if idea_content not in existing_ideas_set:
                    new_idea_obj = {
                        "content": idea_content,
                        "date_added": today_str
                    }
                    ideas.append(new_idea_obj)
                    existing_ideas_set.add(idea_content)
                    added_ideas_count += 1
                else:
                    skipped_ideas_count += 1

            with open(INTELLIGENCE_FILE, 'w', encoding='utf-8') as f:
                json.dump(ideas, f, indent=4)

            msg = f"Saved {added_ideas_count} new ideas to Intelligence."
            if skipped_ideas_count > 0:
                msg += f" Skipped {skipped_ideas_count} duplicates."
            flash(msg, "success")

        except Exception as e:
            app.logger.error(f"Error saving conceptual ideas: {e}", exc_info=True)
            flash("An error occurred while saving the conceptual ideas.", "error")


    # Clean up the session to prevent cookie size issues
    for key in ['scraped_text_file', 'extracted_strategies_file', 'original_input', 'extracted_ideas_file']:
        if key in session:
            try:
                if session[key] and os.path.exists(session[key]):
                    os.remove(session[key])
            except (OSError, TypeError):
                pass
            session.pop(key, None)
    
    flash('Strategies approved and session cleared.', 'success')
    return redirect(url_for('guided_learning'))

@app.route('/clear_session')
def clear_session():
    for key in ['scraped_text_file', 'summary_file', 'extracted_strategies_file', 'original_input']:
        if key in session:
            try:
                if session[key] and os.path.exists(session[key]):
                    os.remove(session[key])
            except (OSError, TypeError):
                pass
            session.pop(key, None)
    session.clear()
    flash('Session cleared!', 'success')
    return redirect(url_for('guided_learning'))

@app.route('/test_route', methods=['POST'])
def test_route():
    app.logger.info("Test route called!")
    return "Test route called!"

def get_youtube_transcript(url: str) -> str:
    """
    Fetches the transcript of a YouTube video using the youtube-transcript-api library.
    """
    app.logger.info(f"Attempting to fetch transcript for {url} using youtube-transcript-api.")
    
    # Regex to find YouTube video ID
    youtube_regex = r'(?:https?:\/\/)?(?:www\.)?(?:youtube\.com|youtu\.be)\/(?:watch\?v=)?(?:embed\/)?(?:v\/)?(?:shorts\/)?([\w-]{11})(?:\S+)?'
    match = re.search(youtube_regex, url)
    if not match:
        app.logger.error(f"Could not extract YouTube video ID from URL: {url}")
        return "Error: Could not extract YouTube video ID from URL."

    video_id = match.group(1)
    
    try:
        # --- Bright Data Proxy Configuration ---
        bd_user = os.getenv("BRIGHTDATA_USERNAME")
        bd_pass = os.getenv("BRIGHTDATA_PASSWORD")
        bd_host = os.getenv("BRIGHTDATA_HOST")

        proxy_config = None
        if all([bd_user, bd_pass, bd_host]):
            proxy_url = f'http://{bd_user}:{bd_pass}@{bd_host}:9222'
            proxy_config = GenericProxyConfig(
                http_url=proxy_url,
                https_url=proxy_url,
            )
            app.logger.info(f"Using Bright Data proxy: {bd_host}")
        else:
            app.logger.warning("Bright Data credentials not fully configured. Proceeding without proxy.")

        # Create an instance of the API, passing the proxy config if it exists
        api = YouTubeTranscriptApi(proxy_config=proxy_config)
        
        # The method is .list(), not .list_transcripts()
        transcript_list_obj = api.list(video_id)

        # Find the English transcript
        transcript = transcript_list_obj.find_transcript(['en'])
        
        # Fetch the transcript data
        transcript_data = transcript.fetch()
        
        # Join the text segments
        transcript_text = " ".join([item['text'] for item in transcript_data])
        
        app.logger.info(f"Successfully fetched transcript for video ID: {video_id}")
        return transcript_text
    except Exception as e:
        app.logger.error(f"Could not fetch transcript for video ID {video_id}: {e}", exc_info=True)
        return f"Error: Could not retrieve transcript. The video may have transcripts disabled, or an API error occurred: {str(e)}"

# --- Keepa Scan Status Management ---
STATUS_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'scan_status.json')
LOGS_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'static/logs')
DATA_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'data')

def get_scan_status():
    if not os.path.exists(STATUS_FILE):
        return {"status": "Idle"}
    try:
        with open(STATUS_FILE, 'r') as f:
            return json.load(f)
    except (IOError, json.JSONDecodeError):
        return {"status": "Error", "message": "Could not read status file."}

def set_scan_status(status_data):
    try:
        with open(STATUS_FILE, 'w') as f:
            json.dump(status_data, f, indent=4)
    except IOError:
        app.logger.error(f"Could not write to status file: {STATUS_FILE}")

@app.route('/data_sourcing')
def data_sourcing():
    if not session.get('logged_in'):
        return redirect(url_for('index'))
    
    status_data = get_scan_status()
    # Check if the process is still running to catch crashes
    if status_data.get('status') == 'Running' and status_data.get('pid'):
        try:
            os.kill(status_data['pid'], 0)
        except OSError:
            status_data['status'] = 'Failed'
            status_data['message'] = 'The process disappeared unexpectedly. Check logs for details.'
            set_scan_status(status_data)
            
    return render_template('data_sourcing.html', status=status_data)

@app.route('/start-keepa-scan', methods=['POST'])
def start_keepa_scan():
    if not session.get('logged_in'):
        return jsonify({'status': 'error', 'message': 'Not logged in'}), 401

    status = get_scan_status()
    if status.get('status') == 'Running':
        # Optionally, you could check the task state if you store the task_id
        flash('A scan is already in progress.', 'warning')
        return redirect(url_for('data_sourcing'))

    limit_str = request.form.get('limit')
    limit = int(limit_str) if limit_str and limit_str.isdigit() else None

    # Immediately set status to "Running" to provide feedback and prevent race conditions.
    # The Celery task will overwrite this with more details, but this is a crucial first step.
    set_scan_status({
        "status": "Running",
        "start_time": datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%S.%f')[:-3] + 'Z',
        "message": "Scan has been queued. Waiting for worker to start processing...",
        "task_id": None # Task ID will be set by the worker
    })

    # Trigger the task
    # task = run_keepa_script.delay(
    #     api_key=KEEPA_API_KEY,
    #     no_cache=True, # Or get this from the form
    #     output_dir='data',
    #     deal_limit=limit,
    #     status_update_callback=None # Cannot pass this from here
    # )

    # # The Celery task is now responsible for setting the initial status.
    # # We can store the task_id in the session for potential future use.
    # session['latest_task_id'] = task.id
    pass

    flash('Keepa scan has been initiated in the background.', 'success')
    return redirect(url_for('data_sourcing'))

@app.route('/scan-status')
def scan_status_endpoint():
    if not session.get('logged_in'):
        return jsonify({'status': 'error', 'message': 'Not logged in'}), 401

    status_data = get_scan_status()
    return jsonify(status_data)

@app.route('/download/<path:filename>')
def download_file(filename):
    if not session.get('logged_in'):
        return redirect(url_for('index'))
    return send_from_directory(DATA_DIR, filename, as_attachment=True)

@app.route('/settings', methods=['GET', 'POST'])
def settings():
    if not session.get('logged_in'):
        return redirect(url_for('index'))

    # --- Session Re-hydration Logic ---
    ensure_sp_api_session()

    if request.method == 'POST':
        try:
            # Load existing settings to preserve any other values not on this form
            try:
                with open(SETTINGS_FILE, 'r') as f:
                    settings_data = json.load(f)
            except (FileNotFoundError, json.JSONDecodeError):
                settings_data = {}

            # Update with business cost values
            tax_exempt = 'tax_exempt' in request.form
            estimated_tax = request.form.get('estimated_tax_per_book', 0, type=int)
            settings_data.update({
                'prep_fee_per_book': request.form.get('prep_fee_per_book', 0.0, type=float),
                'estimated_shipping_per_book': request.form.get('estimated_shipping_per_book', 0.0, type=float),
                'estimated_tax_per_book': 0 if tax_exempt else estimated_tax,
                'tax_exempt': tax_exempt,
                'default_markup': request.form.get('default_markup', 0, type=int)
            })


            with open(SETTINGS_FILE, 'w') as f:
                json.dump(settings_data, f, indent=4)

            # Trigger the background recalculation task by name
            celery_app.send_task('keepa_deals.recalculator.recalculate_deals')

            flash('Settings saved successfully! Recalculating deals in the background...', 'success')
        except Exception as e:
            flash(f'Error saving settings: {e}', 'error')
        return redirect(url_for('settings'))

    # GET request
    try:
        with open(SETTINGS_FILE, 'r') as f:
            settings_data = json.load(f)
    except (FileNotFoundError, json.JSONDecodeError):
        # Default settings if file doesn't exist or is empty
        settings_data = {
            "prep_fee_per_book": 2.50,
            "estimated_shipping_per_book": 2.00,
            "estimated_tax_per_book": 15,
            "tax_exempt": False,
            "default_markup": 10
        }

    return render_template('settings.html', settings=settings_data)

@app.cli.command("fetch-keepa-deals")
@click.option('--no-cache', is_flag=True, help="Force fresh Keepa API calls.")
@click.option('--output-dir', default='data', help="Directory to save the output CSV file.")
@click.option('--limit', type=int, default=None, help="Limit the number of deals to process for testing.")
def fetch_keepa_deals_command(no_cache, output_dir, limit):
    """
    Runs the Keepa deals fetching script, wrapped with status reporting.
    """
    cli_status_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'scan_status.json')

    def _update_cli_status(new_status_dict):
        try:
            # Ensure we read the latest status before writing to avoid race conditions
            current_status = {}
            if os.path.exists(cli_status_file):
                with open(cli_status_file, 'r') as f:
                    current_status = json.load(f)
            
            current_status.update(new_status_dict)

            with open(cli_status_file, 'w') as f:
                json.dump(current_status, f, indent=4)
        except (IOError, json.JSONDecodeError) as e:
            print(f"CLI Error: Could not write to status file: {cli_status_file}. Error: {e}", file=sys.stderr)

    try:
        # Robustly reconfigure logging for this command
        root_logger = logging.getLogger()
        # Set level to DEBUG for detailed analysis
        root_logger.setLevel(logging.DEBUG)
        # Remove any handlers configured by the main app
        root_logger.handlers = []
        # Add a new handler that writes to stdout
        handler = logging.StreamHandler(sys.stdout)
        handler.setLevel(logging.DEBUG)
        formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
        handler.setFormatter(formatter)
        root_logger.addHandler(handler)

        print("--- Running fetch-keepa-deals command ---")
        dotenv_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '.env')
        load_dotenv(dotenv_path=dotenv_path)
        KEEPA_API_KEY = os.getenv("KEEPA_API_KEY")
        if not KEEPA_API_KEY:
            print("KEEPA_API_KEY not found in environment.", file=sys.stderr)
            raise ValueError("KEEPA_API_KEY not found")
        
        print(f"KEEPA_API_KEY loaded: {'*' * len(KEEPA_API_KEY)}")
        app.logger.info("Starting Keepa deals fetching command...")
        
        # run_keepa_script.delay(
        #     api_key=KEEPA_API_KEY,
        #     no_cache=no_cache,
        #     output_dir=output_dir,
        #     deal_limit=limit,
        #     status_update_callback=None
        # )
        pass
        
        print("run_keepa_script finished successfully.")
        app.logger.info("Keepa deals fetching command finished successfully.")

        # On success, update status
        _update_cli_status({
            'status': 'Completed',
            'end_time': datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%S.%f')[:-3] + 'Z',
            'output_file': f"{output_dir}/Keepa_Deals_Export.csv",
            'message': 'Scan completed successfully.'
        })

    except Exception as e:
        print(f"An error occurred during fetch-keepa-deals: {e}", file=sys.stderr)
        app.logger.error(f"An error occurred during fetch-keepa-deals: {e}", exc_info=True)
        # On failure, update status
        _update_cli_status({
            'status': 'Failed',
            'end_time': datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%S.%f')[:-3] + 'Z',
            'message': f"An error occurred: {str(e)}"
        })

@app.route('/deals', methods=['GET', 'POST'])
def deals():
    if not session.get('logged_in'):
        return redirect(url_for('index'))

    KEEPA_QUERY_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'keepa_query.json')

    if request.method == 'POST':
        action = request.form.get('action')

        if action == 'update_query':
            keepa_query = request.form.get('keepa_query')
            try:
                json.loads(keepa_query)
                with open(KEEPA_QUERY_FILE, 'w') as f:
                    f.write(keepa_query)
                flash('Keepa query saved successfully!', 'success')
            except json.JSONDecodeError:
                flash('Invalid JSON. Please check the syntax.', 'error')
            except Exception as e:
                flash(f'Error saving Keepa query: {e}', 'error')

        return redirect(url_for('deals'))

    # GET request
    try:
        with open(KEEPA_QUERY_FILE, 'r') as f:
            keepa_query = f.read()
    except (FileNotFoundError, json.JSONDecodeError):
        keepa_query = ''

    return render_template('deals.html', keepa_query=keepa_query)

@app.route('/api/deals')
def api_deals():
    try:
        # DB_PATH is now imported from db_utils
        TABLE_NAME = 'deals'
        RESTRICTIONS_TABLE = 'user_restrictions'

        # Check SP-API connection status from session
        is_sp_api_connected = session.get('sp_api_connected', False)
        user_id = session.get('sp_api_user_id')

        # --- Connect and get column names ---
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (TABLE_NAME,))
        if cursor.fetchone() is None:
            conn.close()
            return jsonify({
                "pagination": {"total_records": 0, "total_pages": 0, "current_page": 1, "limit": 50},
                "deals": [],
                "message": "No data found. Please run a scan."
            })

        cursor.execute(f"PRAGMA table_info({TABLE_NAME})")
        available_columns = [row['name'] for row in cursor.fetchall()]
    except sqlite3.Error as e:
        app.logger.error(f"Database error when fetching column info: {e}")
        return jsonify({"error": "Database error", "message": str(e)}), 500

    # --- Pagination and Sorting ---
    page = request.args.get('page', 1, type=int)
    limit = request.args.get('limit', 50, type=int)
    offset = (page - 1) * limit
    sort_by = request.args.get('sort', 'id')
    order = request.args.get('order', 'asc').lower()
    if order not in ['asc', 'desc']:
        order = 'asc'

    # --- Filtering ---
    filters = {
        "sales_rank_current_lte": request.args.get('sales_rank_current_lte', type=int),
        "roi_gte": request.args.get('roi_gte', type=int),
        "drops_30_gte": request.args.get('drops_30_gte', type=int),
        "keyword": request.args.get('keyword', type=str),
        "deal_trust_gte": request.args.get('deal_trust_gte', type=int),
        "seller_trust_gte": request.args.get('seller_trust_gte', type=int),
        "profit_gte": request.args.get('profit_gte', type=float),
        "percent_down_gte": request.args.get('percent_down_gte', type=int),
        "hide_gated": request.args.get('hide_gated', type=int),
        "hide_amz": request.args.get('hide_amz', type=int),
        "excluded_conditions": request.args.get('excluded_conditions', type=str)
    }
    where_clauses = []
    filter_params = []

    # Exclude Condition Filtering
    if filters.get("excluded_conditions"):
        cond_list = filters["excluded_conditions"].split(',')
        # We process exclusions by adding AND clauses.
        # Since DB can have '1' OR 'New', we must exclude BOTH representations.

        for c in cond_list:
            if c == 'New':
                # Strict check for New to avoid excluding 'New, other' if desired, or exclude all New variations?
                # Usually 'New' means new condition.
                where_clauses.append("(\"Condition\" != '1' AND \"Condition\" != 'New' AND \"Condition\" NOT LIKE 'New, %')")
            elif c == 'U-Like New':
                where_clauses.append("(\"Condition\" != '2' AND \"Condition\" != 'like new' AND \"Condition\" != 'Used - Like New')")
            elif c == 'U-Very Good':
                where_clauses.append("(\"Condition\" != '3' AND \"Condition\" != 'very good' AND \"Condition\" != 'Used - Very Good')")
            elif c == 'U-Good':
                where_clauses.append("(\"Condition\" != '4' AND \"Condition\" != 'good' AND \"Condition\" != 'Used - Good')")
            elif c == 'U-Acceptable':
                where_clauses.append("(\"Condition\" != '5' AND \"Condition\" != 'acceptable' AND \"Condition\" != 'Used - Acceptable')")
            elif c == 'Collectible':
                # Exclude anything starting with Collectible or C-
                where_clauses.append("(\"Condition\" NOT LIKE 'Collectible%' AND \"Condition\" NOT LIKE 'C-%' AND \"Condition\" NOT LIKE 'C -%')")

    # (Existing filter logic remains the same...)
    if filters.get("sales_rank_current_lte") is not None:
        where_clauses.append("\"Sales_Rank_Current\" <= ?")
        filter_params.append(filters["sales_rank_current_lte"])

    # New ROI Filter: (Profit / All_in_Cost) * 100
    if filters.get("roi_gte") is not None and filters["roi_gte"] > 0:
        # Prevent division by zero and negative/zero cost issues by ensuring Cost > 0
        # Robustly handle currency symbols (e.g., "$15.00") by stripping $ and , before casting
        sanitized_cost = "CAST(REPLACE(REPLACE(\"All_in_Cost\", '$', ''), ',', '') AS REAL)"
        sanitized_profit = "CAST(REPLACE(REPLACE(\"Profit\", '$', ''), ',', '') AS REAL)"
        where_clauses.append(f"({sanitized_cost} > 0 AND (({sanitized_profit} * 1.0 / {sanitized_cost}) * 100) >= ?)")
        filter_params.append(filters["roi_gte"])

    # New Drops Filter
    if filters.get("drops_30_gte") is not None and filters["drops_30_gte"] > 0:
        where_clauses.append("\"Sales_Rank_Drops_last_30_days\" >= ?")
        filter_params.append(filters["drops_30_gte"])

    if filters.get("keyword"):
        keyword_like = f"%{filters['keyword']}%"
        keyword_clauses = ["\"Title\" LIKE ?", "\"Categories_Sub\" LIKE ?", "\"Detailed_Seasonality\" LIKE ?", "\"Manufacturer\" LIKE ?", "\"Author\" LIKE ?", "\"Seller\" LIKE ?"]
        where_clauses.append(f"({ ' OR '.join(keyword_clauses) })")
        filter_params.extend([keyword_like] * len(keyword_clauses))

    # New Filters
    if filters.get("deal_trust_gte") is not None and filters["deal_trust_gte"] > 0:
        # Cast to INTEGER to handle text values like '75%' correctly against numerical threshold
        where_clauses.append("CAST(\"Deal_Trust\" AS INTEGER) >= ?")
        filter_params.append(filters["deal_trust_gte"])

    if filters.get("seller_trust_gte") is not None and filters["seller_trust_gte"] > 0:
        # User input is 0-10, DB is 0-1 (Wilson Score).
        # Display logic rounds to nearest tenth (0.95 -> 10/10, 0.85 -> 9/10).
        # We adjust the filter to match the rounding lower bound: (input - 0.5) / 10.0
        seller_trust_db_value = (filters["seller_trust_gte"] - 0.5) / 10.0
        where_clauses.append("\"Seller_Quality_Score\" >= ?")
        filter_params.append(seller_trust_db_value)

    # Enforce Profit > 0 by default to exclude negative/zero profit deals
    # Robustly handle currency symbols
    sanitized_profit = "CAST(REPLACE(REPLACE(\"Profit\", '$', ''), ',', '') AS REAL)"
    if filters.get("profit_gte") is None or filters["profit_gte"] <= 0:
        where_clauses.append(f"{sanitized_profit} > 0")
    else:
        where_clauses.append(f"{sanitized_profit} >= ?")
        filter_params.append(filters["profit_gte"])

    # Enforce Data Completeness (Global Filters)
    # Using correct column names from DB schema: 'List_at' (sanitized, but originally had space) and '1yr_Avg'
    # Wait, 'List at' sanitizes to 'List_at' in db_utils.py, but pragma output showed 'List_at' and '1yr_Avg'.
    # The grep output confirms: 14|1yr_Avg|TEXT and 236|List_at|REAL.
    # So underscores ARE correct in the DB schema for these specific columns.

    where_clauses.append("\"List_at\" IS NOT NULL")
    where_clauses.append("\"List_at\" > 0")
    where_clauses.append("\"1yr_Avg\" IS NOT NULL")
    # Filter out common placeholders for missing data in TEXT column and ensure numeric validity
    where_clauses.append("\"1yr_Avg\" NOT IN ('-', 'N/A', '', '0', '0.00', '$0.00')")
    where_clauses.append("\"1yr_Avg\" != 0")

    if filters.get("percent_down_gte") is not None and filters["percent_down_gte"] > 0:
        # Cast to INTEGER to handle text values like '20%' correctly against numerical threshold
        where_clauses.append("CAST(\"Percent_Down\" AS INTEGER) >= ?")
        filter_params.append(filters["percent_down_gte"])

    if filters.get("hide_gated") == 1:
        # Exclude restricted items (is_restricted = 1).
        # Only apply if connected, otherwise ignore this filter to prevent SQL error (missing 'ur' alias).
        if is_sp_api_connected and user_id:
             # Include NULL (Pending), 0 (Not Restricted), -1 (Error).
             where_clauses.append("(ur.is_restricted IS NULL OR ur.is_restricted != 1)")
        # If not connected, we can't filter gated items, so we default to showing them (safe fail-open).

    if filters.get("hide_amz") == 1:
        # Exclude items where Amazon is selling (AMZ column has warning icon '⚠️').
        where_clauses.append("(d.\"AMZ\" IS NULL OR d.\"AMZ\" != '⚠️')")


    # --- Build and Execute Query ---
    try:
        # Refactored to remove 'd' alias and use 'deals' explicitly to match deal_count success pattern
        select_clause = "deals.*"
        from_clause = f"FROM {TABLE_NAME}"
        query_params = []

        if is_sp_api_connected and user_id:
            select_clause += ", ur.is_restricted, ur.approval_url"
            from_clause += f" LEFT JOIN {RESTRICTIONS_TABLE} AS ur ON deals.\"ASIN\" = ur.asin AND ur.user_id = ?"
            query_params.append(user_id)
        
        query_params.extend(filter_params)

        # Ensure where clauses use table name if they were using alias 'd.'
        final_where_clauses = []
        for clause in where_clauses:
            final_where_clauses.append(clause.replace('d.', 'deals.'))

        where_sql = " WHERE " + " AND ".join(final_where_clauses) if final_where_clauses else ""

        # Get total count (filtered)
        count_query = f"SELECT COUNT(*) {from_clause}{where_sql}"
        total_records = cursor.execute(count_query, query_params).fetchone()[0]
        total_pages = (total_records + limit - 1) // limit if limit > 0 else 1

        # Get absolute total count (unfiltered) for UI notification logic
        total_db_records = cursor.execute(f"SELECT COUNT(*) FROM {TABLE_NAME}").fetchone()[0]

        # Get data for the current page
        query_params.extend([limit, offset])

        if sort_by == 'Gated':
            if is_sp_api_connected and user_id:
                sort_clause = 'ur.is_restricted'
            else:
                sort_clause = 'deals."id"'
        elif sort_by == 'ROI':
            # Dynamically calculate and sort by ROI: (Profit / All_in_Cost)
            # Use CAST and NULLIF to prevent division by zero errors in SQLite
            sort_clause = '(CAST(deals."Profit" AS REAL) / NULLIF(CAST(deals."All_in_Cost" AS REAL), 0))'
        elif sort_by in available_columns:
            sort_clause = f'deals."{sort_by}"'
        else:
            sort_clause = 'deals."id"'

        data_query = f"SELECT {select_clause} {from_clause}{where_sql} ORDER BY {sort_clause} {order} LIMIT ? OFFSET ?"

        # Log the query for debugging
        app.logger.debug(f"Executing Deals Query: {data_query} | Params: {query_params}")

        deal_rows = cursor.execute(data_query, query_params).fetchall()
        deals_list = [dict(row) for row in deal_rows]

        # --- Post-processing and Formatting ---
        for deal in deals_list:
            # Handle restriction status
            if is_sp_api_connected:
                is_restricted = deal.get('is_restricted')
                if is_restricted is None:
                    deal['restriction_status'] = 'pending_check'
                elif is_restricted == 1:
                    deal['restriction_status'] = 'restricted'
                elif is_restricted == -1:
                    deal['restriction_status'] = 'error'
                else:
                    deal['restriction_status'] = 'not_restricted'

            # (Existing formatting logic for conditions, bindings, etc. remains the same...)
            # binding_map removed in favor of automatic formatting
            condition_string_map = {"New": "N", "Used - Like New": "U - LN", "Used - Very Good": "U - VG", "Used - Good": "U - G", "Used - Acceptable": "U - A"}
            condition_code_map = {"1": "New", "2": "Used - Like New", "3": "Used - Very Good", "4": "Used - Good", "5": "Used - Acceptable"}

            if 'Condition' in deal and deal['Condition'] and str(deal['Condition']).isdigit():
                deal['Condition'] = condition_code_map.get(str(deal['Condition']), f"Unknown ({deal['Condition']})")

            # Automatic formatting for Binding: remove underscores/hyphens, title case
            if 'Binding' in deal and deal['Binding']:
                deal['Binding'] = str(deal['Binding']).replace('_', ' ').replace('-', ' ').title()

            if 'Condition' in deal and deal['Condition'] in condition_string_map:
                deal['Condition'] = condition_string_map[deal['Condition']]

    except Exception as e:
        app.logger.error(f"API Deals Error: {e}", exc_info=True)
        return jsonify({"error": "Server Error", "message": str(e)}), 500
    finally:
        if 'conn' in locals() and conn:
            conn.close()

    # --- Format and Return Response ---
    response = {
        "pagination": {
            "total_records": total_records,
            "total_db_records": total_db_records,
            "total_pages": total_pages,
            "current_page": page,
            "limit": limit
        },
        "deals": deals_list
    }
    
    return jsonify(response)

@app.route('/api/recalc-status')
def recalc_status():
    if not session.get('logged_in'):
        return jsonify({'status': 'error', 'message': 'Not logged in'}), 401

    RECALC_STATUS_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'recalc_status.json')

    if not os.path.exists(RECALC_STATUS_FILE):
        return jsonify({"status": "Idle"}) # Or "Not Found", depending on desired frontend logic

    try:
        with open(RECALC_STATUS_FILE, 'r') as f:
            status_data = json.load(f)
        return jsonify(status_data)
    except (IOError, json.JSONDecodeError) as e:
        app.logger.error(f"Could not read or parse recalc_status.json: {e}")
        return jsonify({"status": "Error", "message": "Could not read status file."}), 500


@app.route('/api/debug/deal/<string:asin>')
def debug_deal(asin):
    if not session.get('logged_in'):
        return jsonify({'error': 'Not authenticated'}), 401

    from keepa_deals.keepa_api import fetch_product_batch
    from keepa_deals.token_manager import TokenManager

    # Use the KEEPA_API_KEY defined at the top of the file
    if not KEEPA_API_KEY:
        return jsonify({'error': 'KEEPA_API_KEY not configured on server.'}), 500

    # The fetch_product_batch function expects a list of ASINs
    # We now receive tokens_left as well, so we unpack it and ignore it with _.
    product_data, api_info, tokens_consumed, _ = fetch_product_batch(KEEPA_API_KEY, [asin])

    if api_info and api_info.get('error_status_code'):
        return jsonify({
            'error': 'Failed to fetch data from Keepa API',
            'status_code': api_info.get('error_status_code'),
            'asin': asin
        }), 502

    if not product_data or not product_data.get('products'):
        return jsonify({'error': 'No product data returned from Keepa', 'asin': asin}), 404

    # Return the raw product data as JSON
    return jsonify(product_data['products'][0])


import secrets
from urllib.parse import urlencode

@app.route('/connect_amazon')
def connect_amazon():
    """
    Step 1 of the real OAuth flow: Redirect the user to the Amazon consent page.
    """
    if not SP_API_APP_ID:
        flash("SP-API application ID is not configured.", "error")
        return redirect(url_for('settings'))

    # Generate a random state token to prevent CSRF attacks
    state = secrets.token_urlsafe(16)
    session['oauth_state'] = state

    # Construct the authorization URL
    auth_params = {
        'application_id': SP_API_APP_ID,
        'state': state,
        'redirect_uri': "https://agentarbitrage.co/amazon_callback",
        'version': 'beta'
    }
    authorization_url = f"{AMAZON_AUTH_URL}?{urlencode(auth_params)}"

    flash("Redirecting to Amazon for authentication...", "info")
    return redirect(authorization_url)

@app.route('/reset_test_user_state', methods=['POST'])
def reset_test_user_state():
    """Resets the session state for the test user."""
    session.clear()
    return jsonify({'status': 'success', 'message': 'Test user state reset.'})

@app.route('/amazon_callback')
def amazon_callback():
    """
    Step 2 of the real OAuth flow: Handle the callback from Amazon,
    exchange the authorization code for tokens.
    """
    # --- Security Check: Validate the state token ---
    received_state = request.args.get('state')
    if not received_state or received_state != session.pop('oauth_state', None):
        flash("Invalid state token. Authorization failed due to a potential CSRF attack.", "error")
        app.logger.warning("OAuth failed: State token mismatch.")
        return redirect(url_for('settings'))

    # --- Exchange the authorization code for tokens ---
    auth_code = request.args.get('spapi_oauth_code')
    seller_id = request.args.get('selling_partner_id')

    if not auth_code or not seller_id:
        flash("Authorization failed: Missing required parameters from Amazon.", "error")
        app.logger.warning(f"OAuth failed: Missing auth_code or seller_id. Code: {auth_code}, Seller ID: {seller_id}")
        return redirect(url_for('settings'))

    token_payload = {
        'grant_type': 'authorization_code',
        'code': auth_code,
        'redirect_uri': "https://agentarbitrage.co/amazon_callback",
        'client_id': SP_API_CLIENT_ID,
        'client_secret': SP_API_CLIENT_SECRET
    }

    try:
        with httpx.Client() as client:
            response = client.post(AMAZON_TOKEN_URL, data=token_payload)
            response.raise_for_status()
            token_data = response.json()

        # --- Store Tokens Securely ---
        access_token = token_data['access_token']
        refresh_token = token_data['refresh_token']

        session['sp_api_access_token'] = access_token
        session['sp_api_refresh_token'] = refresh_token
        session['sp_api_token_expiry'] = time.time() + token_data['expires_in']
        session['sp_api_seller_id'] = seller_id
        session['sp_api_connected'] = True
        session['sp_api_user_id'] = seller_id # Use the seller_id as the unique user identifier

        # Persist credentials for background tasks
        save_user_credentials(seller_id, refresh_token)

        app.logger.info(f"Successfully obtained SP-API tokens for seller_id: {seller_id}")

        # --- Trigger the background task with all necessary info ---
        task_args = [seller_id, seller_id, access_token, refresh_token]
        celery_app.send_task('keepa_deals.sp_api_tasks.check_all_restrictions_for_user', args=task_args)

        flash("Successfully connected your Amazon Seller Account!", "success")

    except httpx.HTTPStatusError as e:
        error_details = e.response.json()
        flash(f"Failed to get API tokens from Amazon: {error_details.get('error_description', 'Unknown error')}", "error")
        app.logger.error(f"SP-API token exchange failed: {e.response.text}")
    except Exception as e:
        flash("An unexpected error occurred during token exchange.", "error")
        app.logger.error(f"An unexpected error occurred during token exchange: {e}", exc_info=True)

    return redirect(url_for('settings'))

@app.route('/trigger_restriction_check', methods=['POST'])
def trigger_restriction_check():
    """
    Manually triggers the background restriction check for all deals.
    Useful if the automatic check didn't catch everything or if the user wants to force an update.
    """
    if not session.get('logged_in'):
        return redirect(url_for('index'))

    if not session.get('sp_api_connected'):
        flash("Not connected to Amazon SP-API.", "error")
        return redirect(url_for('settings'))

    user_id = session.get('sp_api_user_id')
    refresh_token = session.get('sp_api_refresh_token')
    seller_id = session.get('sp_api_seller_id') or user_id

    if not user_id or not refresh_token:
        # Attempt to recover from DB if session is partial
        try:
            creds = get_all_user_credentials()
            for c in creds:
                if c['user_id'] == user_id:
                    refresh_token = c['refresh_token']
                    break
        except Exception:
            pass

    if not user_id or not refresh_token:
        flash("Missing credentials. Please try reconnecting via the manual form below.", "error")
        # In a real scenario we might want to let them disconnect, but for now just show error.
        return redirect(url_for('settings'))

    app.logger.info(f"Manually triggering restriction check for user: {user_id}")

    # Trigger task
    # We pass 'manual_placeholder' for access_token so the task refreshes it.
    task_args = [user_id, seller_id, 'manual_placeholder', refresh_token]
    celery_app.send_task('keepa_deals.sp_api_tasks.check_all_restrictions_for_user', args=task_args)

    flash("Restriction check has been queued for all existing deals. Check the Dashboard in a moment.", "success")
    return redirect(url_for('settings'))

@app.route('/manual_sp_api_token', methods=['POST'])
def manual_sp_api_token():
    """
    Handles manual submission of Seller ID and Refresh Token.
    Bypasses the OAuth flow.
    """
    seller_id = request.form.get('seller_id')
    refresh_token = request.form.get('refresh_token')

    if not seller_id or not refresh_token:
        flash("Please provide both Seller ID and Refresh Token.", "error")
        return redirect(url_for('settings'))

    # Store in session (mimicking successful OAuth)
    session['sp_api_connected'] = True
    session['sp_api_user_id'] = seller_id
    session['sp_api_refresh_token'] = refresh_token
    session['sp_api_seller_id'] = seller_id

    # Persist credentials for background tasks
    try:
        save_user_credentials(seller_id, refresh_token)
        app.logger.info(f"Manual SP-API connection for seller_id: {seller_id}")

        # Trigger the background task. Pass a placeholder for access_token so it forces a refresh.
        task_args = [seller_id, seller_id, 'manual_placeholder', refresh_token]
        celery_app.send_task('keepa_deals.sp_api_tasks.check_all_restrictions_for_user', args=task_args)

        flash("Successfully connected manually! Restriction checks started in background.", "success")
    except Exception as e:
        app.logger.error(f"Error saving manual credentials: {e}", exc_info=True)
        flash(f"Error saving credentials to database: {e}", "error")

    return redirect(url_for('settings'))

@app.route('/api/run-janitor', methods=['POST'])
def run_janitor():
    if not session.get('logged_in'):
        return jsonify({'status': 'error', 'message': 'Not logged in'}), 401

    try:
        deleted = _clean_stale_deals_logic(grace_period_hours=72)
        return jsonify({'status': 'success', 'deleted_count': deleted})
    except Exception as e:
        app.logger.error(f"Janitor API failed: {e}")
        return jsonify({'status': 'error', 'message': str(e)}), 500

@app.route('/api/deal-count')
def deal_count():
    if not session.get('logged_in'):
         return jsonify({'status': 'error', 'message': 'Not logged in'}), 401

    try:
        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            # Ensure the table exists before querying
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='deals'")
            if not cursor.fetchone():
                 return jsonify({'count': 0, 'max_id': 0})

            # Filtering Logic (Same as api_deals)
            filters = {
                "sales_rank_current_lte": request.args.get('sales_rank_current_lte', type=int),
                "roi_gte": request.args.get('roi_gte', type=int),
                "drops_30_gte": request.args.get('drops_30_gte', type=int),
                "keyword": request.args.get('keyword', type=str),
                "deal_trust_gte": request.args.get('deal_trust_gte', type=int),
                "seller_trust_gte": request.args.get('seller_trust_gte', type=int),
                "profit_gte": request.args.get('profit_gte', type=float),
                "percent_down_gte": request.args.get('percent_down_gte', type=int),
                "hide_gated": request.args.get('hide_gated', type=int),
                "hide_amz": request.args.get('hide_amz', type=int),
                "excluded_conditions": request.args.get('excluded_conditions', type=str)
            }
            where_clauses = []
            filter_params = []

            # Exclude Condition Filtering
            if filters.get("excluded_conditions"):
                cond_list = filters["excluded_conditions"].split(',')

                for c in cond_list:
                    if c == 'New':
                        where_clauses.append("(\"Condition\" != '1' AND \"Condition\" != 'New' AND \"Condition\" NOT LIKE 'New, %')")
                    elif c == 'U-Like New':
                        where_clauses.append("(\"Condition\" != '2' AND \"Condition\" != 'like new' AND \"Condition\" != 'Used - Like New')")
                    elif c == 'U-Very Good':
                        where_clauses.append("(\"Condition\" != '3' AND \"Condition\" != 'very good' AND \"Condition\" != 'Used - Very Good')")
                    elif c == 'U-Good':
                        where_clauses.append("(\"Condition\" != '4' AND \"Condition\" != 'good' AND \"Condition\" != 'Used - Good')")
                    elif c == 'U-Acceptable':
                        where_clauses.append("(\"Condition\" != '5' AND \"Condition\" != 'acceptable' AND \"Condition\" != 'Used - Acceptable')")
                    elif c == 'Collectible':
                        where_clauses.append("(\"Condition\" NOT LIKE 'Collectible%' AND \"Condition\" NOT LIKE 'C-%' AND \"Condition\" NOT LIKE 'C -%')")

            # Determine connection status for Gated check
            # We need to join user_restrictions for the count if hide_gated is used.
            is_sp_api_connected = session.get('sp_api_connected', False)
            user_id = session.get('sp_api_user_id')

            join_clause = ""
            if filters.get("hide_gated") == 1 and is_sp_api_connected and user_id:
                join_clause = f" LEFT JOIN user_restrictions AS ur ON deals.ASIN = ur.asin AND ur.user_id = ?"
                # We need to inject user_id into params *before* filter params if we use it in JOIN
                # But sqlite parameter order matters.
                # Actually, filtering is in WHERE clause.
                # If we put user_id in JOIN condition, it needs to be passed.
                # Let's handle params order carefully.

            # Param Handling for Join
            params = []
            if filters.get("hide_gated") == 1 and is_sp_api_connected and user_id:
                params.append(user_id)

            if filters.get("sales_rank_current_lte") is not None:
                where_clauses.append("\"Sales_Rank_Current\" <= ?")
                filter_params.append(filters["sales_rank_current_lte"])

            # New ROI Filter
            if filters.get("roi_gte") is not None and filters["roi_gte"] > 0:
                sanitized_cost = "CAST(REPLACE(REPLACE(\"All_in_Cost\", '$', ''), ',', '') AS REAL)"
                sanitized_profit = "CAST(REPLACE(REPLACE(\"Profit\", '$', ''), ',', '') AS REAL)"
                where_clauses.append(f"({sanitized_cost} > 0 AND (({sanitized_profit} * 1.0 / {sanitized_cost}) * 100) >= ?)")
                filter_params.append(filters["roi_gte"])

            # New Drops Filter
            if filters.get("drops_30_gte") is not None and filters["drops_30_gte"] > 0:
                where_clauses.append("\"Sales_Rank_Drops_last_30_days\" >= ?")
                filter_params.append(filters["drops_30_gte"])

            if filters.get("keyword"):
                keyword_like = f"%{filters['keyword']}%"
                keyword_clauses = ["\"Title\" LIKE ?", "\"Categories_Sub\" LIKE ?", "\"Detailed_Seasonality\" LIKE ?", "\"Manufacturer\" LIKE ?", "\"Author\" LIKE ?", "\"Seller\" LIKE ?"]
                where_clauses.append(f"({ ' OR '.join(keyword_clauses) })")
                filter_params.extend([keyword_like] * len(keyword_clauses))

            if filters.get("deal_trust_gte") is not None and filters["deal_trust_gte"] > 0:
                where_clauses.append("CAST(\"Deal_Trust\" AS INTEGER) >= ?")
                filter_params.append(filters["deal_trust_gte"])

            if filters.get("seller_trust_gte") is not None and filters["seller_trust_gte"] > 0:
                # User input is 0-10, DB is 0-1 (Wilson Score).
                # Match rounding lower bound: (input - 0.5) / 10.0
                seller_trust_db_value = (filters["seller_trust_gte"] - 0.5) / 10.0
                where_clauses.append("\"Seller_Quality_Score\" >= ?")
                filter_params.append(seller_trust_db_value)

            # Enforce Profit > 0 by default to exclude negative/zero profit deals
            sanitized_profit = "CAST(REPLACE(REPLACE(\"Profit\", '$', ''), ',', '') AS REAL)"
            if filters.get("profit_gte") is None or filters["profit_gte"] <= 0:
                where_clauses.append(f"{sanitized_profit} > 0")
            else:
                where_clauses.append(f"{sanitized_profit} >= ?")
                filter_params.append(filters["profit_gte"])

            # Enforce Data Completeness (Global Filters) to match api_deals
            where_clauses.append("\"List_at\" IS NOT NULL")
            where_clauses.append("\"List_at\" > 0")
            where_clauses.append("\"1yr_Avg\" IS NOT NULL")
            where_clauses.append("\"1yr_Avg\" NOT IN ('-', 'N/A', '', '0', '0.00', '$0.00')")
            where_clauses.append("\"1yr_Avg\" != 0")

            if filters.get("percent_down_gte") is not None and filters["percent_down_gte"] > 0:
                where_clauses.append("CAST(\"Percent_Down\" AS INTEGER) >= ?")
                filter_params.append(filters["percent_down_gte"])

            if filters.get("hide_gated") == 1:
                # Same logic as api_deals: Only apply if connected
                if is_sp_api_connected and user_id:
                     where_clauses.append("(ur.is_restricted IS NULL OR ur.is_restricted != 1)")

            if filters.get("hide_amz") == 1:
                where_clauses.append("(deals.\"AMZ\" IS NULL OR deals.\"AMZ\" != '⚠️')")

            where_sql = " WHERE " + " AND ".join(where_clauses) if where_clauses else ""

            # Add filter params to the main params list
            params.extend(filter_params)

            cursor.execute(f"SELECT COUNT(*), MAX(deals.id) FROM deals {join_clause} {where_sql}", params)
            row = cursor.fetchone()
            count = row[0] if row else 0
            max_id = row[1] if row and row[1] else 0
            return jsonify({'count': count, 'max_id': max_id})
    except sqlite3.Error as e:
        app.logger.error(f"Database error in deal_count: {e}")
        return jsonify({'error': 'Database error', 'message': str(e)}), 500

@app.route('/api/tooltip/<string:term>')
def get_tooltip_advice(term):
    if not session.get('logged_in'):
        return jsonify({'error': 'Not authenticated'}), 401

    # Try to decode if encoded, but flask routing handles basic URL encoding
    import urllib.parse
    decoded_term = urllib.parse.unquote(term)

    try:
        advice = generate_tooltip_advice(decoded_term)
        return jsonify({'tooltip': advice})
    except Exception as e:
        app.logger.error(f"Error in tooltip endpoint for term {term}: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/ava-advice/<string:asin>')
def get_ava_advice(asin):
    if not session.get('logged_in'):
        return jsonify({'error': 'Not authenticated'}), 401

    try:
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()

            cursor.execute("SELECT * FROM deals WHERE ASIN = ?", (asin,))
            row = cursor.fetchone()

            if not row:
                return jsonify({'error': 'Deal not found'}), 404

            deal_data = dict(row)
            mentor_type = request.args.get('mentor', 'cfo')
            advice = generate_ava_advice(deal_data, mentor_type=mentor_type)

            return jsonify({'advice': advice})

    except Exception as e:
        app.logger.error(f"Error in ava advice endpoint: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

@app.route('/api/mentor-chat', methods=['POST'])
def mentor_chat():
    if not session.get('logged_in'):
        return jsonify({'error': 'Not authenticated'}), 401

    try:
        data = request.json
        message = data.get('message', '')
        mentor_name = data.get('mentor', 'olyvia')

        mentor = get_mentor_config(mentor_name)

        # Load Strategy Context
        strategies_text = load_strategies()
        strategy_section = ""
        if strategies_text:
             strategy_section = f"""
        **Learned Strategies Knowledge Base:**
        {strategies_text}
        """

        # Load Intelligence Context
        intelligence_text = load_intelligence()
        intelligence_section = ""
        if intelligence_text:
             intelligence_section = f"""
        **Learned Intelligence/Concepts Knowledge Base:**
        {intelligence_text}
        """

        prompt = f"""
        You are {mentor['name']}, {mentor['role']}.

        **Your Persona:**
        *   **Intro:** "{mentor['intro']}"
        *   **Focus:** {mentor['focus']}
        *   **Tone:** {mentor['tone']}
        *   **Style:** {mentor['style_guide']}

        **Context:**
        You are chatting with a user (Tim) about online arbitrage, Amazon FBA, and business strategy.
        Use your specific persona and the knowledge bases below to answer their questions.
        Prioritize the strategies and intelligence gathered.
        *   **Constraint:** Do NOT start with an introduction or preamble.
        *   **Constraint:** Do NOT use markdown. Use HTML tags (e.g., <b>, <br>, <p>) for formatting.

        {strategy_section}

        {intelligence_section}

        **User Message:**
        {message}

        **Your Response:**
        """

        payload = {
            "messages": [
                {
                    "role": "system",
                    "content": f"You are {mentor['name']}, an expert book arbitrage assistant. Stay in character."
                },
                {
                    "role": "user",
                    "content": prompt
                }
            ],
            "model": "grok-4-fast-reasoning", # Use reasoning model
            "stream": False,
            "temperature": 0.5,
            "max_tokens": 300
        }

        result = query_xai_api(payload)

        if "error" in result:
             return jsonify({'error': result['error']}), 500

        try:
             reply = result['choices'][0]['message']['content'].strip()
             return jsonify({'reply': reply})
        except (KeyError, IndexError):
             return jsonify({'error': 'Invalid response from AI'}), 500

    except Exception as e:
        app.logger.error(f"Error in mentor chat endpoint: {e}", exc_info=True)
        return jsonify({'error': str(e)}), 500

# Ensure tables exist on module load (for WSGI environment)
create_user_restrictions_table_if_not_exists()
create_user_credentials_table_if_not_exists()
# Verify/Update Deals schema (Critical for missing columns like Drops)
create_deals_table_if_not_exists()

if __name__ == '__main__':
    app.run(debug=True)