#!/usr/bin/env python3
"""
This program provides an ETL pipeline built specifically for maintaining a repeater database. 
The program downloads the RadioID.net JSON repeater file, computes each repeater’s transmit 
frequency, and then inserts or updates those records in a PostgreSQL database.

2021-03-21 KB1B nedecn@kb1b.org
"""

import logging
from decimal import Decimal, InvalidOperation

import psycopg2
from psycopg2.extras import execute_batch
import requests

RPT_JSON_URL = "https://database.radioid.net/static/rptrs.json"

DB_CONFIG = {
    "host": "<server>",
    "dbname": "<database>",
    "user": "<database_user>",
    "password": "<database_password>",
    "port": <database_port_number>,
}

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


def fetch_repeaters():
    """Fetch repeater JSON data from the remote API."""
    try:
        response = requests.get(RPT_JSON_URL, timeout=30)
        response.raise_for_status()
        data = response.json()
    except requests.RequestException as exc:
        raise RuntimeError(f"Failed to fetch repeater data: {exc}") from exc

    repeaters = data.get("rptrs")
    if not isinstance(repeaters, list):
        raise ValueError("Unexpected JSON format: missing 'rptrs' list")

    return repeaters


def compute_tx_frequency(rx, offset):
    """Compute TX frequency as a string, or return None on failure."""
    if rx is None or offset is None:
        return None
    try:
        return str(Decimal(rx) + Decimal(offset))
    except (InvalidOperation, TypeError):
        return None


def normalize_repeater(rpt):
    """Convert raw repeater JSON into a DB-ready dictionary."""
    locator = rpt.get("id")
    if locator is None:
        return None

    rx = rpt.get("frequency")
    offset = rpt.get("offset")

    return {
        "locator": str(locator),
        "callsign": rpt.get("callsign"),
        "city": rpt.get("city"),
        "state": rpt.get("state"),
        "country": rpt.get("country"),
        "freq_rx": rx,
        "freq_tx": compute_tx_frequency(rx, offset),
        "offset": offset,
        "color_code": str(rpt["color_code"]) if rpt.get("color_code") is not None else None,
        "assigned": rpt.get("assigned"),
        "ts_linked": rpt.get("ts_linked"),
        "trustee": rpt.get("trustee"),
        "ipsc_network": rpt.get("ipsc_network"),
    }


def insert_repeaters(conn, repeaters):
    """Insert or update repeater records in PostgreSQL."""
    sql = """
        INSERT INTO public.repeaters (
            locator, callsign, city, state, country,
            freq_rx, freq_tx, "offset",
            color_code, assigned, ts_linked,
            trustee, ipsc_network
        )
        VALUES (
            %(locator)s, %(callsign)s, %(city)s, %(state)s, %(country)s,
            %(freq_rx)s, %(freq_tx)s, %(offset)s,
            %(color_code)s, %(assigned)s, %(ts_linked)s,
            %(trustee)s, %(ipsc_network)s
        )
        ON CONFLICT (locator) DO UPDATE SET
            callsign     = EXCLUDED.callsign,
            city         = EXCLUDED.city,
            state        = EXCLUDED.state,
            country      = EXCLUDED.country,
            freq_rx      = EXCLUDED.freq_rx,
            freq_tx      = EXCLUDED.freq_tx,
            "offset"     = EXCLUDED."offset",
            color_code   = EXCLUDED.color_code,
            assigned     = EXCLUDED.assigned,
            ts_linked    = EXCLUDED.ts_linked,
            trustee      = EXCLUDED.trustee,
            ipsc_network = EXCLUDED.ipsc_network;
    """

    rows = [row for rpt in repeaters if (row := normalize_repeater(rpt))]

    with conn.cursor() as cur:
        execute_batch(cur, sql, rows, page_size=500)

    conn.commit()
    return len(rows)


def main():
    logging.info("Fetching repeater database...")
    repeaters = fetch_repeaters()
    logging.info(f"Repeaters loaded: {len(repeaters)}")

    logging.info("Connecting to PostgreSQL...")
    with psycopg2.connect(**DB_CONFIG) as conn:
        count = insert_repeaters(conn, repeaters)
        logging.info(f"Inserted/updated {count} repeaters")


if __name__ == "__main__":
    main()