eac-ledger / internal /api /api.go
runitx's picture
Claude Opus 5.5
eac-ledger: ledger of the EAEU register of conformity documents
2a6941d unverified
Raw History Blame Contribute Delete
13.6 kB
// Package api serves the ledger over HTTP as JSON.
//
// Everything the API returns is safe to publish: sole traders' names and
// addresses are withheld, applicants are identified by public_id (a keyed
// hash), and contacts appear only as keyed hashes.
package api
import (
"context"
"database/sql"
"encoding/json"
"log/slog"
"net/http"
"strconv"
"strings"
"time"
"github.com/go-chi/chi/v5"
"github.com/go-chi/chi/v5/middleware"
"eacledger/internal/version"
)
// API is the HTTP handler set.
type API struct {
db *sql.DB
log *slog.Logger
}
// New returns an API over db.
func New(db *sql.DB, log *slog.Logger) *API { return &API{db: db, log: log} }
// Router returns the HTTP routes.
func (a *API) Router() http.Handler {
r := chi.NewRouter()
r.Use(middleware.RealIP, middleware.Recoverer, middleware.Timeout(60*time.Second))
r.Get("/healthz", func(w http.ResponseWriter, _ *http.Request) { w.Write([]byte("ok\n")) })
r.Get("/api/stats", a.stats)
r.Get("/api/docs", a.docs)
r.Get("/api/docs/{key}", a.doc)
r.Get("/api/applicants/{id}", a.applicant)
r.Get("/api/brands", a.brands)
r.Get("/api/events", a.events)
return r
}
// KV is one summary line.
type KV struct {
Key string `json:"key"`
Value any `json:"value"`
}
// Summary returns headline counts.
func Summary(ctx context.Context, db *sql.DB) ([]KV, error) {
q := []struct{ k, sql string }{
{"documents", `SELECT COUNT(*) FROM docs`},
{"documents_active_status", `SELECT COUNT(*) FROM docs WHERE status_code IN ('01','04','05')`},
{"certificates", `SELECT COUNT(*) FROM docs WHERE kind='certificate'`},
{"declarations", `SELECT COUNT(*) FROM docs WHERE kind='declaration'`},
{"issued_RU", `SELECT COUNT(*) FROM docs WHERE issuing_country='RU'`},
{"issued_BY", `SELECT COUNT(*) FROM docs WHERE issuing_country='BY'`},
{"issued_KZ", `SELECT COUNT(*) FROM docs WHERE issuing_country='KZ'`},
{"issued_KG", `SELECT COUNT(*) FROM docs WHERE issuing_country='KG'`},
{"issued_AM", `SELECT COUNT(*) FROM docs WHERE issuing_country='AM'`},
{"coalition_manufacturer_docs", `SELECT COUNT(*) FROM docs WHERE coalition_mfr=1`},
{"coalition_manufacturer_docs_since_2022-02-24", `SELECT COUNT(*) FROM docs WHERE coalition_mfr=1 AND start_date>='2022-02-24'`},
{"chpl_docs", `SELECT COUNT(*) FROM docs WHERE chp_tier IS NOT NULL`},
{"chpl_coalition_docs_since_2022-02-24", `SELECT COUNT(*) FROM docs WHERE chp_tier IS NOT NULL AND coalition_mfr=1 AND start_date>='2022-02-24'`},
{"docs_with_brand_match", `SELECT COUNT(DISTINCT doc_key) FROM doc_brands`},
{"applicants", `SELECT COUNT(*) FROM applicants`},
{"manufacturers", `SELECT COUNT(*) FROM manufacturers`},
{"vanished_docs", `SELECT COUNT(*) FROM docs WHERE vanished_on IS NOT NULL`},
{"deleted_flag_docs", `SELECT COUNT(*) FROM docs WHERE deleted=1`},
{"events", `SELECT COUNT(*) FROM events`},
{"last_full_snapshot", `SELECT COALESCE((SELECT value FROM meta WHERE key='last_full_day'),'')`},
{"last_ingest_day", `SELECT COALESCE(MAX(day),'') FROM ingests WHERE finished_at IS NOT NULL`},
}
out := make([]KV, 0, len(q))
for _, x := range q {
var v any
if err := db.QueryRowContext(ctx, x.sql).Scan(&v); err != nil {
return nil, err
}
if b, ok := v.([]byte); ok {
v = string(b)
}
out = append(out, KV{x.k, v})
}
return out, nil
}
func (a *API) stats(w http.ResponseWriter, r *http.Request) {
s, err := Summary(r.Context(), a.db)
if err != nil {
a.fail(w, err)
return
}
m := map[string]any{"version": version.Version}
for _, kv := range s {
m[kv.Key] = kv.Value
}
writeJSON(w, m)
}
// DocRow is a document in list responses.
type DocRow struct {
Key string `json:"doc_key"`
DocID string `json:"doc_id"`
Issuing *string `json:"issuing_country"`
Kind string `json:"kind"`
Start *string `json:"start_date"`
Validity *string `json:"validity_date"`
Status *string `json:"status_code"`
StatusStart *string `json:"status_start"`
ApplicantID *string `json:"applicant_id"`
ApplicantName *string `json:"applicant_name"`
ApplicantCC *string `json:"applicant_country"`
ApplicantReg *string `json:"applicant_region"`
Authority *string `json:"authority_name"`
AuthorityID *string `json:"authority_id"`
CoalitionMfr bool `json:"coalition_manufacturer"`
CHPTier *string `json:"chp_tier"`
VanishedOn *string `json:"vanished_on"`
Manufacturers []string `json:"manufacturers,omitempty"`
Brands []string `json:"brands,omitempty"`
}
const docSelect = `SELECT d.doc_key, d.doc_id, d.issuing_country, d.kind, d.start_date, d.validity_date, d.status_code, d.status_start,
ap.public_id, CASE WHEN ap.natural_person=1 THEN NULL ELSE ap.name END, ap.country, ap.region,
d.authority_name, d.authority_id, d.coalition_mfr, d.chp_tier, d.vanished_on
FROM docs d LEFT JOIN applicants ap ON ap.applicant_key=d.applicant_key`
func scanDoc(rows interface{ Scan(...any) error }) (DocRow, error) {
var d DocRow
var coal int
err := rows.Scan(&d.Key, &d.DocID, &d.Issuing, &d.Kind, &d.Start, &d.Validity, &d.Status, &d.StatusStart,
&d.ApplicantID, &d.ApplicantName, &d.ApplicantCC, &d.ApplicantReg, &d.Authority, &d.AuthorityID, &coal, &d.CHPTier, &d.VanishedOn)
d.CoalitionMfr = coal == 1
return d, err
}
func (a *API) docs(w http.ResponseWriter, r *http.Request) {
q := r.URL.Query()
var where []string
var args []any
add := func(cond string, v ...any) { where = append(where, cond); args = append(args, v...) }
if v := q.Get("brand"); v != "" {
add(`d.doc_key IN (SELECT doc_key FROM doc_brands WHERE brand=?)`, v)
}
if v := q.Get("hs6"); v != "" {
add(`d.doc_key IN (SELECT doc_key FROM doc_hs WHERE hs6=?)`, v)
}
if v := q.Get("tier"); v != "" {
add(`d.chp_tier=?`, v)
}
if q.Get("chpl") == "1" {
add(`d.chp_tier IS NOT NULL`)
}
if v := q.Get("applicant"); v != "" {
add(`ap.public_id=?`, v)
}
if v := q.Get("mfr_country"); v != "" {
add(`d.doc_key IN (SELECT dm.doc_key FROM doc_manufacturers dm JOIN manufacturers m ON m.mfr_key=dm.mfr_key WHERE m.country=?)`, strings.ToUpper(v))
}
if v := q.Get("issuing"); v != "" {
add(`d.issuing_country=?`, strings.ToUpper(v))
}
if v := q.Get("status"); v != "" {
add(`d.status_code=?`, v)
}
if v := q.Get("from"); v != "" {
add(`d.start_date>=?`, v)
}
if v := q.Get("to"); v != "" {
add(`d.start_date<=?`, v)
}
if q.Get("coalition") == "1" {
add(`d.coalition_mfr=1`)
}
if q.Get("vanished") == "1" {
add(`d.vanished_on IS NOT NULL`)
}
if v := q.Get("doc_id"); v != "" {
add(`d.doc_id=?`, v)
}
if v := q.Get("region"); v != "" {
add(`ap.region=?`, v)
}
sqlq := docSelect
if len(where) > 0 {
sqlq += " WHERE " + strings.Join(where, " AND ")
}
limit := clamp(atoi(q.Get("limit"), 100), 1, 1000)
offset := max(atoi(q.Get("offset"), 0), 0)
sqlq += ` ORDER BY d.start_date DESC, d.doc_key LIMIT ? OFFSET ?`
args = append(args, limit, offset)
rows, err := a.db.QueryContext(r.Context(), sqlq, args...)
if err != nil {
a.fail(w, err)
return
}
defer rows.Close()
out := []DocRow{}
for rows.Next() {
d, err := scanDoc(rows)
if err != nil {
a.fail(w, err)
return
}
out = append(out, d)
}
if err := rows.Err(); err != nil {
a.fail(w, err)
return
}
for i := range out {
a.decorate(r.Context(), &out[i])
}
writeJSON(w, map[string]any{"limit": limit, "offset": offset, "docs": out})
}
func (a *API) decorate(ctx context.Context, d *DocRow) {
if rows, err := a.db.QueryContext(ctx, `SELECT CASE WHEN m.natural_person=1 THEN '(natural person)' ELSE m.name END||' ('||COALESCE(m.country,'?')||')' FROM doc_manufacturers dm JOIN manufacturers m ON m.mfr_key=dm.mfr_key WHERE dm.doc_key=?`, d.Key); err == nil {
for rows.Next() {
var s string
if rows.Scan(&s) == nil {
d.Manufacturers = append(d.Manufacturers, s)
}
}
rows.Close()
}
if rows, err := a.db.QueryContext(ctx, `SELECT DISTINCT brand FROM doc_brands WHERE doc_key=? ORDER BY brand`, d.Key); err == nil {
for rows.Next() {
var s string
if rows.Scan(&s) == nil {
d.Brands = append(d.Brands, s)
}
}
rows.Close()
}
}
func (a *API) doc(w http.ResponseWriter, r *http.Request) {
ctx := r.Context()
key := chi.URLParam(r, "key")
d, err := scanDoc(a.db.QueryRowContext(ctx, docSelect+` WHERE d.doc_key=?`, key))
if err == sql.ErrNoRows {
http.Error(w, "not found", http.StatusNotFound)
return
}
if err != nil {
a.fail(w, err)
return
}
a.decorate(ctx, &d)
out := map[string]any{"doc": d}
out["products"] = a.rows(ctx, `SELECT idx, name, trade_names, text FROM doc_products WHERE doc_key=? ORDER BY idx`, key)
out["hs"] = a.rows(ctx, `SELECT h.hs, h.hs6, c.tier AS chp_tier FROM doc_hs h LEFT JOIN chpl c ON c.hs6=h.hs6 WHERE h.doc_key=?`, key)
out["evidence"] = a.rows(ctx, `SELECT lab_name, accreditation, evidence_id, evidence_date, evidence_name FROM doc_labs WHERE doc_key=? ORDER BY idx`, key)
out["events"] = a.rows(ctx, `SELECT day, event, status_from, status_to, source FROM events WHERE doc_key=? ORDER BY id`, key)
writeJSON(w, out)
}
func (a *API) applicant(w http.ResponseWriter, r *http.Request) {
ctx := r.Context()
id := chi.URLParam(r, "id")
var key string
if err := a.db.QueryRowContext(ctx, `SELECT applicant_key FROM applicants WHERE public_id=?`, id).Scan(&key); err != nil {
http.Error(w, "not found", http.StatusNotFound)
return
}
out := map[string]any{}
if info := a.rows(ctx, `SELECT public_id, country, CASE WHEN natural_person=1 THEN NULL ELSE name END AS name, natural_person, id_kind,
CASE WHEN natural_person=1 THEN NULL ELSE id_value END AS id_value, region,
CASE WHEN natural_person=1 THEN NULL ELSE address END AS address FROM applicants WHERE applicant_key=?`, key); len(info) > 0 {
out["applicant"] = info[0]
}
out["by_year"] = a.rows(ctx, `SELECT substr(start_date,1,4) AS year, COUNT(*) AS docs, SUM(coalition_mfr) AS coalition_docs,
SUM(chp_tier IS NOT NULL) AS chpl_docs FROM docs WHERE applicant_key=? GROUP BY 1 ORDER BY 1`, key)
out["brands"] = a.rows(ctx, `SELECT b.brand, COUNT(DISTINCT b.doc_key) AS docs, MIN(d.start_date) AS first, MAX(d.start_date) AS last
FROM doc_brands b JOIN docs d ON d.doc_key=b.doc_key WHERE d.applicant_key=? GROUP BY 1 ORDER BY 2 DESC LIMIT 100`, key)
out["manufacturer_countries"] = a.rows(ctx, `SELECT m.country, COUNT(DISTINCT d.doc_key) AS docs FROM docs d
JOIN doc_manufacturers dm ON dm.doc_key=d.doc_key JOIN manufacturers m ON m.mfr_key=dm.mfr_key
WHERE d.applicant_key=? GROUP BY 1 ORDER BY 2 DESC`, key)
out["issuing_countries"] = a.rows(ctx, `SELECT issuing_country, COUNT(*) AS docs FROM docs WHERE applicant_key=? GROUP BY 1 ORDER BY 2 DESC`, key)
// Applicants that share a phone, e-mail or website with this one.
out["shared_contacts"] = a.rows(ctx, `SELECT ap.public_id, ap.country, CASE WHEN ap.natural_person=1 THEN NULL ELSE ap.name END AS name,
COUNT(DISTINCT c2.hash) AS shared FROM contacts c1
JOIN contacts c2 ON c2.hash=c1.hash AND c2.owner_kind='applicant' AND c2.owner_key<>c1.owner_key
JOIN applicants ap ON ap.applicant_key=c2.owner_key
WHERE c1.owner_kind='applicant' AND c1.owner_key=? GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 100`, key)
writeJSON(w, out)
}
func (a *API) brands(w http.ResponseWriter, r *http.Request) {
from := r.URL.Query().Get("from")
if from == "" {
from = "2022-02-24"
}
writeJSON(w, a.rows(r.Context(), `SELECT b.brand, br.country, br.sector, COUNT(DISTINCT b.doc_key) AS docs,
COUNT(DISTINCT d.applicant_key) AS applicants,
SUM(d.issuing_country='RU') AS issued_ru, SUM(d.issuing_country IN ('KG','AM','KZ','BY')) AS issued_other_eaeu
FROM doc_brands b JOIN docs d ON d.doc_key=b.doc_key LEFT JOIN brands br ON br.brand=b.brand
WHERE d.start_date>=? GROUP BY 1,2,3 ORDER BY docs DESC`, from))
}
func (a *API) events(w http.ResponseWriter, r *http.Request) {
q := r.URL.Query()
since := q.Get("since")
if since == "" {
since = time.Now().AddDate(0, 0, -30).Format("2006-01-02")
}
ev := q.Get("event")
limit := clamp(atoi(q.Get("limit"), 500), 1, 5000)
writeJSON(w, a.rows(r.Context(), `SELECT e.day, e.event, e.status_from, e.status_to, d.doc_id, d.doc_key, d.issuing_country, d.coalition_mfr, d.chp_tier
FROM events e JOIN docs d ON d.doc_key=e.doc_key WHERE e.day>=? AND (?='' OR e.event=?) ORDER BY e.day DESC, e.id DESC LIMIT ?`, since, ev, ev, limit))
}
func (a *API) rows(ctx context.Context, q string, args ...any) []map[string]any {
rows, err := a.db.QueryContext(ctx, q, args...)
if err != nil {
a.log.Error("query", "err", err)
return []map[string]any{}
}
defer rows.Close()
cols, _ := rows.Columns()
out := []map[string]any{}
for rows.Next() {
vals := make([]any, len(cols))
ptrs := make([]any, len(cols))
for i := range vals {
ptrs[i] = &vals[i]
}
if err := rows.Scan(ptrs...); err != nil {
a.log.Error("scan", "err", err)
return out
}
m := make(map[string]any, len(cols))
for i, c := range cols {
if b, ok := vals[i].([]byte); ok {
vals[i] = string(b)
}
m[c] = vals[i]
}
out = append(out, m)
}
return out
}
func (a *API) fail(w http.ResponseWriter, err error) {
a.log.Error("request", "err", err)
http.Error(w, "internal error", http.StatusInternalServerError)
}
func writeJSON(w http.ResponseWriter, v any) {
w.Header().Set("Content-Type", "application/json; charset=utf-8")
enc := json.NewEncoder(w)
enc.SetEscapeHTML(false)
enc.Encode(v)
}
func atoi(s string, def int) int {
if n, err := strconv.Atoi(s); err == nil {
return n
}
return def
}
func clamp(n, lo, hi int) int { return max(lo, min(n, hi)) }