File size: 13,648 Bytes
2a6941d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
// 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)) }