package services

// PgTemplateStore — paliad's Postgres implementation of
// docforge.TemplateStore (t-paliad-349 slice 4). The carrier .docx bytes
// live in a bytea column (paliad.template_versions.carrier_blob); the
// stylemap is jsonb; slots are rows in paliad.template_slots. Versioning is
// snapshot-at-create: Create makes version 1 and pins it as current,
// AddVersion inserts the next version and re-points current.
//
// docforge owns the interface + the neutral types; this is the paliad-side
// data binding. No handler wires this yet — the authoring surface (slice 6)
// and generation-on-templates (slice 7) are the consumers.

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"

	"mgit.msbls.de/m/paliad/pkg/docforge"
)

// PgTemplateStore implements docforge.TemplateStore against Postgres.
type PgTemplateStore struct {
	db *sqlx.DB
}

// compile-time conformance.
var _ docforge.TemplateStore = (*PgTemplateStore)(nil)

// NewPgTemplateStore wires the store.
func NewPgTemplateStore(db *sqlx.DB) *PgTemplateStore {
	return &PgTemplateStore{db: db}
}

// templateMetaRow scans the catalog metadata + the current version number
// (via LEFT JOIN, 0 when no version pinned yet).
type templateMetaRow struct {
	ID           uuid.UUID  `db:"id"`
	Slug         *string    `db:"slug"`
	NameDE       string     `db:"name_de"`
	NameEN       string     `db:"name_en"`
	Kind         string     `db:"kind"`
	SourceFormat string     `db:"source_format"`
	Firm         *string    `db:"firm"`
	IsActive     bool       `db:"is_active"`
	Version      int        `db:"version"`
	VersionID    *uuid.UUID `db:"version_id"`
}

func (r templateMetaRow) toMeta() docforge.TemplateMeta {
	m := docforge.TemplateMeta{
		ID:           r.ID.String(),
		Slug:         derefString(r.Slug),
		NameDE:       r.NameDE,
		NameEN:       r.NameEN,
		Kind:         r.Kind,
		SourceFormat: r.SourceFormat,
		Firm:         derefString(r.Firm),
		IsActive:     r.IsActive,
		Version:      r.Version,
	}
	if r.VersionID != nil {
		m.VersionID = r.VersionID.String()
	}
	return m
}

const templateMetaColumns = `t.id, t.slug, t.name_de, t.name_en, t.kind,
                             t.source_format, t.firm, t.is_active,
                             COALESCE(v.version, 0) AS version,
                             v.id AS version_id`

const templateMetaFrom = `FROM paliad.templates t
                          LEFT JOIN paliad.template_versions v
                                 ON v.id = t.current_version_id`

// List returns catalog metadata for matching templates, without carrier
// bytes.
func (s *PgTemplateStore) List(ctx context.Context, f docforge.TemplateFilter) ([]docforge.TemplateMeta, error) {
	q := `SELECT ` + templateMetaColumns + ` ` + templateMetaFrom + ` WHERE 1=1`
	var args []any
	if f.ActiveOnly {
		q += ` AND t.is_active`
	}
	if f.Firm != "" {
		args = append(args, f.Firm)
		q += fmt.Sprintf(` AND (t.firm = $%d OR t.firm IS NULL)`, len(args))
	}
	if f.Kind != "" {
		args = append(args, f.Kind)
		q += fmt.Sprintf(` AND t.kind = $%d`, len(args))
	}
	q += ` ORDER BY COALESCE(t.firm, ''), t.name_de`

	var rows []templateMetaRow
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("list templates: %w", err)
	}
	out := make([]docforge.TemplateMeta, len(rows))
	for i := range rows {
		out[i] = rows[i].toMeta()
	}
	return out, nil
}

// Get resolves a template to its current version.
func (s *PgTemplateStore) Get(ctx context.Context, id string) (*docforge.Template, error) {
	tid, err := uuid.Parse(id)
	if err != nil {
		return nil, docforge.ErrTemplateNotFound
	}
	var meta templateMetaRow
	err = s.db.GetContext(ctx, &meta,
		`SELECT `+templateMetaColumns+` `+templateMetaFrom+` WHERE t.id = $1`, tid)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, docforge.ErrTemplateNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get template: %w", err)
	}
	tmpl := &docforge.Template{TemplateMeta: meta.toMeta()}
	if meta.Version == 0 {
		// No version pinned yet — return metadata only (carrier empty).
		return tmpl, nil
	}
	if err := s.loadCurrentVersionContent(ctx, tid, tmpl); err != nil {
		return nil, err
	}
	return tmpl, nil
}

