import psycopg2
import requests

# === CONFIGURATION ===
DB_HOST = "localhost"
DB_PORT = 5432
DB_NAME = "db_name"
DB_USER = "db_user"
DB_PASSWORD = "password"

CLOUD_API_BASE_URL = "https://apps-qalityplus.soldevelo.com"
JWT_TOKEN = "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJzdWIiOiI2MzgwYjlkZGE1OTNjYjgyMmU5NGI0MWEiLCJxc2giOiJjb250ZXh0LXFzaCIsImlzcyI6IjY1MWQ0ZDcxLTY2NTUtM2QwZC04MTIwLWQxODM2MDRhM2Q3MiIsImNvbnRleHQiOnsibmF2Ijp7ImlzTmV3TmF2aWdhdGlvbiI6ZmFsc2V9LCJ1cmwiOnsiZGlzcGxheVVybCI6Imh0dHBzOlwvXC9zZC1kZXYuYXRsYXNzaWFuLm5ldCIsImRpc3BsYXlVcmxTZXJ2aWNlZGVza0hlbHBDZW50ZXIiOiJodHRwczpcL1wvc2QtZGV2LmF0bGFzc2lhbi5uZXQifX0sImV4cCI6MTc0ODI1NzE0NiwiaWF0IjoxNzQ4MjU2MjQ2fQ.lwiJI8hU6RQGuER_qvxBOhH7sadqlMK4nX_C1e67WXc"

def fetch_statuses_from_dc():
    query = 'SELECT "ID", "NAME", "CATEGORY", "COLOR_NAME", "IS_DEFAULT", "POSITION" FROM public."AO_A89232_STATUS"'

    try:
        print("Connecting to database...")
        with psycopg2.connect(
            host=DB_HOST,
            port=DB_PORT,
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD
        ) as conn:
            print("✅ Connected to database successfully.")
            with conn.cursor() as cursor:
                print(f"Executing query: {query}")
                cursor.execute(query)
                rows = cursor.fetchall()
                print(f"✅ Retrieved {len(rows)} rows.")
                return [
                    {
                        "ID": row[0],
                        "NAME": row[1],
                        "CATEGORY": row[2],
                        "COLOR_NAME": row[3],
                        "IS_DEFAULT": row[4],
                        "POSITION": row[5]
                    }
                    for row in rows
                ]

    except Exception as e:
        print("❌ Error connecting to database or executing query:", e)
        return []

# === CLOUD API FUNCTIONS ===
def get_cloud_statuses():
    url = f"{CLOUD_API_BASE_URL}/statuses"
    params = {"jwt": JWT_TOKEN}

    response = requests.get(url, params=params, headers={
        "Content-Type": "application/json"
    })
    response.raise_for_status()
   
    data = response.json()
    return {status["name"]: status["id"] for status in data.get("statuses", [])}

def create_status_in_cloud(status):
    params = f"jwt={JWT_TOKEN}"
    response = requests.post(f"{CLOUD_API_BASE_URL}/statuses?{params}")
    print("Creating status in cloud: ", status)
    response.raise_for_status()

    return response.json()["id"]

def update_status_in_cloud(status_id, status):
    url = f"{CLOUD_API_BASE_URL}/statuses/{status_id}"
    params = {"jwt": JWT_TOKEN}

    payload = {
        "fields": {
            "name": status["NAME"],
            "position": status["POSITION"],
            "colorName": status["COLOR_NAME"],
            "isDefault": status["IS_DEFAULT"],
            "category": status["CATEGORY"],
        }
    }
    
    response = requests.patch(url, params=params, json=payload, headers={
        "Content-Type": "application/json"
    })
    response.raise_for_status()

# === SYNC FUNCTION ===
def sync_statuses():
    dc_statuses = fetch_statuses_from_dc()
    cloud_statuses_by_name = get_cloud_statuses()

    STATUS_MAP = {}

    print (f"DC Statuses: {dc_statuses}")
    print (f"Cloud Statuses: {cloud_statuses_by_name}")

    for status in dc_statuses:
        name = status["NAME"]
        print(f"Processing status: {status}")
        if name in cloud_statuses_by_name:
            STATUS_MAP[status["ID"]] = cloud_statuses_by_name[name]
            print(f"[Mapped] Existing: {name} -> {cloud_statuses_by_name[name]}")
            update_status_in_cloud(cloud_statuses_by_name[name], status)
            print(f"[Updated] Status: {name} -> {cloud_statuses_by_name[name]}")
        else:
            cloud_id = create_status_in_cloud(status)
            STATUS_MAP[status["ID"]] = cloud_id
            print(f"[Created] New: {name} -> {cloud_id}")
            update_status_in_cloud(cloud_id, status)
            print(f"[Updated] Status: {name} -> {cloud_id}")

    return STATUS_MAP

# === MAIN ===
if __name__ == "__main__":
    status_map = sync_statuses()

    print("\nSTATUS_MAP:")
    for dc_id, cloud_id in status_map.items():
        print(f"{dc_id} -> {cloud_id}")