clavitor/operations/currency-sync/main.go

// currency-sync - Fetches currency data from authoritative sources
// and updates the currencies table in corporate DB.
//
// Authoritative sources:
// - exchangerate-api.com: currency codes and exchange rates
// - Unicode CLDR: currency names (English, tracks ISO 4217), decimal places, symbols
//
// Usage: currency-sync -db /path/to/corporate.db
// Run on demand only - a few times per year.

package main

import (
	"database/sql"
	"encoding/json"
	"flag"
	"fmt"
	"io"
	"log"
	"net/http"
	"os"
	"strconv"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

const (
	// exchangerate-api.com - provides rates and currency codes
	apiKey     = "62eb374484119e26faabcfda"
	apiBaseURL = "https://v6.exchangerate-api.com/v6/%s/latest/USD"

	// Unicode CLDR - maintained by Unicode Consortium (standards body)
	// Tracks ISO 4217 currency codes, provides localized names and decimals
	cldrNamesURL     = "https://raw.githubusercontent.com/unicode-org/cldr-json/main/cldr-json/cldr-numbers-full/main/en/currencies.json"
	cldrFractionsURL = "https://raw.githubusercontent.com/unicode-org/cldr-json/main/cldr-json/cldr-core/supplemental/currencyData.json"
)

// APIResponse from exchangerate-api.com
type APIResponse struct {
	Result             string             `json:"result"`
	TimeLastUpdateUnix int64              `json:"time_last_update_unix"`
	TimeLastUpdateUTC  string             `json:"time_last_update_utc"`
	BaseCode           string             `json:"base_code"`
	ConversionRates    map[string]float64 `json:"conversion_rates"`
}

// CLDRNamesData from Unicode CLDR (currency names and symbols)
type CLDRNamesData struct {
	Main struct {
		En struct {
			Numbers struct {
				Currencies map[string]struct {
					DisplayName string `json:"displayName"`
					Symbol      string `json:"symbol"`
				} `json:"currencies"`
			} `json:"numbers"`
		} `json:"en"`
	} `json:"main"`
}

// CLDRFractionsData from Unicode CLDR (decimal places)
type CLDRFractionsData struct {
	Supplemental struct {
		CurrencyData struct {
			Fractions map[string]struct {
				Digits string `json:"_digits"`
			} `json:"fractions"`
		} `json:"currencyData"`
	} `json:"supplemental"`
}

func main() {
	var (
		dbPath = flag.String("db", "", "Path to corporate.db (required)")
		dryRun = flag.Bool("dry-run", false, "Show what would be updated without making changes")
	)
	flag.Parse()

	if *dbPath == "" {
		fmt.Fprintln(os.Stderr, "Error: -db flag is required")
		fmt.Fprintln(os.Stderr, "Usage: currency-sync -db /path/to/corporate.db")
		os.Exit(1)
	}

	// Step 1: Fetch exchange rates
	log.Println("Fetching exchange rates from exchangerate-api.com...")
	apiURL := fmt.Sprintf(apiBaseURL, apiKey)
	resp, err := http.Get(apiURL)
	if err != nil {
		log.Fatalf("Failed to fetch rates: %v", err)
	}
	defer resp.Body.Close()

	if resp.StatusCode != 200 {
		body, _ := io.ReadAll(resp.Body)
		log.Fatalf("API error: HTTP %d - %s", resp.StatusCode, string(body))
	}

	var apiResp APIResponse
	if err := json.NewDecoder(resp.Body).Decode(&apiResp); err != nil {
		log.Fatalf("Failed to decode API response: %v", err)
	}

	if apiResp.Result != "success" {
		log.Fatalf("API returned error result")
	}

	log.Printf("Rates fetched: base=%s, last_update=%s, currencies=%d",
		apiResp.BaseCode, apiResp.TimeLastUpdateUTC, len(apiResp.ConversionRates))

	// Step 2: Fetch CLDR names and symbols
	log.Println("Fetching currency names from Unicode CLDR (English locale)...")
	cldrNamesResp, err := http.Get(cldrNamesURL)
	if err != nil {
		log.Fatalf("Failed to fetch CLDR names: %v", err)
	}
	defer cldrNamesResp.Body.Close()

	if cldrNamesResp.StatusCode != 200 {
		log.Fatalf("CLDR names fetch failed: HTTP %d", cldrNamesResp.StatusCode)
	}

	var cldrNamesData CLDRNamesData
	if err := json.NewDecoder(cldrNamesResp.Body).Decode(&cldrNamesData); err != nil {
		log.Fatalf("Failed to decode CLDR names: %v", err)
	}

	names := cldrNamesData.Main.En.Numbers.Currencies
	log.Printf("CLDR names loaded: %d currencies", len(names))

	// Step 3: Fetch CLDR decimal data
	log.Println("Fetching currency decimals from Unicode CLDR...")
	cldrFracResp, err := http.Get(cldrFractionsURL)
	if err != nil {
		log.Fatalf("Failed to fetch CLDR fractions: %v", err)
	}
	defer cldrFracResp.Body.Close()

	if cldrFracResp.StatusCode != 200 {
		log.Fatalf("CLDR fractions fetch failed: HTTP %d", cldrFracResp.StatusCode)
	}

	var cldrFracData CLDRFractionsData
	if err := json.NewDecoder(cldrFracResp.Body).Decode(&cldrFracData); err != nil {
		log.Fatalf("Failed to decode CLDR fractions: %v", err)
	}

	fractions := cldrFracData.Supplemental.CurrencyData.Fractions
	log.Printf("CLDR fractions loaded: %d entries", len(fractions))

	// Step 4: Connect to database
	db, err := sql.Open("sqlite3", *dbPath)
	if err != nil {
		log.Fatalf("Failed to open database: %v", err)
	}
	defer db.Close()

	// Ensure table exists
	if err := ensureTable(db); err != nil {
		log.Fatalf("Failed to ensure table: %v", err)
	}

	// Step 5: Process currencies
	now := time.Now().Unix()
	updated := 0
	inserted := 0
	defaultDecimals := 2

	for code, rate := range apiResp.ConversionRates {
		// Skip invalid rates
		if rate <= 0 {
			continue
		}

		// Get name and symbol from CLDR
		name := ""
		symbol := ""
		if n, ok := names[code]; ok {
			name = n.DisplayName
			symbol = n.Symbol
		}

		// Get decimals from CLDR fractions
		decimals := defaultDecimals
		if frac, ok := fractions[code]; ok {
			if d, err := strconv.Atoi(frac.Digits); err == nil {
				decimals = d
			}
		} else if frac, ok := fractions["DEFAULT"]; ok {
			if d, err := strconv.Atoi(frac.Digits); err == nil {
				decimals = d
			}
		}

		if *dryRun {
			log.Printf("[DRY-RUN] %s: name=%q, symbol=%q, rate=%.6f, decimals=%d",
				code, name, symbol, rate, decimals)
			continue
		}

		// Check if currency exists
		var exists int
		err := db.QueryRow("SELECT COUNT(*) FROM currencies WHERE code = ?", code).Scan(&exists)
		if err != nil {
			log.Printf("Warning: failed to check %s: %v", code, err)
			continue
		}

		if exists > 0 {
			// Update existing
			_, err = db.Exec(`
				UPDATE currencies SET
					name = ?,
					symbol = ?,
					exchange_rate = ?,
					rate_fetched_at = ?,
					decimals = ?,
					updated_at = ?
				WHERE code = ?
			`, name, symbol, rate, now, decimals, now, code)
			if err != nil {
				log.Printf("Warning: failed to update %s: %v", code, err)
				continue
			}
			updated++
		} else {
			// Insert new - pretty_pattern is NULL (set by application)
			_, err = db.Exec(`
				INSERT INTO currencies (
					code, name, decimals, exchange_rate, rate_fetched_at,
					symbol, pretty_pattern, is_active,
					created_at, updated_at
				) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			`, code, name, decimals, rate, now,
				symbol, nil, 1,
				now, now)
			if err != nil {
				log.Printf("Warning: failed to insert %s: %v", code, err)
				continue
			}
			inserted++
		}
	}

	if *dryRun {
		log.Printf("[DRY-RUN] Would process %d currencies", len(apiResp.ConversionRates))
	} else {
		log.Printf("Complete: %d updated, %d inserted, %d total",
			updated, inserted, len(apiResp.ConversionRates))
		log.Printf("Base: USD=1.000000, last_update=%s", apiResp.TimeLastUpdateUTC)
		log.Printf("Sources: exchangerate-api.com (rates), Unicode CLDR (names/decimals/symbols)")
	}
}

func ensureTable(db *sql.DB) error {
	_, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS currencies (
			code            TEXT PRIMARY KEY,
			name            TEXT,               -- From Unicode CLDR (English, tracks ISO 4217)
			decimals        INTEGER NOT NULL DEFAULT 2,  -- From Unicode CLDR
			exchange_rate   REAL,               -- From exchangerate-api.com
			rate_fetched_at INTEGER,            -- From exchangerate-api.com
			symbol          TEXT,               -- From Unicode CLDR
			pretty_pattern  TEXT,               -- Set by application (e.g., "x9", "x0", "x000")
			is_active       INTEGER DEFAULT 1,
			created_at      INTEGER NOT NULL,
			updated_at      INTEGER
		)
	`)
	return err
}