bmc_hub/scripts/reconcile_known_internet_connections.py

#!/usr/bin/env python3
"""Idempotent correction of verified circuit, address, speed and IP reference data."""

from __future__ import annotations

import ipaddress

from app.core.database import execute_insert, execute_query_single, execute_update, init_db


CONNECTIONS = [
    ("NKA-021426", "Tobaksvej 25, 2860 Søborg", 100, ["83.151.156.52/30", "77.233.233.208/28"]),
    ("NKA-023762", "Lundbygaardsvej 100, 4750 Lundby", 200, ["77.233.233.80/28", "152.115.111.240/30"]),
    ("NKA-020900", "Rydagervej 27, 2620 Albertslund", 1000, ["87.116.1.200/29"]),
    ("NKA-023036", "Slotsmarken 18, 2970 Hørsholm", 500, ["152.115.140.8/30", "217.195.179.0/26"]),
    ("NKA-022948", "Oldenburg Alle 7, 2630 Taastrup", 200, ["130.185.140.120/30", "62.116.202.0/28"]),
    ("NKA-022949", "Borupvang 2B, 2750 Ballerup", 500, ["152.115.70.140/30", "87.116.23.0/28"]),
    ("NKA-023763", "Slotsmarken 10, 2970 Hørsholm", 200, ["152.115.137.60/30", "62.116.202.96/28"]),
    ("NKA-024219", "Mileparken 22, 2740 Skovlunde", 1000, ["152.115.111.232/30", "152.115.63.128/26"]),
    ("NML-024495", "Lundbygaardsvej 100, 4750 Lundby", None, ["152.115.111.236/30"]),
    ("NKA-027783", "Ejby Industrivej 1, 2600 Glostrup", 1000, []),
    ("NKA-027784", "Møgelbakken 8, 8520 Lystrup", 1000, ["130.185.141.92/30"]),
    ("NKA-028639", "Slotsmarken 11, 2970 Hørsholm", 100, ["152.115.36.80/28"]),
    ("NKA-025021", "Broenge 4, 2635 Ishøj", 300, ["152.115.107.168/30", "130.185.134.44/30"]),
    ("NKA-031137", "Marielundvej 30, 2730 Herlev", 500, ["217.74.209.220/30"]),
    ("NKA-031083", "Slotsmarken 17, 2970 Hørsholm", 100, ["93.176.69.96/30"]),
    ("NKA-031131", "Sankt Kunds vej 26, 1903 Frederiksberg C", 500, ["152.115.178.192/30"]),
    ("NKA-021047", "Herstedvang 14, 2620 Albertslund", 300, ["83.136.94.128/26"]),
    ("NKA-027964", "Firskovvej 36, 2800 Kongens Lyngby", 1000, ["217.74.219.56/30", "152.115.61.32/27"]),
    ("HB944140", "Lejrvej 17-19, 3500 Værløse", 5000, []),
    ("NKA-021275", "Ejby Industrivej 1, 2600 Glostrup", 1000, ["87.116.30.96/27", "5.56.159.32/29"]),
]


def reference_key(value: str) -> str:
    return "".join(character for character in value.upper() if character.isalnum())


def reconcile() -> dict[str, int]:
    result = {"created_connections": 0, "updated_connections": 0, "created_ranges": 0, "updated_ranges": 0}
    for reference, address, speed, cidrs in CONNECTIONS:
        connection = execute_query_single(
            """SELECT id FROM internet_connections_connections
               WHERE deleted_at IS NULL
                 AND regexp_replace(UPPER(COALESCE(circuit_number, '')), '[^A-Z0-9]', '', 'g') = %s
               ORDER BY id DESC LIMIT 1""",
            (reference_key(reference),),
        )
        if connection:
            connection_id = int(connection["id"])
            execute_update(
                """UPDATE internet_connections_connections
                   SET circuit_number=%s, address=%s,
                       speed_mbps=COALESCE(%s, speed_mbps),
                       download_mbps=COALESCE(%s, download_mbps),
                       upload_mbps=COALESCE(%s, upload_mbps), updated_at=CURRENT_TIMESTAMP
                   WHERE id=%s""",
                (reference, address, speed, speed, speed, connection_id),
            )
            result["updated_connections"] += 1
        else:
            connection_id = execute_insert(
                """INSERT INTO internet_connections_connections
                   (name,provider,address,status,circuit_number,speed_mbps,download_mbps,upload_mbps,
                    monthly_cost,sales_price,allocation_model,value_type,notes)
                   VALUES (%s,'GlobalConnect A/S',%s,'pending',%s,%s,%s,%s,0,0,'dedicated','other',
                           'Oprettet fra verificeret kredsløbsoversigt. Kunde tildeles manuelt.') RETURNING id""",
                (address, address, reference, speed, speed, speed),
            )
            result["created_connections"] += 1

        for raw_cidr in cidrs:
            cidr = str(ipaddress.ip_network(raw_cidr.replace(" ", ""), strict=False))
            ip_range = execute_query_single(
                """SELECT id FROM internet_connections_ip_ranges
                   WHERE deleted_at IS NULL AND cidr::cidr=%s::cidr ORDER BY id LIMIT 1""",
                (cidr,),
            )
            if ip_range:
                execute_update(
                    """UPDATE internet_connections_ip_ranges
                       SET connection_id=%s, provider_reference=%s, service_address=%s,
                           updated_at=CURRENT_TIMESTAMP WHERE id=%s""",
                    (connection_id, reference, address, ip_range["id"]),
                )
                result["updated_ranges"] += 1
            else:
                execute_insert(
                    """INSERT INTO internet_connections_ip_ranges
                       (connection_id,name,cidr,description,provider_reference,service_address,monthly_cost,sales_price)
                       VALUES (%s,%s,%s,'Oprettet fra verificeret kredsløbsoversigt.',%s,%s,0,0) RETURNING id""",
                    (connection_id, f"IPv4 · {cidr}", cidr, reference, address),
                )
                result["created_ranges"] += 1
    return result


if __name__ == "__main__":
    init_db()
    print(reconcile())