from oa_contract_ingest_to_abacus.connectors import sf


def _fetch_all_account_jsons() -> list[tuple[int, str]]:
    cursor = sf.conn.cursor()
    cursor.execute("""
                   SELECT VENDOR_ID,
                          REGEXP_REPLACE(contract_json, ',"contract_id":[0-9]+', ',"contract_id":-1') AS CONTRACT_JSON
                   FROM DEV_ENGINEERING.SLAPOSHKo.SNAPSHOT_CONTRACT_JSON_30_06_2025
                   LIMIT 10000 OFFSET 10000;
    """)
    results = cursor.fetchall()
    return results


def _fetch_account_jsons_by_account_id(account_id: list[int]) -> list[tuple[int, str]]:
    cursor = sf.conn.cursor()
    placeholders = ", ".join(["%s"] * len(account_id))
    cursor.execute(f"""
       SELECT oc.account_id, oc.contract_json
       FROM ROYALTY_ACCOUNTING.PROD.VW_ELIGIBLE_LEGACY_CONTRACT_MIGRATION m
                JOIN royalty_accounting_reporting.prod.VW_OA_CONTRACT_ABACUS_JSON_ALL oc
                     ON m.vendor_id = oc.account_id AND m.contract_id = oc.contract_id
        WHERE ACCOUNT_ID in ({placeholders})
    """, account_id)
    results = cursor.fetchall()
    return results


def fetch_account_jsons(account_id: list[int]) -> list[tuple[int, str]]:
    """Fetch account jsons. Return list of tuples (account_id, contract_json)"""
    if not account_id:
        return _fetch_all_account_jsons()
    return _fetch_account_jsons_by_account_id(account_id)