bmc_hub/scripts/reset_and_rebuild_internet_connections.py

#!/usr/bin/env python3
"""Reset internet connection data and rebuild from latest GlobalConnect extractions."""

from __future__ import annotations

import argparse
import json
from collections import defaultdict

from app.billing.backend.supplier_invoices import _sync_globalconnect_extraction_to_internet
from app.core.database import execute_query, execute_query_single, execute_update, init_db


def latest_globalconnect_extractions() -> list[dict]:
    rows = execute_query(
        """
        SELECT DISTINCT ON (e.file_id)
            e.extraction_id,
            e.file_id,
            e.vendor_name,
            e.document_id,
            e.document_date,
            e.created_at,
            i.filename
        FROM extractions e
        JOIN incoming_files i ON i.file_id = e.file_id
        WHERE LOWER(COALESCE(e.vendor_name, '')) LIKE '%%globalconnect%%'
        ORDER BY e.file_id, e.created_at DESC, e.extraction_id DESC
        """
    ) or []
    return [dict(row) for row in rows]


def reset_all_internet_data() -> dict:
    summary: dict[str, int] = {}
    counts = execute_query_single(
        """
        SELECT
            (SELECT COUNT(*) FROM internet_connections_connections WHERE deleted_at IS NULL) AS connections,
            (SELECT COUNT(*) FROM internet_connections_ip_ranges WHERE deleted_at IS NULL) AS ip_ranges,
            (SELECT COUNT(*) FROM internet_connections_ip_addresses WHERE deleted_at IS NULL) AS ip_addresses,
            (SELECT COUNT(*) FROM internet_connections_pricing) AS pricing,
            (SELECT COUNT(*) FROM internet_connections_history) AS history
        """
    ) or {}
    summary["before_connections"] = int(counts.get("connections") or 0)
    summary["before_ip_ranges"] = int(counts.get("ip_ranges") or 0)
    summary["before_ip_addresses"] = int(counts.get("ip_addresses") or 0)
    summary["before_pricing"] = int(counts.get("pricing") or 0)
    summary["before_history"] = int(counts.get("history") or 0)

    summary["deleted_ip_addresses"] = execute_update(
        """
        UPDATE internet_connections_ip_addresses
        SET deleted_at = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP,
            comment = CONCAT(
                COALESCE(comment, ''),
                CASE WHEN COALESCE(comment, '') = '' THEN '' ELSE E'\n' END,
                'Nulstillet før ren genimport af internetforbindelser.'
            )
        WHERE deleted_at IS NULL
        """
    )
    summary["deleted_ip_ranges"] = execute_update(
        """
        UPDATE internet_connections_ip_ranges
        SET deleted_at = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP,
            description = CONCAT(
                COALESCE(description, ''),
                CASE WHEN COALESCE(description, '') = '' THEN '' ELSE E'\n' END,
                'Nulstillet før ren genimport af internetforbindelser.'
            )
        WHERE deleted_at IS NULL
        """
    )
    summary["deleted_connections"] = execute_update(
        """
        UPDATE internet_connections_connections
        SET deleted_at = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP,
            status = 'inactive',
            notes = CONCAT(
                COALESCE(notes, ''),
                CASE WHEN COALESCE(notes, '') = '' THEN '' ELSE E'\n' END,
                'Nulstillet før ren genimport af internetforbindelser.'
            )
        WHERE deleted_at IS NULL
        """
    )
    summary["deleted_pricing"] = execute_update("DELETE FROM internet_connections_pricing")
    summary["deleted_history"] = execute_update("DELETE FROM internet_connections_history")
    return summary


def rebuild_from_globalconnect() -> dict:
    extractions = latest_globalconnect_extractions()
    results = []
    totals = defaultdict(int)

    for extraction_stub in extractions:
        extraction = execute_query_single(
            """
            SELECT *
            FROM extractions
            WHERE extraction_id = %s
            """,
            (extraction_stub["extraction_id"],),
        )
        if not extraction:
            continue
        result = _sync_globalconnect_extraction_to_internet(dict(extraction))
        result_summary = {
            "file_id": extraction_stub["file_id"],
            "extraction_id": extraction_stub["extraction_id"],
            "filename": extraction_stub["filename"],
            "document_id": extraction_stub["document_id"],
            "connections_synced": int(result.get("connections_synced") or 0),
            "connections_created": int(result.get("connections_created") or 0),
            "connections_updated": int(result.get("connections_updated") or 0),
            "ip_ranges_synced": int(result.get("ip_ranges_synced") or 0),
            "skipped_connection_lines": int(result.get("skipped_connection_lines") or 0),
            "skipped_ip_range_lines": int(result.get("skipped_orphan_ip_ranges") or 0),
            "verification": result.get("verification") or {},
        }
        results.append(result_summary)
        totals["files_processed"] += 1
        totals["connections_synced"] += result_summary["connections_synced"]
        totals["connections_created"] += result_summary["connections_created"]
        totals["connections_updated"] += result_summary["connections_updated"]
        totals["ip_ranges_synced"] += result_summary["ip_ranges_synced"]
        totals["skipped_connection_lines"] += result_summary["skipped_connection_lines"]
        totals["skipped_ip_range_lines"] += result_summary["skipped_ip_range_lines"]

    counts = execute_query_single(
        """
        SELECT
            (SELECT COUNT(*) FROM internet_connections_connections WHERE deleted_at IS NULL) AS active_connections,
            (SELECT COUNT(*) FROM internet_connections_connections WHERE deleted_at IS NULL AND (address IS NULL OR BTRIM(address) = '')) AS missing_address_connections,
            (SELECT COUNT(*) FROM internet_connections_ip_ranges WHERE deleted_at IS NULL) AS active_ip_ranges,
            (SELECT COUNT(*) FROM internet_connections_ip_addresses WHERE deleted_at IS NULL) AS active_ip_addresses
        """
    ) or {}
    return {
        "totals": dict(totals),
        "results": results,
        "post_counts": {
            "active_connections": int(counts.get("active_connections") or 0),
            "missing_address_connections": int(counts.get("missing_address_connections") or 0),
            "active_ip_ranges": int(counts.get("active_ip_ranges") or 0),
            "active_ip_addresses": int(counts.get("active_ip_addresses") or 0),
        },
    }


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--skip-rebuild", action="store_true", help="Only reset internet data")
    args = parser.parse_args()

    init_db()
    payload = {
        "reset": reset_all_internet_data(),
        "rebuild": None if args.skip_rebuild else rebuild_from_globalconnect(),
    }
    print(json.dumps(payload, ensure_ascii=False, indent=2, default=str))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())