#!/usr/bin/env python3.11
import mysql.connector
from datetime import datetime, timedelta
import subprocess
import sys
import os

db_config = {
    "host": "localhost",
    "user": "upload15_uploadtransfers01",
    "password": "2!oum10ZDS",
    "database": "upload15_uploadtransfers01"
}

PROCESSING_SCRIPT = "/home/upload15/api.uploadtransfers.com/processingfiles.py"
BUILDER_PROCESSING_SCRIPT = "/home/upload15/api.uploadtransfers.com/builder_processingfile.py"

def fetch_jobs():
    """Fetch all jobs older than 10min in processing state"""
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT *
        FROM BPyN0vqr_processing_table
        WHERE status = 'processing'
          AND started_at < (NOW() - INTERVAL 10 MINUTE)
        ORDER BY id ASC
    """)
    jobs = cursor.fetchall()
    cursor.close()
    conn.close()
    return jobs

def should_run_builder_script(item_id, order_id):
    """Check if builder script should run based on database conditions"""
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)
    
    # Query to check if image_url is empty/null and design_id is not empty
    cursor.execute("""
        SELECT image_url, design_id
        FROM BPyN0vqr_processing_table
        WHERE item_id = %s AND order_id = %s 
        LIMIT 1
    """, (item_id, order_id))
    
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    
    if result:
        image_url = result.get('image_url')
        design_id = result.get('design_id')
        
        # Check if image_url is empty/null and design_id is not empty
        image_url_empty = not image_url or image_url.strip() == ''
        design_id_not_empty = design_id and design_id.strip() != ''
        
        return image_url_empty and design_id_not_empty
    
    return False

def get_design_id(item_id, order_id):
    """Get design_id for the builder script"""
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)
    
    cursor.execute("""
        SELECT design_id
        FROM BPyN0vqr_processing_table
        WHERE item_id = %s AND order_id = %s
        LIMIT 1
    """, (item_id, order_id))
    
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    
    return result.get('design_id') if result else None

def run_job_background(job):
    """Run the processing script in background"""
    order_combo = f"{job['order_id']}_{job['item_id']}"
    cmd = [
        "python3.11", PROCESSING_SCRIPT,
        "--image-url", job['image_url'],
        "--order-id", order_combo,
        "--product-id", str(job['product_id']),
        "--width", str(job['width']),
        "--height", str(job['height']),
        "--quantity", str(job['quantity']),
        "--limitwidth", str(job['limitwidth']),
        "--aws-access-key", "AKIA2PH3BCE5ZS2SRN7P",
        "--aws-secret-key", "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",
        "--aws-region", "us-east-1",
        "--s3-bucket", job['bucket']
    ]
    logfile = f"/home/upload15/api.uploadtransfers.com/logs/job_{job['id']}.log"
    with open(logfile, "a") as f:
        subprocess.Popen(cmd, stdout=f, stderr=f, preexec_fn=os.setpgrp)  # background process

def run_builder_job_background(job, design_id):
    """Run the builder processing script in background"""
    order_combo = f"{job['order_id']}_{job['item_id']}"
    cmd = [
        "python3.11", BUILDER_PROCESSING_SCRIPT,
        "--design-id", design_id,
        "--order-id", order_combo,
        "--product-id", str(job['product_id']),
        "--quantity", str(job['quantity'])
    ]
    logfile = f"/home/upload15/api.uploadtransfers.com/logs/builder_job_{job['id']}.log"
    with open(logfile, "a") as f:
        subprocess.Popen(cmd, stdout=f, stderr=f, preexec_fn=os.setpgrp)  # background process

def main():
    jobs = fetch_jobs()
    if not jobs:
        # print("[INFO] No jobs ready for processing.")
        sys.exit(0)

    for job in jobs:
        if should_run_builder_script(job['item_id'], job['order_id']):
            design_id = get_design_id(job['item_id'], job['order_id'])
            if design_id:
                run_builder_job_background(job, design_id)
            else:
                pass
        else:
            run_job_background(job)

if __name__ == "__main__":
    main()