import psycopg
import requests
from dotenv import load_dotenv

from app import config

load_dotenv()


URL = "https://api.frankfurter.dev/v1/latest?base=USD"


def update_rates():
    try:
        print(f"Fetching rates from {URL}...")
        response = requests.get(URL, timeout=10)
        response.raise_for_status()
        data = response.json()

        rates = data.get("rates", {})
        if not rates:
            print("Failed to fetch rates: 'rates' key is missing from response.")
            return

        rates["USD"] = 1.0

        inr_rate = rates.get("INR")
        if inr_rate:
            rates["NPR"] = inr_rate * 1.6
            print(f"Calculated NPR rate: {rates['NPR']} (from INR {inr_rate} * 1.6)")
        else:
            print("Warning: INR rate not found, cannot calculate NPR.")

        print("Connecting to PostgreSQL to save rates...")

        conn_string = (
            f"postgresql://{config.PG_USER}:{config.PG_PASSWORD}"
            f"@{config.PG_HOST}:{config.PG_PORT}/{config.PG_DATABASE}"
        )
        with psycopg.connect(conn_string) as conn:
            with conn.cursor() as cur:
                cur.execute("""
                CREATE TABLE IF NOT EXISTS exchange_rates (
                    currency VARCHAR(3) PRIMARY KEY,
                    rate FLOAT NOT NULL,
                    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
                )
                """)

                sql = """
                INSERT INTO exchange_rates (currency, rate, updated_at)
                VALUES (%s, %s, CURRENT_TIMESTAMP)
                ON CONFLICT (currency) DO UPDATE SET
                    rate = EXCLUDED.rate,
                    updated_at = EXCLUDED.updated_at
                """

                rows = [(curr, rate) for curr, rate in rates.items()]
                cur.executemany(sql, rows)
            conn.commit()

        print(f"Successfully upserted {len(rates)} exchange rates into PostgreSQL.")

    except requests.exceptions.HTTPError as e:
        print(f"Fetch failed: {e}")


if __name__ == "__main__":
    update_rates()