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,
    create_confirmed_buys_table_if_not_exists,
    create_confirmed_buy_units_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.pricing_version import CURRENT_PRICING_PREDICATE
from keepa_deals.business_calculations import (
    calculate_all_in_cost,
    calculate_profit_and_margin,
    load_settings as business_load_settings,
)
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, STRATEGIC_CORRECTIONS
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."


def _parse_currency_to_float(value):
    """
    Parses a value that may be a number or a currency-like string
    ('$278.94', '278.94', '15.0%', 278.94) into a float.
    Returns None if value is None, empty, or non-numeric.
    Used by /api/tracking/potential endpoints because the `deals` table
    stores some numeric values (List_at, etc.) as formatted strings.
    """
    if value is None:
        return None
    if isinstance(value, (int, float)):
        return float(value)
    if isinstance(value, str):
        cleaned = value.strip().replace('$', '').replace(',', '').replace('%', '')
        if not cleaned:
            return None
        try:
            return float(cleaned)
        except ValueError:
            return None
    return None


# 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 get_db_connection(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:
        settings = business_load_settings()
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            query = """
                SELECT i.*, 
                       d.List_at, d.FBA_PickandPack_Fee, d.Referral_Fee_Percent, d.Shipping_Included
                FROM inventory_ledger i
                LEFT JOIN deals d ON i.asin = d.ASIN
                WHERE i.status = 'POTENTIAL' 
                ORDER BY i.created_at DESC
            """
            cursor.execute(query)
            rows = cursor.fetchall()
            
            data = []
            for row in rows:
                item = dict(row)
                
                profit_val = '-'
                margin_val = '-'
                roi_val = '-'
                
                # Parse all numeric inputs (deals table stores some as currency strings like "$278.94")
                buy_cost = _parse_currency_to_float(item.get('buy_cost'))
                list_at = _parse_currency_to_float(item.get('List_at'))
                fba_fee = _parse_currency_to_float(item.get('FBA_PickandPack_Fee'))
                ref_fee_pct = _parse_currency_to_float(item.get('Referral_Fee_Percent'))
                
                if list_at is None:
                    list_at = _parse_currency_to_float(item.get('snapshot_list_at'))
                if fba_fee is None:
                    fba_fee = _parse_currency_to_float(item.get('snapshot_fba_fee'))
                if ref_fee_pct is None:
                    ref_fee_pct = _parse_currency_to_float(item.get('snapshot_referral_pct'))

                shipping_included_val = item.get('Shipping_Included')
                if shipping_included_val is None:
                    shipping_included_val = item.get('snapshot_shipping_included')
                
                # snapshot_shipping_included is stored as 1.0 or 0.0 (REAL), deal row might be string
                shipping_included = False
                if shipping_included_val is not None:
                    if isinstance(shipping_included_val, (int, float)):
                        shipping_included = bool(shipping_included_val)
                    else:
                        shipping_included = str(shipping_included_val).lower() in ('true', 'yes', '1')

                # For display-time fallback, if deals row is gone, use snapshot settings for tax/shipping/prep
                # if deals row exists, we use current settings
                current_settings = settings
                if item.get('List_at') is None:
                     # Create a temporary settings dict using snapshot values
                     current_settings = settings.copy()
                     if item.get('snapshot_estimated_tax') is not None:
                         current_settings['estimated_tax_per_book'] = item.get('snapshot_estimated_tax')
                     if item.get('snapshot_estimated_shipping') is not None:
                         current_settings['estimated_shipping_per_book'] = item.get('snapshot_estimated_shipping')
                     if item.get('snapshot_prep_fee') is not None:
                         current_settings['prep_fee_per_book'] = item.get('snapshot_prep_fee')
                
                if buy_cost is not None and list_at is not None and fba_fee is not None and ref_fee_pct is not None:
                    try:
                        all_in_cost = calculate_all_in_cost(buy_cost, current_settings, shipping_included)
                        if isinstance(all_in_cost, (int, float)) and all_in_cost > 0:
                            referral_fee_amount = list_at * (ref_fee_pct / 100.0)
                            total_amz_fees = referral_fee_amount + fba_fee
                            
                            pm = calculate_profit_and_margin(list_at, all_in_cost, total_amz_fees)
                            profit_parsed = _parse_currency_to_float(pm.get('profit'))
                            margin_parsed = _parse_currency_to_float(pm.get('margin'))
                            
                            if profit_parsed is not None:
                                profit_val = profit_parsed
                            if margin_parsed is not None:
                                margin_val = margin_parsed
                            if profit_parsed is not None and all_in_cost > 0:
                                roi_val = (profit_parsed / all_in_cost) * 100
                    except Exception as calc_err:
                        app.logger.error(f"Error calculating potential buy metrics for ASIN {item.get('asin')}: {calc_err}")
                
                item['profit'] = profit_val
                item['margin'] = margin_val
                item['roi'] = roi_val
                
                # Remove extra deals columns from final output just in case
                for key in ['List_at', 'FBA_PickandPack_Fee', 'Referral_Fee_Percent', 'Shipping_Included']:
                    item.pop(key, None)
                    
                data.append(item)
                
            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/confirmed', methods=['GET'])
