import pymysql
import os
from dotenv import load_dotenv
from pymysql.cursors import DictCursor
import subprocess

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

def find_valid_calls():
    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:
            bid = '1713'
            table = f"`{bid}_raw_calls`"
            
            print(f"Searching for valid calls in {table}...")
            
            # Fetch last 100 calls
            cursor.execute(f"SELECT id, callid, fileurl FROM {table} ORDER BY id DESC LIMIT 100")
            rows = cursor.fetchall()
            
            valid_calls = []
            for row in rows:
                url = row['fileurl']
                if not url:
                    continue
                
                # Check URL
                try:
                    res = subprocess.run(['curl', '-I', '-s', url], capture_output=True, text=True)
                    if '200 OK' in res.stdout:
                        valid_calls.append(row)
                        print(f"Found valid call: ID={row['id']}, CallID={row['callid']}")
                    if len(valid_calls) >= 10:
                        break
                except:
                    pass
            
            print(f"\nFound {len(valid_calls)} valid calls.")
            for call in valid_calls:
                print(f"ID: {call['id']}, CallID: {call['callid']}, URL: {call['fileurl']}")

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

if __name__ == "__main__":
    find_valid_calls()