// GetVersion resolves a template to a specific version id — the path a
// draft uses to render its pinned snapshot.
func (s *PgTemplateStore) GetVersion(ctx context.Context, versionID string) (*docforge.Template, error) {
	vid, err := uuid.Parse(versionID)
	if err != nil {
		return nil, docforge.ErrTemplateNotFound
	}
	var vr struct {
		TemplateID uuid.UUID `db:"template_id"`
		Version    int       `db:"version"`
		Carrier    []byte    `db:"carrier_blob"`
		Stylemap   []byte    `db:"stylemap"`
	}
	err = s.db.GetContext(ctx, &vr,
		`SELECT template_id, version, carrier_blob, stylemap
		   FROM paliad.template_versions WHERE id = $1`, vid)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, docforge.ErrTemplateNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get template version: %w", err)
	}
	var meta templateMetaRow
	err = s.db.GetContext(ctx, &meta,
		`SELECT t.id, t.slug, t.name_de, t.name_en, t.kind, t.source_format,
		        t.firm, t.is_active, $2 AS version
		   FROM paliad.templates t WHERE t.id = $1`, vr.TemplateID, vr.Version)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, docforge.ErrTemplateNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get template version meta: %w", err)
	}
	tmpl := &docforge.Template{TemplateMeta: meta.toMeta(), CarrierBytes: vr.Carrier}
	tmpl.VersionID = vid.String() // the resolved version is the one requested
	tmpl.Stylemap = decodeStylemap(vr.Stylemap)
	slots, err := s.loadSlots(ctx, vid)
	if err != nil {
		return nil, err
	}
	tmpl.Slots = slots
	return tmpl, nil
}

// loadCurrentVersionContent fills carrier + stylemap + slots from the
// template's current_version_id.
func (s *PgTemplateStore) loadCurrentVersionContent(ctx context.Context, templateID uuid.UUID, tmpl *docforge.Template) error {
	var vr struct {
		ID       uuid.UUID `db:"id"`
		Carrier  []byte    `db:"carrier_blob"`
		Stylemap []byte    `db:"stylemap"`
	}
	err := s.db.GetContext(ctx, &vr,
		`SELECT v.id, v.carrier_blob, v.stylemap
		   FROM paliad.template_versions v
		   JOIN paliad.templates t ON t.current_version_id = v.id
		  WHERE t.id = $1`, templateID)
	if errors.Is(err, sql.ErrNoRows) {
		return docforge.ErrTemplateNotFound
	}
	if err != nil {
		return fmt.Errorf("load current version: %w", err)
	}
	tmpl.CarrierBytes = vr.Carrier
	tmpl.Stylemap = decodeStylemap(vr.Stylemap)
	slots, err := s.loadSlots(ctx, vr.ID)
	if err != nil {
		return err
	}
	tmpl.Slots = slots
	return nil
}

// loadSlots returns the slots placed in a version, ordered.
func (s *PgTemplateStore) loadSlots(ctx context.Context, versionID uuid.UUID) ([]docforge.TemplateSlot, error) {
	var rows []struct {
		SlotKey    string  `db:"slot_key"`
		Anchor     string  `db:"anchor"`
		Label      *string `db:"label"`
		OrderIndex int     `db:"order_index"`
	}
	err := s.db.SelectContext(ctx, &rows,
		`SELECT slot_key, anchor, label, order_index
		   FROM paliad.template_slots
		  WHERE template_version_id = $1
		  ORDER BY order_index, slot_key`, versionID)
	if err != nil {
		return nil, fmt.Errorf("load template slots: %w", err)
	}
	out := make([]docforge.TemplateSlot, len(rows))
	for i, r := range rows {
		out[i] = docforge.TemplateSlot{
			Key:        r.SlotKey,
			Anchor:     r.Anchor,
			Label:      derefString(r.Label),
			OrderIndex: r.OrderIndex,
		}
	}
	return out, nil
}

// Create inserts a new template + its first version (version 1) and pins
// that version as current.
func (s *PgTemplateStore) Create(ctx context.Context, meta docforge.TemplateMetaInput, first docforge.TemplateVersionInput) (*docforge.Template, error) {
	createdBy, err := uuid.Parse(meta.CreatedBy)
	if err != nil {
		return nil, fmt.Errorf("create template: invalid created_by: %w", err)
	}
	kind := meta.Kind
	if kind == "" {
		kind = "submission"
	}
	format := meta.SourceFormat
	if format == "" {
		format = "docx"
	}

	var versionID uuid.UUID
	err = s.inTx(ctx, func(tx *sqlx.Tx) error {
		var templateID uuid.UUID
		if err := tx.GetContext(ctx, &templateID,
			`INSERT INTO paliad.templates
			     (slug, name_de, name_en, kind, source_format, firm, created_by)
			 VALUES (NULLIF($1, ''), $2, $3, $4, $5, NULLIF($6, ''), $7)
			 RETURNING id`,
			meta.Slug, meta.NameDE, meta.NameEN, kind, format, meta.Firm, createdBy); err != nil {
			return fmt.Errorf("insert template: %w", err)
		}
		var verr error
		versionID, verr = insertTemplateVersion(ctx, tx, templateID, 1, first)
		if verr != nil {
			return verr
		}
		if _, err := tx.ExecContext(ctx,
			`UPDATE paliad.templates SET current_version_id = $1, updated_at = now() WHERE id = $2`,
			versionID, templateID); err != nil {
			return fmt.Errorf("pin current version: %w", err)
		}
		return nil
	})
	if err != nil {
		return nil, err
	}
	return s.GetVersion(ctx, versionID.String())
}