def get_confirmed_buys():
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    try:
        settings = business_load_settings()
        default_markup = settings.get('default_markup', 10)
        
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            query = '''
                SELECT c.*, COALESCE(d.Title, c.title) as Title, d.List_at as deals_list_at, cbu.sku
                FROM confirmed_buys c
                LEFT JOIN deals d ON c.asin = d.ASIN
                LEFT JOIN (
                    SELECT confirmed_buy_id, MIN(sku) as sku
                    FROM confirmed_buy_units
                    GROUP BY confirmed_buy_id
                ) cbu ON c.id = cbu.confirmed_buy_id
                ORDER BY c.id DESC
            '''
            cursor.execute(query)
            rows = cursor.fetchall()
            
            data = []
            for r in rows:
                row_dict = dict(r)
                
                buy_cost = float(row_dict.get('buy_cost', 0.0) or 0.0)
                prep_fee = float(row_dict.get('prep_fee_at_purchase', 0.0) or 0.0)
                
                minimum_list_price = (buy_cost + prep_fee) * (1.0 + (default_markup / 100.0))
                
                row_dict['minimum_list_price'] = minimum_list_price
                
                # Derive recommended_list_price: prioritize deals.List_at, fallback to snapshot_list_at
                recommended_list_price = None
                if row_dict.get('deals_list_at') is not None:
                    recommended_list_price = _parse_currency_to_float(row_dict['deals_list_at'])
                elif row_dict.get('snapshot_list_at') is not None:
                    recommended_list_price = _parse_currency_to_float(row_dict['snapshot_list_at'])
                
                row_dict['recommended_list_price'] = recommended_list_price
                
                # remove the join column to avoid clutter
                if 'deals_list_at' in row_dict:
                    del row_dict['deals_list_at']
                    
                data.append(row_dict)
                
            return jsonify({'data': data})
            
    except Exception as e:
        app.logger.error(f"Error fetching confirmed buys: {e}")
        return jsonify({'error': str(e)}), 500



