import os



from datetime import datetime, timedelta
import time
import threading

from flask import Flask, request, jsonify, render_template
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash, check_password_hash
import requests
from bs4 import BeautifulSoup
from amazon.paapi import AmazonAPI
import re
from apscheduler.schedulers.background import BackgroundScheduler
from dotenv import load_dotenv

load_dotenv()


app = Flask(__name__)

# Configure Database
database_url = os.environ.get('DATABASE_URL', 'sqlite:///local.db')
if database_url.startswith("postgres://"):
    database_url = database_url.replace("postgres://", "postgresql://", 1)

app.config['SQLALCHEMY_DATABASE_URI'] = database_url
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_pre_ping': True,
    'pool_recycle': 300,
}

db = SQLAlchemy(app)

# Models
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    scraper_api_keys = db.Column(db.Text, nullable=True)
    amazon_paapi_access_key = db.Column(db.String(255), nullable=True)
    amazon_paapi_secret_key = db.Column(db.String(255), nullable=True)
    amazon_paapi_partner_tag = db.Column(db.String(255), nullable=True)
    email = db.Column(db.String(255), nullable=False)
    email_notifications = db.Column(db.Boolean, default=True)

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    title = db.Column(db.String(500), nullable=False)
    url = db.Column(db.Text, nullable=False)
    target_price = db.Column(db.Float, nullable=False)
    check_interval_hours = db.Column(db.Integer, default=24)
    last_checked = db.Column(db.DateTime, nullable=True)
    user = db.relationship('User', backref=db.backref('items', lazy=True))

class PriceHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    item_id = db.Column(db.Integer, db.ForeignKey('item.id'), nullable=False)
    price = db.Column(db.Float, nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    item = db.relationship('Item', backref=db.backref('history', lazy=True, cascade="all, delete-orphan"))

# Helper for Scraping
def extract_asin(url):
    match = re.search(r'/([A-Z0-9]{10})(?:[/?]|$)', url)
    if match:
        return match.group(1)
    return None

def fetch_amazon_paapi_price(url, access_key, secret_key, partner_tag):
    asin = extract_asin(url)
    if not asin:
        return None, None
        
    try:
        from amazon.paapi import AmazonAPI
        amazon = AmazonAPI(access_key, secret_key, partner_tag, 'IN')
        items = amazon.get_items(item_ids=[asin])
        if not items or len(items) == 0:
            return None, None
            
        item = items[0]
        title = item.item_info.title.display_value
        
        # Check offers
        if item.offers and item.offers.listings:
            price_amount = item.offers.listings[0].price.amount
            return title, price_amount
            
    except Exception as e:
        print(f"PA-API Error for {url}: {e}")
        
    return None, None

def fetch_amazon_price(url, scraper_api_keys, access_key=None, secret_key=None, partner_tag=None):
    # Try PA-API First
    if access_key and secret_key and partner_tag:
        title, price = fetch_amazon_paapi_price(url, access_key, secret_key, partner_tag)
        if title and price is not None:
            return title, price

    # Fallback to ScraperAPI
    if scraper_api_keys:
        keys = [k.strip() for k in scraper_api_keys.split(',') if k.strip()]
        for key in keys:
            payload = {
                'api_key': key,
                'url': url,
                'country_code': 'in', # specific to Amazon India as per prompt
                'render': 'true' # rendering JS can help fetch dynamic prices on Amazon
            }
            try:
                r = requests.get('https://api.scraperapi.com/', params=payload, timeout=30)
                if r.status_code == 200:
                    soup = BeautifulSoup(r.text, 'html.parser')
                    # Look for common Amazon price elements
                    title_el = soup.select_one('#productTitle')
                    title = title_el.text.strip() if title_el else "Unknown Product"

                    # Try finding `.a-price-whole`
                    price_el = soup.select_one('.a-price-whole')
                    if price_el:
                        price_text = price_el.text.strip().replace(',', '').replace('₹', '')
                        try:
                            price = float(price_text)
                            return title, price
                        except ValueError:
                            pass

                    # Try finding `.a-offscreen` which is common for prices now
                    price_el = soup.select_one('.apexPriceToPay .a-offscreen, .priceToPay .a-offscreen')
                    if price_el:
                        price_text = price_el.text.strip().replace(',', '').replace('₹', '')
                        try:
                            price = float(price_text)
                            return title, price
                        except ValueError:
                            pass

                    # Alternative price elements
                    price_el = soup.select_one('#priceblock_ourprice, #priceblock_dealprice, .a-color-price')
                    if price_el:
                        price_text = price_el.text.strip().replace(',', '').replace('₹', '')
                        try:
                            price = float(price_text)
                            return title, price
                        except ValueError:
                            pass

            except Exception as e:
                print(f"Error fetching {url} with key {key}: {e}")

    return None, None

# Alerts
def send_email_alert(user_email, item_title, item_url, current_price, target_price):
    email_api_url = os.environ.get('EMAIL_API_URL', 'http://russetduck.onpella.app/send-email')
    email_api_key = os.environ.get('EMAIL_API_KEY', '')
    
    smtp_server = os.environ.get('SMTP_SERVER', 'smtp.gmail.com')
    smtp_port = int(os.environ.get('SMTP_PORT', 587))
    smtp_user = os.environ.get('SMTP_USER')
    smtp_password = os.environ.get('SMTP_PASSWORD')

    subject = f"Price Drop Alert: {item_title}"
    body = f"Good news! The price of {item_title} has dropped to ₹{current_price}, which is below your target of ₹{target_price}.\n\nBuy it here: {item_url}"

    payload = {
        "to_email": user_email,
        "subject": subject,
        "body": body,
        "smtp_server": smtp_server,
        "smtp_port": smtp_port,
        "smtp_user": smtp_user,
        "smtp_password": smtp_password
    }
    
    headers = {"Content-Type": "application/json"}
    if email_api_key:
        headers["x-api-key"] = email_api_key

    try:
        response = requests.post(email_api_url, json=payload, headers=headers, timeout=10)
        if response.status_code == 200:
            print(f"Sent email alert to {user_email} for {item_title}", flush=True)
        else:
            print(f"Failed to send email. API returned {response.status_code}: {response.text}", flush=True)
    except Exception as e:
        print(f"Failed to call email API: {e}", flush=True)



# Scheduler Task
def update_prices():
    with app.app_context():
        items = Item.query.all()
        now = datetime.utcnow()
        for item in items:
            user = item.user

            # Check if it's time to run based on check_interval_hours
            if item.last_checked is not None:
                next_check_time = item.last_checked + timedelta(hours=item.check_interval_hours)
                if now < next_check_time:
                    continue # Skip this item for now

            print(f"Checking price for item {item.id}: {item.title}")
            _, current_price = fetch_amazon_price(item.url, user.scraper_api_keys, user.amazon_paapi_access_key, user.amazon_paapi_secret_key, user.amazon_paapi_partner_tag)

            if current_price is not None:
                new_history = PriceHistory(item_id=item.id, price=current_price)
                db.session.add(new_history)
                item.last_checked = now
                db.session.commit()

                # Check for alerts
                if current_price <= item.target_price:
                    if user.email_notifications:
                        send_email_alert(user.email, item.title, item.url, current_price, item.target_price)

# Start Background Scheduler
scheduler = BackgroundScheduler()
# Run every 15 minutes to allow finer granularity for custom intervals
scheduler.add_job(func=update_prices, trigger="interval", minutes=15)
scheduler.start()

# Helper for extracting user from request
def get_current_user():
    auth_header = request.headers.get('Authorization')
    if not auth_header or not auth_header.startswith('Bearer '):
        return None
    user_id = auth_header.split(' ')[1]
    return User.query.get(user_id)


# Routes
@app.route('/')
def index():
    return render_template('index.html')

@app.route('/register', methods=['POST'])
def register():
    data = request.json
    username = data.get('username')
    password = data.get('password')
    email = data.get('email')
    scraper_api_keys = data.get('scraper_api_keys')
    amazon_paapi_access_key = data.get('amazon_paapi_access_key')
    amazon_paapi_secret_key = data.get('amazon_paapi_secret_key')
    amazon_paapi_partner_tag = data.get('amazon_paapi_partner_tag')

    if not all([username, password, email]):
        return jsonify({'error': 'Missing required fields'}), 400
        
    has_scraper = bool(scraper_api_keys)
    has_paapi = all([amazon_paapi_access_key, amazon_paapi_secret_key, amazon_paapi_partner_tag])
    
    if not has_scraper and not has_paapi:
        return jsonify({'error': 'Must provide either Scraper API keys or all Amazon PA-API credentials'}), 400

    if User.query.filter_by(username=username).first():
        return jsonify({'error': 'Username already exists'}), 400

    user = User(
        username=username,
        password_hash=generate_password_hash(password),
        email=email,
        scraper_api_keys=scraper_api_keys,
        amazon_paapi_access_key=amazon_paapi_access_key,
        amazon_paapi_secret_key=amazon_paapi_secret_key,
        amazon_paapi_partner_tag=amazon_paapi_partner_tag
    )
    db.session.add(user)
    db.session.commit()

    return jsonify({
        'message': 'User created successfully',
        'user_id': user.id,
        'scraper_api_keys': user.scraper_api_keys,
        'amazon_paapi_access_key': user.amazon_paapi_access_key,
        'amazon_paapi_secret_key': user.amazon_paapi_secret_key,
        'amazon_paapi_partner_tag': user.amazon_paapi_partner_tag
    }), 201

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

    user = User.query.filter_by(username=username).first()
    if user and check_password_hash(user.password_hash, password):
        return jsonify({
            'message': 'Login successful',
            'user_id': user.id,
            'scraper_api_keys': user.scraper_api_keys,
            'amazon_paapi_access_key': user.amazon_paapi_access_key,
            'amazon_paapi_secret_key': user.amazon_paapi_secret_key,
            'amazon_paapi_partner_tag': user.amazon_paapi_partner_tag
        }), 200

    return jsonify({'error': 'Invalid credentials'}), 401

@app.route('/api/items', methods=['GET'])
def get_items():
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Unauthorized'}), 401

    items = Item.query.filter_by(user_id=user.id).all()
    result = []
    for item in items:
        history = PriceHistory.query.filter_by(item_id=item.id).order_by(PriceHistory.timestamp.asc()).all()
        history_data = [{'price': h.price, 'timestamp': h.timestamp.isoformat()} for h in history]

        current_price = history_data[-1]['price'] if history_data else None

        result.append({
            'id': item.id,
            'title': item.title,
            'url': item.url,
            'target_price': item.target_price,
            'check_interval_hours': item.check_interval_hours,
            'current_price': current_price,
            'history': history_data
        })

    return jsonify({'items': result}), 200


def send_item_added_email(user_email, item_title, url, target_price):
    email_api_url = os.environ.get('EMAIL_API_URL', 'https://buzzing-gross-bracket--9315094515a.replit.app/send-email')
    email_api_key = os.environ.get('EMAIL_API_KEY', '')
    
    smtp_server = os.environ.get('SMTP_SERVER', 'smtp.gmail.com')
    smtp_port = int(os.environ.get('SMTP_PORT', 587))
    smtp_user = os.environ.get('SMTP_USER')
    smtp_password = os.environ.get('SMTP_PASSWORD')

    subject = f"Item Added to Tracker: {item_title}"
    body = f"You have successfully added a new item to track!\n\nItem: {item_title}\nTarget Price: ₹{target_price}\nURL: {url}\n\nWe will notify you when the price drops below your target."

    payload = {
        "to_email": user_email,
        "subject": subject,
        "body": body,
        "smtp_server": smtp_server,
        "smtp_port": smtp_port,
        "smtp_user": smtp_user,
        "smtp_password": smtp_password
    }

    headers = {"Content-Type": "application/json"}
    if email_api_key:
        headers["x-api-key"] = email_api_key

    try:
        response = requests.post(email_api_url, json=payload, headers=headers, timeout=10)
        if response.status_code == 200:
            print(f"Sent added item email to {user_email}", flush=True)
        else:
            print(f"Failed to send email. API returned {response.status_code}: {response.text}", flush=True)
    except Exception as e:
        print(f"Failed to call email API: {e}", flush=True)


@app.route('/api/add', methods=['POST'])
def add_item():
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Unauthorized'}), 401

    data = request.json
    url = data.get('url')
    target_price = data.get('target_price')
    check_interval_hours = data.get('check_interval_hours', 24)
    scraper_api_keys = user.scraper_api_keys

    # Check duplicate
    existing = Item.query.filter_by(user_id=user.id, url=url).first()
    if existing:
        return jsonify({'error': 'Item is already being tracked in your dashboard.'}), 400

    if not url or not target_price:
        return jsonify({'error': 'Missing fields'}), 400

    try:
        target_price = float(target_price)
    except ValueError:
        return jsonify({'error': 'Invalid target price'}), 400

    # Fetch initial data
    title, current_price = fetch_amazon_price(url, scraper_api_keys, user.amazon_paapi_access_key, user.amazon_paapi_secret_key, user.amazon_paapi_partner_tag)
    if not title or current_price is None:
        return jsonify({'error': 'Failed to fetch product details. Check URL or ScraperAPI key.'}), 400

    item = Item(user_id=user.id, title=title, url=url, target_price=target_price, check_interval_hours=check_interval_hours, last_checked=datetime.utcnow())
    db.session.add(item)
    db.session.commit()

    history = PriceHistory(item_id=item.id, price=current_price)
    db.session.add(history)
    db.session.commit()

    # Send added item email if notifications enabled (in background)
    if user.email_notifications:
        threading.Thread(target=send_item_added_email, args=(user.email, title, url, target_price)).start()

    return jsonify({'message': 'Item added successfully', 'item_id': item.id, 'title': title, 'current_price': current_price}), 201



@app.route('/api/items/<int:item_id>', methods=['PUT'])
def update_item_target_price(item_id):
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Unauthorized'}), 401

    item = Item.query.filter_by(id=item_id, user_id=user.id).first()
    if not item:
        return jsonify({'error': 'Item not found'}), 404

    data = request.json
    target_price = data.get('target_price')
    if target_price is None:
        return jsonify({'error': 'Target price not provided'}), 400

    try:
        item.target_price = float(target_price)
        db.session.commit()
        return jsonify({'message': 'Target price updated successfully'})
    except ValueError:
        return jsonify({'error': 'Invalid target price format'}), 400

@app.route('/api/items/<int:item_id>', methods=['DELETE'])
def delete_item(item_id):
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Unauthorized'}), 401

    item = Item.query.filter_by(id=item_id, user_id=user.id).first()
    if not item:
        return jsonify({'error': 'Item not found'}), 404

    db.session.delete(item)
    db.session.commit()
    return jsonify({'message': 'Item deleted successfully'})

@app.route('/api/profile', methods=['GET'])
def get_profile():
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Unauthorized'}), 401

    return jsonify({
        'username': user.username,
        'email': user.email,
        'scraper_api_keys': user.scraper_api_keys,
        'amazon_paapi_access_key': user.amazon_paapi_access_key,
        'amazon_paapi_secret_key': user.amazon_paapi_secret_key,
        'amazon_paapi_partner_tag': user.amazon_paapi_partner_tag,
        'email_notifications': user.email_notifications
    })

@app.route('/api/profile', methods=['PUT'])
def update_profile():
    user = get_current_user()
    if not user:
        return jsonify({'error': 'Unauthorized'}), 401

    data = request.json
    if 'email' in data:
        user.email = data['email']
    if 'scraper_api_keys' in data:
        user.scraper_api_keys = data['scraper_api_keys']
    if 'amazon_paapi_access_key' in data:
        user.amazon_paapi_access_key = data['amazon_paapi_access_key']
    if 'amazon_paapi_secret_key' in data:
        user.amazon_paapi_secret_key = data['amazon_paapi_secret_key']
    if 'amazon_paapi_partner_tag' in data:
        user.amazon_paapi_partner_tag = data['amazon_paapi_partner_tag']
    if 'password' in data and data['password']:
        user.password_hash = generate_password_hash(data['password'])
    if 'email_notifications' in data:
        user.email_notifications = bool(data['email_notifications'])

    db.session.commit()
    return jsonify({'message': 'Profile updated successfully'})


if __name__ == '__main__':
    with app.app_context():
        db.create_all()
        # Ensure migration for new columns to prevent UndefinedColumn errors on remote DB
        try:
            db.session.execute(db.text('ALTER TABLE "user" ADD COLUMN email_notifications BOOLEAN DEFAULT TRUE;'))
            db.session.commit()
        except Exception:
            db.session.rollback()

        # Add PAAPI columns
        for col in ['amazon_paapi_access_key', 'amazon_paapi_secret_key', 'amazon_paapi_partner_tag']:
            try:
                db.session.execute(db.text(f'ALTER TABLE "user" ADD COLUMN {col} VARCHAR(255);'))
                db.session.commit()
            except Exception:
                db.session.rollback()

        # Drop Telegram columns if they exist
        for col in ['telegram_chat_id', 'telegram_notifications']:
            try:
                db.session.execute(db.text(f'ALTER TABLE "user" DROP COLUMN {col};'))
                db.session.commit()
            except Exception:
                db.session.rollback()

    app.run(host='0.0.0.0', port=7860)