// AddVersion inserts the next version for an existing template and
// re-points current_version to it.
func (s *PgTemplateStore) AddVersion(ctx context.Context, templateID string, v docforge.TemplateVersionInput) (*docforge.Template, error) {
	tid, err := uuid.Parse(templateID)
	if err != nil {
		return nil, docforge.ErrTemplateNotFound
	}

	var versionID uuid.UUID
	err = s.inTx(ctx, func(tx *sqlx.Tx) error {
		var exists bool
		if err := tx.GetContext(ctx, &exists,
			`SELECT EXISTS(SELECT 1 FROM paliad.templates WHERE id = $1)`, tid); err != nil {
			return fmt.Errorf("check template exists: %w", err)
		}
		if !exists {
			return docforge.ErrTemplateNotFound
		}
		var nextVersion int
		if err := tx.GetContext(ctx, &nextVersion,
			`SELECT COALESCE(MAX(version), 0) + 1 FROM paliad.template_versions WHERE template_id = $1`,
			tid); err != nil {
			return fmt.Errorf("next version: %w", err)
		}
		var verr error
		versionID, verr = insertTemplateVersion(ctx, tx, tid, nextVersion, v)
		if verr != nil {
			return verr
		}
		if _, err := tx.ExecContext(ctx,
			`UPDATE paliad.templates SET current_version_id = $1, updated_at = now() WHERE id = $2`,
			versionID, tid); err != nil {
			return fmt.Errorf("pin current version: %w", err)
		}
		return nil
	})
	if err != nil {
		return nil, err
	}
	return s.GetVersion(ctx, versionID.String())
}

// insertTemplateVersion inserts a version row + its slots inside tx and
// returns the new version id.
func insertTemplateVersion(ctx context.Context, tx *sqlx.Tx, templateID uuid.UUID, version int, v docforge.TemplateVersionInput) (uuid.UUID, error) {
	createdBy, err := uuid.Parse(v.CreatedBy)
	if err != nil {
		return uuid.Nil, fmt.Errorf("insert template version: invalid created_by: %w", err)
	}
	stylemap := v.Stylemap
	if stylemap == nil {
		stylemap = map[string]string{}
	}
	smJSON, err := json.Marshal(stylemap)
	if err != nil {
		return uuid.Nil, fmt.Errorf("marshal stylemap: %w", err)
	}
	var versionID uuid.UUID
	if err := tx.GetContext(ctx, &versionID,
		`INSERT INTO paliad.template_versions
		     (template_id, version, carrier_blob, stylemap, created_by)
		 VALUES ($1, $2, $3, $4, $5)
		 RETURNING id`,
		templateID, version, v.CarrierBytes, smJSON, createdBy); err != nil {
		return uuid.Nil, fmt.Errorf("insert template version: %w", err)
	}
	for i, slot := range v.Slots {
		order := slot.OrderIndex
		if order == 0 {
			order = i
		}
		if _, err := tx.ExecContext(ctx,
			`INSERT INTO paliad.template_slots
			     (template_version_id, slot_key, anchor, label, order_index)
			 VALUES ($1, $2, $3, NULLIF($4, ''), $5)`,
			versionID, slot.Key, slot.Anchor, slot.Label, order); err != nil {
			return uuid.Nil, fmt.Errorf("insert template slot %q: %w", slot.Key, err)
		}
	}
	return versionID, nil
}

// inTx runs fn inside a transaction, committing on success and rolling
// back on error or panic.
func (s *PgTemplateStore) inTx(ctx context.Context, fn func(tx *sqlx.Tx) error) error {
	tx, err := s.db.BeginTxx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		}
	}()
	if err := fn(tx); err != nil {
		_ = tx.Rollback()
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit tx: %w", err)
	}
	return nil
}

// decodeStylemap unmarshals the stylemap jsonb; an empty/invalid value
// yields an empty map so callers never deref nil.
func decodeStylemap(raw []byte) map[string]string {
	out := map[string]string{}
	if len(raw) == 0 {
		return out
	}
	_ = json.Unmarshal(raw, &out)
	return out
}