@app.route('/api/tracking/confirmed/<int:item_id>/condition', methods=['PATCH'])
def update_confirmed_condition(item_id):
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    condition = data.get('condition')
    
    if not condition or str(condition) not in ['1', '2', '3', '4', '5', '6']:
        return jsonify({'error': 'Invalid condition value. Must be between 1 and 6.'}), 400
        
    try:
        settings = business_load_settings()
        default_markup = settings.get('default_markup', 10)
        
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("UPDATE confirmed_buys SET condition = ? WHERE id = ?", (str(condition), item_id))
            if cursor.rowcount == 0:
                return jsonify({'error': 'Item not found'}), 404
                
            cursor.execute('''
                SELECT c.*, d.Title as Title
                FROM confirmed_buys c
                LEFT JOIN deals d ON c.asin = d.ASIN
                WHERE c.id = ?
            ''', (item_id,))
            
            r = cursor.fetchone()
            row_dict = dict(r)
            
            buy_cost = float(row_dict.get('buy_cost', 0.0) or 0.0)
            prep_fee = float(row_dict.get('prep_fee_at_purchase', 0.0) or 0.0)
            minimum_list_price = (buy_cost + prep_fee) * (1.0 + (default_markup / 100.0))
            row_dict['minimum_list_price'] = minimum_list_price
            
            conn.commit()
            
            return jsonify({'status': 'success', 'data': row_dict})
            
    except Exception as e:
        app.logger.error(f"Error updating confirmed buy condition: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/tracking/confirmed/<int:item_id>/list-price', methods=['PATCH'])
def update_confirmed_list_price(item_id):
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    actual_list_price = data.get('actual_list_price')
    
    if actual_list_price is not None:
        try:
            actual_list_price = float(actual_list_price)
        except ValueError:
            return jsonify({'error': 'Invalid actual_list_price value. Must be a float or null.'}), 400
            
    try:
        settings = business_load_settings()
        default_markup = settings.get('default_markup', 10)
        
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("UPDATE confirmed_buys SET actual_list_price = ? WHERE id = ?", (actual_list_price, item_id))
            if cursor.rowcount == 0:
                return jsonify({'error': 'Item not found'}), 404
                
            cursor.execute('''
                SELECT c.*, d.Title as Title
                FROM confirmed_buys c
                LEFT JOIN deals d ON c.asin = d.ASIN
                WHERE c.id = ?
            ''', (item_id,))
            
            r = cursor.fetchone()
            row_dict = dict(r)
            
            buy_cost = float(row_dict.get('buy_cost', 0.0) or 0.0)
            prep_fee = float(row_dict.get('prep_fee_at_purchase', 0.0) or 0.0)
            minimum_list_price = (buy_cost + prep_fee) * (1.0 + (default_markup / 100.0))
            row_dict['minimum_list_price'] = minimum_list_price
            
            conn.commit()
            
            return jsonify({'status': 'success', 'data': row_dict})
            
    except Exception as e:
        app.logger.error(f"Error updating confirmed buy list price: {e}")
        return jsonify({'error': str(e)}), 500



@app.route('/api/tracking/confirmed/<int:item_id>/buyer-order-id', methods=['PATCH'])
def update_confirmed_buyer_order_id(item_id):
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    buyer_order_id = data.get('buyer_order_id')
    
    if buyer_order_id is not None:
        buyer_order_id = str(buyer_order_id).strip()
        if not buyer_order_id:
            buyer_order_id = None
            
    try:
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("UPDATE confirmed_buys SET buyer_order_id = ? WHERE id = ?", (buyer_order_id, item_id))
            if cursor.rowcount == 0:
                return jsonify({'error': 'Item not found'}), 404
                
            cursor.execute('''
                SELECT c.*, d.Title as Title
                FROM confirmed_buys c
                LEFT JOIN deals d ON c.asin = d.ASIN
                WHERE c.id = ?
            ''', (item_id,))
            
            r = cursor.fetchone()
            row_dict = dict(r)
            
            conn.commit()
            
            return jsonify({'status': 'success', 'data': row_dict})
            
    except Exception as e:
        app.logger.error(f"Error updating confirmed buy buyer_order_id: {e}")
        return jsonify({'error': str(e)}), 500


@app.route('/api/tracking/confirmed/<int:item_id>/buy-cost', methods=['PATCH'])
def update_confirmed_buy_cost(item_id):
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    buy_cost = data.get('buy_cost')
    
    if buy_cost is None:
        return jsonify({'error': 'buy_cost is required'}), 400
        
    try:
        buy_cost = float(buy_cost)
        if buy_cost <= 0:
            return jsonify({'error': 'buy_cost must be strictly positive'}), 400
    except ValueError:
        return jsonify({'error': 'Invalid buy_cost value. Must be a number.'}), 400
            
    try:
        settings = business_load_settings()
        default_markup = settings.get('default_markup', 10)
        
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("UPDATE confirmed_buys SET buy_cost = ? WHERE id = ?", (buy_cost, item_id))
            if cursor.rowcount == 0:
                return jsonify({'error': 'Item not found'}), 404
                
            cursor.execute('''
                SELECT c.*, d.Title as Title
                FROM confirmed_buys c
                LEFT JOIN deals d ON c.asin = d.ASIN
                WHERE c.id = ?
            ''', (item_id,))
            
            r = cursor.fetchone()
            row_dict = dict(r)
            
            updated_buy_cost = float(row_dict.get('buy_cost', 0.0) or 0.0)
            prep_fee = float(row_dict.get('prep_fee_at_purchase', 0.0) or 0.0)
            minimum_list_price = (updated_buy_cost + prep_fee) * (1.0 + (default_markup / 100.0))
            row_dict['minimum_list_price'] = minimum_list_price
            
            conn.commit()
            
            return jsonify({'status': 'success', 'data': row_dict})
            
    except Exception as e:
        app.logger.error(f"Error updating confirmed buy cost: {e}")
        return jsonify({'error': str(e)}), 500



@app.route('/api/tracking/confirmed/<int:item_id>/sku', methods=['PATCH'])
def update_confirmed_sku(item_id):
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    sku = data.get('sku')
    
    if sku is not None:
        sku = str(sku).strip()
        if not sku:
            sku = None
            
    try:
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            # Check if a child row exists
            cursor.execute("SELECT id FROM confirmed_buy_units WHERE confirmed_buy_id = ?", (item_id,))
            child_row = cursor.fetchone()
            
            try:
                if child_row:
                    cursor.execute("UPDATE confirmed_buy_units SET sku = ? WHERE confirmed_buy_id = ?", (sku, item_id))
                elif sku is not None:
                    cursor.execute("INSERT INTO confirmed_buy_units (confirmed_buy_id, sku) VALUES (?, ?)", (item_id, sku))
            except sqlite3.IntegrityError:
                return jsonify({'error': 'SKU is already in use by another unit.'}), 409
                
            cursor.execute('''
                SELECT c.*, d.Title as Title, cbu.sku
                FROM confirmed_buys c
                LEFT JOIN deals d ON c.asin = d.ASIN
                LEFT JOIN confirmed_buy_units cbu ON c.id = cbu.confirmed_buy_id
                WHERE c.id = ?
            ''', (item_id,))
            
            r = cursor.fetchone()
            if not r:
                return jsonify({'error': 'Item not found'}), 404
                
            row_dict = dict(r)
            conn.commit()
            
            return jsonify({'status': 'success', 'data': row_dict})
            
    except Exception as e:
        app.logger.error(f"Error updating confirmed buy sku: {e}")
        return jsonify({'error': str(e)}), 500


@app.route('/api/tracking/confirmed/<int:item_id>/quantity', methods=['PATCH'])
def update_confirmed_quantity(item_id):
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    quantity_purchased = data.get('quantity_purchased')
    
    if quantity_purchased is None:
        return jsonify({'error': 'quantity_purchased is required'}), 400
        
    try:
        quantity_purchased = int(quantity_purchased)
        if quantity_purchased <= 0:
            return jsonify({'error': 'quantity_purchased must be greater than 0'}), 400
    except ValueError:
        return jsonify({'error': 'Invalid quantity_purchased value. Must be an integer.'}), 400
            
    try:
        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("UPDATE confirmed_buys SET quantity_purchased = ? WHERE id = ?", (quantity_purchased, item_id))
            if cursor.rowcount == 0:
                return jsonify({'error': 'Item not found'}), 404
                
            cursor.execute('''
                SELECT c.*, d.Title as Title
                FROM confirmed_buys c
                LEFT JOIN deals d ON c.asin = d.ASIN
                WHERE c.id = ?
            ''', (item_id,))
            
            r = cursor.fetchone()
            row_dict = dict(r)
            
            conn.commit()
            
            return jsonify({'status': 'success', 'data': row_dict})
            
    except Exception as e:
        app.logger.error(f"Error updating confirmed buy quantity: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/tracking/potential/<int:item_id>', methods=['PATCH'])
def update_potential_buy_cost(item_id):
    if not session.get('logged_in'):
        return jsonify({'error': 'Unauthorized'}), 401
    try:
        data = request.json
        new_cost = data.get('buy_cost')

        if new_cost is None:
            return jsonify({'error': 'buy_cost is required'}), 400

        try:
            new_cost = float(new_cost)
            if new_cost <= 0:
                return jsonify({'error': 'buy_cost must be positive'}), 400
        except ValueError:
            return jsonify({'error': 'buy_cost must be a number'}), 400

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

            # Check if item exists and is POTENTIAL
            cursor.execute("SELECT id FROM inventory_ledger WHERE id = ? AND status = 'POTENTIAL'", (item_id,))
            if not cursor.fetchone():
                return jsonify({'error': 'Item not found or not in potential state'}), 404

            # Update cost and confirmation flag
            cursor.execute("""
                UPDATE inventory_ledger
                SET buy_cost = ?, buy_cost_confirmed = 1
                WHERE id = ?
            """, (new_cost, item_id))
            conn.commit()

            # Now fetch the updated item to return full recalculated values
            settings = business_load_settings()

            query = """
                SELECT i.*,
                       d.List_at, d.FBA_PickandPack_Fee, d.Referral_Fee_Percent, d.Shipping_Included
                FROM inventory_ledger i
                LEFT JOIN deals d ON i.asin = d.ASIN
                WHERE i.id = ?
            """
            cursor.execute(query, (item_id,))
            row = cursor.fetchone()

            if not row:
                 return jsonify({'error': 'Error fetching updated item'}), 500

            item = dict(row)

            profit_val = '-'
            margin_val = '-'
            roi_val = '-'

            # Parse all numeric inputs (deals table stores some as currency strings like "$278.94")
            buy_cost = _parse_currency_to_float(item.get('buy_cost'))
            list_at = _parse_currency_to_float(item.get('List_at'))
            fba_fee = _parse_currency_to_float(item.get('FBA_PickandPack_Fee'))
            ref_fee_pct = _parse_currency_to_float(item.get('Referral_Fee_Percent'))
            
            if list_at is None:
                list_at = _parse_currency_to_float(item.get('snapshot_list_at'))
            if fba_fee is None:
                fba_fee = _parse_currency_to_float(item.get('snapshot_fba_fee'))
            if ref_fee_pct is None:
                ref_fee_pct = _parse_currency_to_float(item.get('snapshot_referral_pct'))

            shipping_included_val = item.get('Shipping_Included')
            if shipping_included_val is None:
                shipping_included_val = item.get('snapshot_shipping_included')
            
            shipping_included = False
            if shipping_included_val is not None:
                if isinstance(shipping_included_val, (int, float)):
                    shipping_included = bool(shipping_included_val)
                else:
                    shipping_included = str(shipping_included_val).lower() in ('true', 'yes', '1')

            # We DO NOT use fallback settings here. This is an active cost update,
            # so we always recalculate using the fresh settings loaded at the top.
            # The spec explicitly states "that recalc reads fresh Settings values".
            current_settings = settings

            if buy_cost is not None and list_at is not None and fba_fee is not None and ref_fee_pct is not None:
                # TODO(P3 Refactor): Extract this calculation block into a shared function within
                # keepa_deals/business_calculations.py to avoid duplicating logic from
                # `_process_single_deal` in `keepa_deals/processing.py`.
                try:
                    all_in_cost = calculate_all_in_cost(buy_cost, current_settings, shipping_included)
                    if isinstance(all_in_cost, (int, float)) and all_in_cost > 0:
                        referral_fee_amount = list_at * (ref_fee_pct / 100.0)
                        total_amz_fees = referral_fee_amount + fba_fee

                        pm = calculate_profit_and_margin(list_at, all_in_cost, total_amz_fees)
                        profit_parsed = _parse_currency_to_float(pm.get('profit'))
                        margin_parsed = _parse_currency_to_float(pm.get('margin'))

                        if profit_parsed is not None:
                            profit_val = profit_parsed
                        if margin_parsed is not None:
                            margin_val = margin_parsed
                        if profit_parsed is not None and all_in_cost > 0:
                            roi_val = (profit_parsed / all_in_cost) * 100
                except Exception as calc_err:
                    app.logger.error(f"Error calculating potential buy metrics for ASIN {item.get('asin')}: {calc_err}")

            item['profit'] = profit_val
            item['margin'] = margin_val
            item['roi'] = roi_val

            # Remove extra deals columns from final output just in case
            for key in ['List_at', 'FBA_PickandPack_Fee', 'Referral_Fee_Percent', 'Shipping_Included']:
                item.pop(key, None)

            return jsonify({'success': True, 'data': item})

    except Exception as e:
        app.logger.error(f"Error updating potential buy cost: {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 get_db_connection(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 get_db_connection(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, 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

        settings = business_load_settings()

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

            # 1. Fetch values dynamically from the deals database
            cursor.execute("""
                SELECT List_at, FBA_PickandPack_Fee, Referral_Fee_Percent, Shipping_Included
                FROM deals
                WHERE ASIN = ?
            """, (asin,))
            deal_row = cursor.fetchone()

            snapshot_list_at = None
            snapshot_fba_fee = None
            snapshot_referral_pct = None
            snapshot_shipping_included = None

            if deal_row:
                try:
                    snapshot_list_at = _parse_currency_to_float(deal_row['List_at'])
                    snapshot_fba_fee = _parse_currency_to_float(deal_row['FBA_PickandPack_Fee'])
                    snapshot_referral_pct = _parse_currency_to_float(deal_row['Referral_Fee_Percent'])
                    si_raw = str(deal_row['Shipping_Included']).lower() if deal_row['Shipping_Included'] is not None else 'false'
                    snapshot_shipping_included = 1 if si_raw in ("true", "yes", "1") else 0
                except Exception as parse_e:
                    app.logger.warning(f"Error parsing deal snapshot values for ASIN {asin}: {parse_e}")

            # 2. Capture settings exactly as they exist at flag time
            snapshot_estimated_tax = settings.get('estimated_tax_per_book')
            snapshot_estimated_shipping = settings.get('estimated_shipping_per_book')
            snapshot_prep_fee = settings.get('prep_fee_per_book')

            cursor.execute("""
                INSERT INTO inventory_ledger (
                    asin, title, buy_cost, status, source,
                    snapshot_list_at, snapshot_fba_fee, snapshot_referral_pct, snapshot_shipping_included,
                    snapshot_estimated_tax, snapshot_estimated_shipping, snapshot_prep_fee
                )
                VALUES (?, ?, ?, 'POTENTIAL', 'Dashboard', ?, ?, ?, ?, ?, ?, ?)
            """, (
                asin, title, price,
                snapshot_list_at, snapshot_fba_fee, snapshot_referral_pct, snapshot_shipping_included,
                snapshot_estimated_tax, snapshot_estimated_shipping, snapshot_prep_fee
            ))
            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')
        buyer_order_id = data.get('buyer_order_id')
        condition = data.get('condition')

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

        settings = business_load_settings()
        prep_fee_at_purchase = float(settings.get('prep_fee_per_book', 0.0))

        with get_db_connection(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            # 1. Fetch the row from inventory_ledger to get snapshots and other fields
            cursor.execute("SELECT * FROM inventory_ledger WHERE id = ?", (ledger_id,))
            row = cursor.fetchone()
            if not row:
                return jsonify({'error': 'Potential buy not found'}), 404

            asin = row['asin']

            conn.execute("BEGIN TRANSACTION")

            try:
                # 2. Insert into confirmed_buys
                # Convert snapshot_shipping_included to int (0 or 1) rather than float
                si_val = row['snapshot_shipping_included']
                si_int = int(si_val) if si_val is not None else None

                cursor.execute('''
                    INSERT INTO confirmed_buys (
                        asin, title, condition, buy_cost, purchase_date, quantity_purchased,
                        prep_fee_at_purchase, buyer_order_id, source_deal_id,
                        snapshot_list_at, snapshot_fba_fee, snapshot_referral_pct, snapshot_shipping_included,
                        snapshot_estimated_tax, snapshot_estimated_shipping, snapshot_prep_fee
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    asin, row['title'], condition, buy_cost, purchase_date, qty,
                    prep_fee_at_purchase, buyer_order_id, None,
                    row['snapshot_list_at'], row['snapshot_fba_fee'], row['snapshot_referral_pct'], si_int,
                    row['snapshot_estimated_tax'], row['snapshot_estimated_shipping'], row['snapshot_prep_fee']
                ))
                
                confirmed_buy_id = cursor.lastrowid
                
                # 3. Insert into confirmed_buy_units
                if sku:
                    cursor.execute('''
                        INSERT INTO confirmed_buy_units (confirmed_buy_id, sku) VALUES (?, ?)
                    ''', (confirmed_buy_id, sku))

                # 4. Delete from inventory_ledger
                cursor.execute("DELETE FROM inventory_ledger WHERE id = ?", (ledger_id,))

                conn.execute("COMMIT")
            except Exception as inner_e:
                conn.execute("ROLLBACK")
                raise inner_e

        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 get_db_connection(DB_PATH) as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM inventory_ledger 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 get_db_connection(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/prime_picks/refresh', methods=['POST'])
def refresh_prime_picks():
    if not session.get('logged_in'):
        return jsonify({'status': 'error', 'message': 'Not logged in'}), 401

    try:
        from keepa_deals.prime_picks_task import generate_prime_picks
        generate_prime_picks.delay()
        return jsonify({'status': 'success', 'message': 'Prime Picks refresh started in the background.'})
    except Exception as e:
        app.logger.error(f"Failed to start Prime Picks task: {e}")
        return jsonify({'status': 'error', 'message': str(e)}), 500

@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 = get_db_connection(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 = {
        "agents_choice": request.args.get('agents_choice', type=str) == 'true',
        "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(REPLACE(\"Deal_Trust\", '%', '') AS REAL) >= ?")
        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)"
    sanitized_cost = "CAST(REPLACE(REPLACE(\"All_in_Cost\", '$', ''), ',', '') AS REAL)"
    if filters.get("agents_choice"):
        # The Smart Floor
        where_clauses.append(f"{sanitized_profit} >= 10")
        where_clauses.append(f"({sanitized_cost} > 0 AND (({sanitized_profit} * 1.0 / {sanitized_cost}) * 100) >= 15)")
        where_clauses.append("CAST(REPLACE(\"Deal_Trust\", '%', '') AS REAL) >= 40")

        sanitized_list_at = "CAST(REPLACE(REPLACE(\"List_at\", '$', ''), ',', '') AS REAL)"
        where_clauses.append(f"{sanitized_list_at} <= 1500")

        # Enforce Data Completeness (Global Filters) - Keep them for Smart Floor
        where_clauses.append("\"List_at\" IS NOT NULL")
        where_clauses.append(f"{sanitized_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")

        # Never SHOW a pick whose prices predate the current pricing logic, even if
        # one is sitting in the cache. prime_picks is a materialized selection made
        # up to four hours ago, and generate_prime_picks evicts stale entries only
        # when it runs; a PRICING_LOGIC_VERSION bump re-stales every row in the table
        # the moment it deploys (AGENTS.md 7.13), and the outer exception handler in
        # that task can also return with the cache untouched. This clause is what
        # makes the invariant true at the moment of display rather than at the moment
        # of the last successful run. Same shared predicate, applied to the join's
        # deals side; it contains no 'd.' for the alias rewrite below to mangle.
        #
        # Agent's Choice only. The main grid is deliberately NOT filtered on pricing
        # version - it would empty out while a repair sweep is in flight.
        where_clauses.append(CURRENT_PRICING_PREDICATE)
    else:
        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])

        # Agent's Choice carries its own AI ranking, stored as prime_picks.rank and
        # exposed to the frontend as the pseudo-column 'Agent_Rank'. 'id' is treated
        # the same way because it is the sort default and is NOT a clickable dashboard
        # column, so under Agent's Choice it can only mean "no column was chosen".
        # This is a sibling flag, not a branch in the sort_clause chain below: it never
        # assigns sort_clause, so data_query (the unfiltered path) is unaffected and
        # can never reference the pp alias.
        agent_rank_sort = bool(filters.get("agents_choice")) and sort_by in ('Agent_Rank', 'id')

        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}")

        prime_picks_generated_at = None

        if filters.get("agents_choice"):
            # Fetch cached Prime Picks from background task
            # Apply all existing UI filters via JOIN
            select_clause = select_clause.replace("deals.*", "deals.*, pp.rank, pp.score")
            from_clause = f"FROM prime_picks pp INNER JOIN {TABLE_NAME} AS deals ON pp.asin = deals.\"ASIN\""

            if is_sp_api_connected and user_id:
                from_clause += f" LEFT JOIN {RESTRICTIONS_TABLE} AS ur ON deals.\"ASIN\" = ur.asin AND ur.user_id = ?"

            # Replace 'deals.' in where_clauses if any was prepended
            where_sql = " WHERE " + " AND ".join(final_where_clauses) if final_where_clauses else ""

            # Honour the user's column sort. This branch previously hardcoded
            # 'ORDER BY pp.rank ASC', silently discarding the sort/order params that
            # the dashboard sends on every request. pp.rank remains the default
            # (agent_rank_sort) and the tie-break for every other sort.
            order_by_sql = "pp.rank ASC" if agent_rank_sort else f"{sort_clause} {order}, pp.rank ASC"

            agents_choice_query = f"SELECT {select_clause} {from_clause}{where_sql} ORDER BY {order_by_sql}"

            # The query_params have limit/offset appended at the end. We need to remove them for this fetch
            agents_choice_params = query_params[:-2]

            app.logger.debug(f"Executing Cached Prime Picks Query: {agents_choice_query} | Params: {agents_choice_params}")
            deal_rows = cursor.execute(agents_choice_query, agents_choice_params).fetchall()
            deals_list = [dict(row) for row in deal_rows]

            # Get the generated_at timestamp for the UI
            if deals_list:
                # The timestamp is stored in the DB as string, we can grab it from any row or query directly
                generated_at_row = cursor.execute("SELECT MAX(generated_at) FROM prime_picks").fetchone()
                if generated_at_row and generated_at_row[0]:
                    prime_picks_generated_at = generated_at_row[0]

            # Override pagination for Agent's Choice since we return a specific filtered set
            total_records = len(deals_list)
            total_pages = 1
            page = 1

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

        # Get the prime picks generated_at even if we're not currently filtering by it
        # so the UI can check if new ones arrived
        if not prime_picks_generated_at:
            generated_at_row = cursor.execute("SELECT MAX(generated_at) FROM prime_picks").fetchone()
            if generated_at_row and generated_at_row[0]:
                prime_picks_generated_at = generated_at_row[0]

        # --- 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,
            "prime_picks_generated_at": prime_picks_generated_at
        },
        "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
from keepa_deals.db_utils import get_db_connection

@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 get_db_connection(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(REPLACE(\"Deal_Trust\", '%', '') AS REAL) >= ?")
                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 get_db_connection(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}

        {STRATEGIC_CORRECTIONS}

        **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):
             app.logger.exception(f"Error parsing AI response: {result}")
             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

_db_initialized = False

@app.before_request
def initialize_database_tables():
    global _db_initialized
    if not _db_initialized:
        app.logger.info("First request received. Initializing database tables...")
        try:
            create_user_restrictions_table_if_not_exists()
            create_user_credentials_table_if_not_exists()
            create_deals_table_if_not_exists()
            create_confirmed_buys_table_if_not_exists()
            create_confirmed_buy_units_table_if_not_exists()
            _db_initialized = True
            app.logger.info("Database tables initialized successfully.")
        except Exception as e:
            app.logger.error(f"Error during database initialization: {e}", exc_info=True)

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