import os
import time
import paramiko
from dotenv import load_dotenv

repo_root = "c:/Users/lanfr144/Documents/DOPRO1/Antigravity/Food"
load_dotenv(dotenv_path=os.path.join(repo_root, ".env"))

host = os.environ.get('SERVER_HOST', '192.168.130.170')
user = os.environ.get('SERVER_USER', 'francois')
password = os.environ.get('SERVER_PASS', 'BTSai123')

def is_downloading(ssh):
    stdin, stdout, stderr = ssh.exec_command("pgrep -f download_csv.sh")
    out1 = stdout.read().decode('utf-8').strip()
    stdin, stdout, stderr = ssh.exec_command("pgrep -f curl")
    out2 = stdout.read().decode('utf-8').strip()
    return bool(out1 or out2)

def main():
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    try:
        ssh.connect(host, username=user, password=password, timeout=15)
        print("Connected! Monitoring download...")
        
        # Monitor download loop
        start_time = time.time()
        while True:
            downloading = is_downloading(ssh)
            
            # Check file size
            stdin, stdout, stderr = ssh.exec_command("ls -lh ~/food_project/data/en.openfoodfacts.org.products.csv")
            size_out = stdout.read().decode('utf-8').strip()
            
            print(f"[Elapsed: {int(time.time() - start_time)}s] Downloading: {downloading}. File info: {size_out}")
            
            if not downloading:
                # Double check to make sure it's really finished
                time.sleep(5)
                if not is_downloading(ssh):
                    print("Download has finished!")
                    break
            
            time.sleep(30)
            
        # Trigger ingestion
        print("\nTriggering database ingestion on remote server...")
        ssh.exec_command("cd ~/food_project && bash start_batch_ingest.sh")
        time.sleep(5)
        
        # Monitor row count growth
        print("\nMonitoring database row count:")
        for _ in range(6):
            stdin, stdout, stderr = ssh.exec_command("docker exec -i food_project-mysql-1 mysql -u food_reader -pBTSai123 food_db -e 'SELECT COUNT(*) FROM products_core;'")
            count_out = stdout.read().decode('utf-8').strip()
            print(f"Row count: {count_out.replace('mysql: [Warning] Using a password on the command line interface can be insecure.', '').strip()}")
            time.sleep(15)
            
    except Exception as e:
        print(f"Error: {e}")
    finally:
        ssh.close()
        print("Connection closed.")

if __name__ == "__main__":
    main()