#!/usr/bin/env python3
"""
Backfill de sys_created_by en sn.case

- Busca registros con sys_created_by IS NULL
- Consulta ServiceNow por number
- Actualiza PostgreSQL solo si SN devuelve valor
"""

import requests
import psycopg2
import logging
import urllib3

# ================= CONFIG =================

# ServiceNow
SN_BASE_URL = "https://atowerarprod.service-now.com/api/now/table/sn_customerservice_case"
SN_USER = "plataformas.atc"
SN_PASS = "fiberatc1"

# PostgreSQL
PG_CONN = {
    "host": "localhost",
    "database": "postgres",
    "user": "postgres",
    "password": "post3008"
}

# ================= LOGGING =================
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(message)s"
)

# Desactivar warning SSL (mismo criterio que loaders)
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

# ================= FUNCTIONS =================

def get_cases_with_null_sys_created_by(conn):
    sql = """
        SELECT number
        FROM sn."case"
        WHERE sys_created_by IS NULL
          AND number IS NOT NULL;
    """
    with conn.cursor() as cur:
        cur.execute(sql)
        return [r[0] for r in cur.fetchall()]


def fetch_sys_created_by_from_sn(number):
    params = {
        "sysparm_query": f"number={number}",
        "sysparm_fields": "number,sys_created_by",
        "sysparm_display_value": "true",
        "sysparm_exclude_reference_link": "true"
    }

    resp = requests.get(
        SN_BASE_URL,
        auth=(SN_USER, SN_PASS),
        params=params,
        verify=False,
        timeout=30
    )

    resp.raise_for_status()
    data = resp.json().get("result", [])

    if not data:
        return None

    return data[0].get("sys_created_by")


def update_sys_created_by(conn, number, sys_created_by):
    sql = """
        UPDATE sn."case"
        SET sys_created_by = %s
        WHERE number = %s
          AND sys_created_by IS NULL;
    """
    with conn.cursor() as cur:
        cur.execute(sql, (sys_created_by, number))


# ================= MAIN =================

def main():
    conn = psycopg2.connect(**PG_CONN)
    conn.autocommit = False

    try:
        numbers = get_cases_with_null_sys_created_by(conn)
        logging.info(f"Casos a reparar: {len(numbers)}")

        for number in numbers:
            try:
                sys_created_by = fetch_sys_created_by_from_sn(number)

                if sys_created_by:
                    update_sys_created_by(conn, number, sys_created_by)
                    conn.commit()
                    logging.info(f"{number} -> sys_created_by actualizado ({sys_created_by})")
                else:
                    conn.rollback()
                    logging.warning(f"{number} -> SN no devolvió sys_created_by")

            except Exception as e:
                conn.rollback()
                logging.error(f"{number} -> error: {e}")

    finally:
        conn.close()


if __name__ == "__main__":
    main()