from flask import Blueprint, request, jsonify
from ..db import get_db_connection, release_db_connection
from ..middleware import token_required
import random
from datetime import datetime, timedelta

seo_blueprint = Blueprint('seo', __name__)

@seo_blueprint.route('/seo/keywords', methods=['GET'])
@token_required
def list_keywords(current_user_id):
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT k.id, k.keyword, k.article, k.platform, 
               (SELECT position FROM seo_history h WHERE h.keyword_id = k.id ORDER BY checked_at DESC LIMIT 1) as last_pos
        FROM seo_keywords k
        WHERE k.user_id = %s
    """, (current_user_id,))
    rows = cur.fetchall()
    release_db_connection(conn)
    
    return jsonify([{
        "id": r[0],
        "keyword": r[1],
        "article": r[2],
        "platform": r[3],
        "last_position": r[4]
    } for r in rows])

@seo_blueprint.route('/seo/keywords', methods=['POST'])
@token_required
def add_keyword(current_user_id):
    data = request.json
    keyword = data.get('keyword')
    article = data.get('article')
    platform = data.get('platform', 'ozon')
    
    if not keyword or not article:
        return jsonify({"error": "Keyword and article are required"}), 400
        
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO seo_keywords (user_id, keyword, article, platform) VALUES (%s, %s, %s, %s) RETURNING id",
        (current_user_id, keyword, article, platform)
    )
    kid = cur.fetchone()[0]
    
    # Generate some mock history immediately
    for i in range(7):
        date = datetime.now() - timedelta(days=i)
        pos = random.randint(1, 150)
        cur.execute("INSERT INTO seo_history (keyword_id, position, checked_at) VALUES (%s, %s, %s)", (kid, pos, date))
        
    conn.commit()
    release_db_connection(conn)
    return jsonify({"id": kid}), 201

@seo_blueprint.route('/seo/history/<int:keyword_id>', methods=['GET'])
@token_required
def get_seo_history(current_user_id, keyword_id):
    conn = get_db_connection()
    cur = conn.cursor()
    # Check ownership
    cur.execute("SELECT id FROM seo_keywords WHERE id = %s AND user_id = %s", (keyword_id, current_user_id))
    if not cur.fetchone():
        release_db_connection(conn)
        return jsonify({"error": "Not found"}), 404
        
    cur.execute("SELECT position, checked_at FROM seo_history WHERE keyword_id = %s ORDER BY checked_at ASC", (keyword_id,))
    rows = cur.fetchall()
    release_db_connection(conn)
    
    return jsonify([{
        "position": r[0],
        "date": r[1].strftime('%d.%m')
    } for r in rows])