package ontology

import (
	"context"

	"github.com/jackc/pgx/v5/pgxpool"
)

// DBWriter upserts a VocabRecord into the global ontology_terms table. It runs as the migrator role (the
// only role granted write on the global reference tables). The upsert key is (source_name, source_record_id),
// so a rerun refreshes a term in place and the command is idempotent.
type DBWriter struct{ pool *pgxpool.Pool }

// NewDBWriter builds a DBWriter over a migrator pool.
func NewDBWriter(pool *pgxpool.Pool) *DBWriter { return &DBWriter{pool: pool} }

// Upsert writes one record, refreshing it on a (source_name, source_record_id) conflict.
func (w *DBWriter) Upsert(ctx context.Context, rec VocabRecord, rawObjectKey, rawSHA256 string) error {
	_, err := w.pool.Exec(ctx, `
INSERT INTO ontology_terms
  (source_name, source_record_id, term_kind, term, code, category, description, raw_object_key, raw_sha256, fetched_at)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9, now())
ON CONFLICT (source_name, source_record_id) DO UPDATE SET
  term_kind=EXCLUDED.term_kind, term=EXCLUDED.term, code=EXCLUDED.code, category=EXCLUDED.category,
  description=EXCLUDED.description, raw_object_key=EXCLUDED.raw_object_key, raw_sha256=EXCLUDED.raw_sha256,
  synced_at=now()`,
		rec.SourceName, rec.SourceRecordID, string(rec.Kind), rec.Term, rec.Code, rec.Category, rec.Description,
		rawObjectKey, rawSHA256)
	return err
}