import json
import pika
import pymysql
import os
import sys
from dotenv import load_dotenv
from pymysql.cursors import DictCursor

# Ensure we can import from the pipeline
sys.path.append(os.path.join(os.path.dirname(__file__), 'call-proccessing/stt_pipeline'))

# Load env
env_path = os.path.join(os.path.dirname(__file__), 'dashboard-backend/.env')
load_dotenv(env_path)

def trigger_last_10():
    bid = "1713"
    
    # DB Connection
    conn = pymysql.connect(
        host=os.getenv('DB_HOST', '127.0.0.1'),
        port=int(os.getenv('DB_PORT', 3306)),
        user=os.getenv('DB_USER', 'admin'),
        password=os.getenv('DB_PASSWORD', 'Syntheon@1982'),
        database=os.getenv('DB_NAME', 'voicebot_cluster'),
        cursorclass=DictCursor
    )

    try:
        with conn.cursor() as cursor:
            # 1. Identify 10 most recent unprocessed records
            # Get already seen IDs
            cursor.execute(f"SELECT call_id FROM stt_jobs WHERE bid='{bid}'")
            seen_ids = [str(r['call_id']) for r in cursor.fetchall()]
            
            raw_table = f"`{bid}_raw_calls`"
            if seen_ids:
                placeholders = ', '.join(['%s'] * len(seen_ids))
                query = f"""
                    SELECT * FROM {raw_table} 
                    WHERE `id` NOT IN ({placeholders}) 
                    AND `fileurl` IS NOT NULL 
                    AND `fileurl` != '' 
                    ORDER BY `id` DESC LIMIT 100
                """
                cursor.execute(query, tuple(seen_ids))
            else:
                query = f"""
                    SELECT * FROM {raw_table} 
                    WHERE `fileurl` IS NOT NULL 
                    AND `fileurl` != '' 
                    ORDER BY `id` DESC LIMIT 100
                """
                cursor.execute(query)
                
            new_calls = cursor.fetchall()
            
            if not new_calls:
                print("No new calls to process.")
                return

            print(f"Found {len(new_calls)} new calls to trigger.")

            # RabbitMQ setup
            rabbitmq_host = os.getenv("RABBITMQ_HOST", "localhost")
            rabbitmq_port = int(os.getenv("RABBITMQ_PORT", "5672"))
            rabbitmq_user = os.getenv("RABBITMQ_USER", "guest")
            rabbitmq_password = os.getenv("RABBITMQ_PASSWORD", "guest")
            rabbitmq_queue = os.getenv("RABBITMQ_QUEUE", "stt_jobs")

            creds = pika.PlainCredentials(rabbitmq_user, rabbitmq_password)
            parameters = pika.ConnectionParameters(host=rabbitmq_host, port=rabbitmq_port, credentials=creds)
            connection = pika.BlockingConnection(parameters)
            channel = connection.channel()
            channel.queue_declare(queue=rabbitmq_queue, durable=True)

            import subprocess

            valid_count = 0
            for call in new_calls:
                if valid_count >= 10:
                    break
                    
                call_id = str(call['callid'])
                recording_url = call['fileurl']
                
                # Validate URL
                try:
                    res = subprocess.run(['curl', '-I', '-s', recording_url], capture_output=True, text=True)
                    if '200 OK' not in res.stdout:
                        print(f"Skipping call {call_id} - URL invalid (404/Error)")
                        continue
                except Exception as e:
                    print(f"Skipping call {call_id} - URL check failed: {e}")
                    continue

                metadata = {k: str(v) for k, v in call.items() if k not in ("callid", "fileurl") and v is not None}
                
                # 2. Insert into stt_jobs
                cursor.execute(
                    "INSERT INTO stt_jobs (bid, call_id, recording_url, metadata, status, created_at) "
                    "VALUES (%s, %s, %s, %s, %s, NOW())",
                    (bid, call_id, recording_url, json.dumps(metadata), 'pending')
                )
                job_id = cursor.lastrowid
                
                # 3. Publish to RabbitMQ
                job_payload = {
                    "job_id": job_id,
                    "bid": bid,
                    "call_id": call_id,
                    "recording_url": recording_url,
                    "metadata": metadata
                }
                
                channel.basic_publish(
                    exchange='',
                    routing_key=rabbitmq_queue,
                    body=json.dumps(job_payload),
                    properties=pika.BasicProperties(delivery_mode=2)
                )
                print(f"Successfully triggered job {job_id} for call {call_id}")
                valid_count += 1

            conn.commit()
            connection.close()
            print("\nAll jobs triggered successfully.")

    except Exception as e:
        print(f"Error: {e}")
        conn.rollback()
    finally:
        conn.close()

if __name__ == "__main__":
    trigger_last_10()