import pyodbc
import subprocess
import re
from datetime import datetime

conn = pyodbc.connect(
    'DRIVER={ODBC Driver 18 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=HomelabDB;'
    'UID=sa;'
    'PWD=Helloadmin123!;'
    'Encrypt=no;'
)

cursor = conn.cursor()

output = subprocess.check_output(
    ["docker", "ps", "--format", "{{.Names}} {{.Ports}} {{.Status}}"]
)

lines = output.decode().splitlines()

for line in lines:
    parts = line.split(" ", 2)
    name = parts[0]
    ports = parts[1] if len(parts) > 1 else ""
    raw_status = parts[2] if len(parts) > 2 else "Unknown"

    # Extract main status (Up, Exited, etc.)
    status_match = re.search(r"\b(Up|Exited|Restarting|Paused|Created|Dead)\b", raw_status)
    status = status_match.group(1) if status_match else "Unknown"

    # Extract uptime text (e.g. "7 days", "2 hours", etc.)
    uptime_match = re.search(r"(?:Up|Exited)\s+(.*?)(?:\s*\(|$)", raw_status)
    uptime = uptime_match.group(1).strip() if uptime_match else None

    # Extract health (inside parentheses like "(healthy)" or "(unhealthy)")
    health_match = re.search(r"\((healthy|unhealthy)\)", raw_status, re.IGNORECASE)
    health = health_match.group(1).lower() if health_match else "none"

    # Get creation date from `docker inspect`
    # Get creation date from `docker inspect`
    try:
        inspect_out = subprocess.check_output(
            ["docker", "inspect", "-f", "{{.Created}}", name]
        )
        created_str = inspect_out.decode().strip()  # e.g. "2025-11-07T09:25:03.548912345Z"

        # Normalize the timestamp (strip Z, keep only first 6 digits of microseconds)
        created_str = created_str.rstrip("Z")
        if "." in created_str:
            created_str = re.sub(r"\.(\d+)", lambda m: "." + m.group(1)[:6], created_str)

        created_dt = datetime.fromisoformat(created_str)
    except Exception as e:
        print(f"Warning: could not parse creation date for {name}: {e}")
        created_dt = None

    # Extract all port numbers
    port_matches = re.findall(r"(\d+)->", ports)
    if not port_matches:
        port_matches = re.findall(r"(\d+)/tcp", ports)

    if not port_matches:
        cursor.execute("""
            IF EXISTS (SELECT 1 FROM Services WHERE ServiceName = ? AND Port IS NULL)
                UPDATE Services
                SET Status = ?, HealthStatus = ?, Uptime = ?, InstalledAt = ?
                WHERE ServiceName = ? AND Port IS NULL
            ELSE
                INSERT INTO Services (ServerID, ServiceName, Port, Status, HealthStatus, Uptime, InstalledAt)
                VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (name, status, health, uptime, created_dt, name,
              1, name, None, status, health, uptime, created_dt))
    else:
        for port in port_matches:
            cursor.execute("""
                IF EXISTS (SELECT 1 FROM Services WHERE ServiceName = ? AND Port = ?)
                    UPDATE Services
                    SET Status = ?, HealthStatus = ?, Uptime = ?, InstalledAt = ?
                    WHERE ServiceName = ? AND Port = ?
                ELSE
                    INSERT INTO Services (ServerID, ServiceName, Port, Status, HealthStatus, Uptime, InstalledAt)
                    VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (name, int(port), status, health, uptime, created_dt,
                  name, int(port),
                  1, name, int(port), status, health, uptime, created_dt))

conn.commit()
conn.close()