package storage

import (
	"context"
	"errors"
	"fmt"
	"time"

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

// ErrClassificationTemplateNotFound is returned when a classification-template
// lookup, update or delete does not match any row owned by the caller's tenant.
var ErrClassificationTemplateNotFound = errors.New("storage: classification template not found or not owned by tenant")

// ErrDuplicateTemplateName is returned when a tenant already has a
// classification template with the same name (UNIQUE(tenant_id, name)).
var ErrDuplicateTemplateName = errors.New("storage: classification template with this name already exists for tenant")

// ClassificationTemplate is a tenant-scoped "Klassifizierungsvorlage": a named
// bundle of a document type, tags, custom-field default values and a retention
// period that can be applied to a document in one action. Deliberately NOT
// persistently coupled to any document (no template_id column on documents) so
// a later template edit can never retroactively change past documents — the
// application is recorded only via an audit-log entry (GoBD-Nachvollziehbarkeit).
type ClassificationTemplate struct {
	ID            int64                  `json:"id"`
	TenantID      int64                  `json:"tenant_id"`
	Name          string                 `json:"name"`
	Description   string                 `json:"description,omitempty"`
	DocTypeID     *int64                 `json:"doc_type_id,omitempty"`
	RetainYears   *int                   `json:"retain_years,omitempty"`
	Active        bool                   `json:"active"`
	CreatedBy     *int64                 `json:"created_by,omitempty"`
	CreatedAt     time.Time              `json:"created_at"`
	UpdatedAt     time.Time              `json:"updated_at"`
	Tags          []TaxonomyEntity       `json:"tags"`
	FieldDefaults []TemplateFieldDefault `json:"field_defaults"`

	// TitleTemplate is an optional Go text/template pattern used to derive the
	// document title when this template is applied (see
	// classification_templates_title.go). NULL/empty means "no template title"
	// — the tenant-wide default_title_template is tried next, and if that is
	// also empty the document's existing title is kept.
	TitleTemplate *string `json:"title_template,omitempty"`
}

// TemplateFieldDefault is one resolved custom-field default of a template,
// joined to its field definition. Exactly one value column is expected to be
// populated (matching the field's type). overwrite carries the template's
// intent to overwrite an already-set document value (only honoured on an
// explicit confirmed apply — see ApplyTemplate).
type TemplateFieldDefault struct {
	FieldID     int64      `json:"field_id"`
	Name        string     `json:"name"`
	Label       string     `json:"label"`
	FieldType   string     `json:"field_type"`
	ValueText   *string    `json:"value_text,omitempty"`
	ValueNumber *float64   `json:"value_number,omitempty"`
	ValueDate   *time.Time `json:"value_date,omitempty"`
	ValueBool   *bool      `json:"value_bool,omitempty"`
	Overwrite   bool       `json:"overwrite"`
}

// TemplateFieldDefaultInput is one supplied default in a bulk PUT. Exactly one
// of the value pointers is expected to be populated (matching the field type).
type TemplateFieldDefaultInput struct {
	FieldID     int64    `json:"field_id"`
	ValueText   *string  `json:"value_text,omitempty"`
	ValueNumber *float64 `json:"value_number,omitempty"`
	ValueDate   *string  `json:"value_date,omitempty"` // ISO date "2006-01-02"
	ValueBool   *bool    `json:"value_bool,omitempty"`
	Overwrite   bool     `json:"overwrite"`
}

// CreateTemplateRequest holds create parameters for a classification template.
type CreateTemplateRequest struct {
	Name          string
	Description   string
	DocTypeID     *int64
	RetainYears   *int
	Active        bool
	CreatedBy     *int64
	TitleTemplate *string
}

// UpdateTemplateRequest holds update parameters for a classification template.
type UpdateTemplateRequest struct {
	Name          string
	Description   string
	DocTypeID     *int64
	RetainYears   *int
	Active        bool
	TitleTemplate *string
}

// initClassificationTemplatesSchema creates the classification_templates /
// classification_template_tags / classification_template_field_defaults tables.
// Idempotent, called from (*Store).initSchema AFTER initTaxonomySchema and
// initCustomFieldsSchema (FK dependency on document_types / custom_field_defs).
// Documented (not executed) in migrations/011_classification_templates.sql.
func (s *Store) initClassificationTemplatesSchema(ctx context.Context) error {
	_, err := s.db.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS classification_templates (
			id                  BIGSERIAL PRIMARY KEY,
			tenant_id           BIGINT NOT NULL,
			name                TEXT NOT NULL,
			description         TEXT,
			doc_type_id         BIGINT REFERENCES document_types(id) ON DELETE SET NULL,
			retain_years        INT,
			active              BOOLEAN NOT NULL DEFAULT true,
			created_by          BIGINT,
			created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
			updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
			UNIQUE(tenant_id, name)
		);
		ALTER TABLE classification_templates ADD COLUMN IF NOT EXISTS title_template TEXT;
		CREATE INDEX IF NOT EXISTS idx_classification_templates_tenant ON classification_templates(tenant_id);
		CREATE INDEX IF NOT EXISTS idx_classification_templates_doc_type ON classification_templates(doc_type_id);

		CREATE TABLE IF NOT EXISTS classification_template_tags (
			template_id BIGINT NOT NULL REFERENCES classification_templates(id) ON DELETE CASCADE,
			tag_id      BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
			PRIMARY KEY (template_id, tag_id)
		);

		CREATE TABLE IF NOT EXISTS classification_template_field_defaults (
			template_id   BIGINT NOT NULL REFERENCES classification_templates(id) ON DELETE CASCADE,
			field_id      BIGINT NOT NULL REFERENCES custom_field_defs(id) ON DELETE CASCADE,
			value_text    TEXT,
			value_number  NUMERIC,
			value_date    DATE,
			value_bool    BOOLEAN,
			overwrite     BOOLEAN NOT NULL DEFAULT false,
			PRIMARY KEY (template_id, field_id)
		);
	`)
	if err != nil {
		return fmt.Errorf("storage: create classification templates tables: %w", err)
	}
	return nil
}

func scanClassificationTemplate(row interface {
	Scan(dest ...any) error
}) (*ClassificationTemplate, error) {
	var t ClassificationTemplate
	if err := row.Scan(&t.ID, &t.TenantID, &t.Name, &t.Description, &t.DocTypeID, &t.RetainYears,
		&t.Active, &t.CreatedBy, &t.CreatedAt, &t.UpdatedAt, &t.TitleTemplate); err != nil {
		return nil, err
	}
	t.Tags = make([]TaxonomyEntity, 0)
	t.FieldDefaults = make([]TemplateFieldDefault, 0)
	return &t, nil
}

const classificationTemplateCols = `id, tenant_id, name, COALESCE(description, ''), doc_type_id, retain_years, active, created_by, created_at, updated_at, title_template`

// CreateTemplate inserts a new classification template (without tags / field
// defaults — those are set via SetTemplateTags / SetTemplateFieldDefaults).
func (s *Store) CreateTemplate(ctx context.Context, tenantID int64, req CreateTemplateRequest) (*ClassificationTemplate, error) {
	row := s.db.QueryRow(ctx, `
		INSERT INTO classification_templates (tenant_id, name, description, doc_type_id, retain_years, active, created_by, title_template)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
		RETURNING `+classificationTemplateCols,
		tenantID, req.Name, nullIfEmpty(req.Description), req.DocTypeID, req.RetainYears, req.Active, req.CreatedBy, nullIfEmptyPtr(req.TitleTemplate))
	t, err := scanClassificationTemplate(row)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return nil, ErrDuplicateTemplateName
		}
		return nil, fmt.Errorf("storage: create classification template: %w", err)
	}
	return t, nil
}

// ListTemplates returns all classification templates for a tenant, optionally
// filtered by document type. Returns a non-nil (possibly empty) slice.
func (s *Store) ListTemplates(ctx context.Context, tenantID int64, docTypeID *int64) ([]ClassificationTemplate, error) {
	query := `SELECT ` + classificationTemplateCols + ` FROM classification_templates WHERE tenant_id = $1`
	args := []any{tenantID}
	if docTypeID != nil {
		query += ` AND doc_type_id = $2`
		args = append(args, *docTypeID)
	}
	query += ` ORDER BY name ASC`
	rows, err := s.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("storage: list classification templates: %w", err)
	}
	defer rows.Close()

	out := make([]ClassificationTemplate, 0)
	for rows.Next() {
		t, err := scanClassificationTemplate(rows)
		if err != nil {
			return nil, fmt.Errorf("storage: scan classification template: %w", err)
		}
		out = append(out, *t)
	}
	return out, rows.Err()
}

// GetTemplate returns one classification template resolved with its tags and
// custom-field defaults, scoped to tenant ownership.
func (s *Store) GetTemplate(ctx context.Context, id, tenantID int64) (*ClassificationTemplate, error) {
	row := s.db.QueryRow(ctx, `SELECT `+classificationTemplateCols+`
		FROM classification_templates WHERE id = $1 AND tenant_id = $2`, id, tenantID)
	t, err := scanClassificationTemplate(row)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrClassificationTemplateNotFound
		}
		return nil, fmt.Errorf("storage: get classification template: %w", err)
	}

	tags, err := s.listTemplateTags(ctx, id, tenantID)
	if err != nil {
		return nil, err
	}
	t.Tags = tags

	defaults, err := s.listTemplateFieldDefaults(ctx, id, tenantID)
	if err != nil {
		return nil, err
	}
	t.FieldDefaults = defaults
	return t, nil
}

// listTemplateTags returns the tags attached to a template, scoped to tenant.
func (s *Store) listTemplateTags(ctx context.Context, templateID, tenantID int64) ([]TaxonomyEntity, error) {
	rows, err := s.db.Query(ctx, `
		SELECT t.id, t.tenant_id, t.name, COALESCE(t.color, ''), t.match_algorithm, COALESCE(t.match_pattern, ''), t.case_sensitive, COALESCE(t.barcode_value, ''), t.created_at
		FROM tags t
		JOIN classification_template_tags ctt ON ctt.tag_id = t.id
		WHERE ctt.template_id = $1 AND t.tenant_id = $2
		ORDER BY t.name ASC
	`, templateID, tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list template tags: %w", err)
	}
	defer rows.Close()

	out := make([]TaxonomyEntity, 0)
	for rows.Next() {
		e, err := scanTaxonomyEntity(rows)
		if err != nil {
			return nil, fmt.Errorf("storage: scan template tag: %w", err)
		}
		out = append(out, *e)
	}
	return out, rows.Err()
}

// listTemplateFieldDefaults returns the custom-field defaults of a template,
// joined to their definitions, scoped to tenant.
func (s *Store) listTemplateFieldDefaults(ctx context.Context, templateID, tenantID int64) ([]TemplateFieldDefault, error) {
	rows, err := s.db.Query(ctx, `
		SELECT d.field_id, f.name, f.label, f.field_type,
			d.value_text, d.value_number, d.value_date, d.value_bool, d.overwrite
		FROM classification_template_field_defaults d
		JOIN custom_field_defs f ON f.id = d.field_id
		WHERE d.template_id = $1 AND f.tenant_id = $2
		ORDER BY f.name ASC
	`, templateID, tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list template field defaults: %w", err)
	}
	defer rows.Close()

	out := make([]TemplateFieldDefault, 0)
	for rows.Next() {
		var d TemplateFieldDefault
		if err := rows.Scan(&d.FieldID, &d.Name, &d.Label, &d.FieldType,
			&d.ValueText, &d.ValueNumber, &d.ValueDate, &d.ValueBool, &d.Overwrite); err != nil {
			return nil, fmt.Errorf("storage: scan template field default: %w", err)
		}
		out = append(out, d)
	}
	return out, rows.Err()
}

// UpdateTemplate updates a classification template's core attributes (not its
// tags / field defaults), scoped to tenant ownership.
func (s *Store) UpdateTemplate(ctx context.Context, id, tenantID int64, req UpdateTemplateRequest) error {
	tag, err := s.db.Exec(ctx, `
		UPDATE classification_templates
		SET name = $1, description = $2, doc_type_id = $3, retain_years = $4, active = $5, title_template = $6, updated_at = now()
		WHERE id = $7 AND tenant_id = $8
	`, req.Name, nullIfEmpty(req.Description), req.DocTypeID, req.RetainYears, req.Active, nullIfEmptyPtr(req.TitleTemplate), id, tenantID)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return ErrDuplicateTemplateName
		}
		return fmt.Errorf("storage: update classification template: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrClassificationTemplateNotFound
	}
	return nil
}

// DeleteTemplate deletes a classification template (cascades to its tags /
// field defaults), scoped to tenant ownership.
func (s *Store) DeleteTemplate(ctx context.Context, id, tenantID int64) error {
	tag, err := s.db.Exec(ctx, `DELETE FROM classification_templates WHERE id = $1 AND tenant_id = $2`, id, tenantID)
	if err != nil {
		return fmt.Errorf("storage: delete classification template: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrClassificationTemplateNotFound
	}
	return nil
}

// templateOwned returns true if the template belongs to the tenant.
func (s *Store) templateOwned(ctx context.Context, templateID, tenantID int64) (bool, error) {
	var ok bool
	err := s.db.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM classification_templates WHERE id = $1 AND tenant_id = $2)`, templateID, tenantID).Scan(&ok)
	if err != nil {
		return false, fmt.Errorf("storage: check template ownership: %w", err)
	}
	return ok, nil
}

// SetTemplateTags replaces the complete set of tags on a template (bulk PUT).
// All referenced tags must belong to the tenant. Scoped to tenant ownership of
// the template. Delete-all + insert in one transaction (SetDocumentTypeFields
// pattern).
func (s *Store) SetTemplateTags(ctx context.Context, templateID, tenantID int64, tagIDs []int64) error {
	owns, err := s.templateOwned(ctx, templateID, tenantID)
	if err != nil {
		return err
	}
	if !owns {
		return ErrClassificationTemplateNotFound
	}

	tx, err := s.db.Begin(ctx)
	if err != nil {
		return fmt.Errorf("storage: begin set template tags: %w", err)
	}
	defer tx.Rollback(ctx)

	if _, err := tx.Exec(ctx, `DELETE FROM classification_template_tags WHERE template_id = $1`, templateID); err != nil {
		return fmt.Errorf("storage: clear template tags: %w", err)
	}
	for _, tagID := range tagIDs {
		var ok bool
		if err := tx.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM tags WHERE id = $1 AND tenant_id = $2)`, tagID, tenantID).Scan(&ok); err != nil {
			return fmt.Errorf("storage: check tag ownership: %w", err)
		}
		if !ok {
			return fmt.Errorf("%w: tag_id %d", ErrTaxonomyNotFound, tagID)
		}
		if _, err := tx.Exec(ctx, `
			INSERT INTO classification_template_tags (template_id, tag_id) VALUES ($1, $2)
			ON CONFLICT (template_id, tag_id) DO NOTHING
		`, templateID, tagID); err != nil {
			return fmt.Errorf("storage: insert template tag: %w", err)
		}
	}
	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("storage: commit set template tags: %w", err)
	}
	return nil
}

// SetTemplateFieldDefaults replaces the complete set of custom-field defaults
// on a template (bulk PUT). All referenced fields must belong to the tenant and
// their supplied value is validated against the field type. Scoped to tenant
// ownership of the template. Delete-all + insert in one transaction.
func (s *Store) SetTemplateFieldDefaults(ctx context.Context, templateID, tenantID int64, defaults []TemplateFieldDefaultInput) error {
	owns, err := s.templateOwned(ctx, templateID, tenantID)
	if err != nil {
		return err
	}
	if !owns {
		return ErrClassificationTemplateNotFound
	}

	// Load field definitions for type resolution / validation.
	defs, err := s.ListCustomFieldDefs(ctx, tenantID)
	if err != nil {
		return err
	}
	defByID := make(map[int64]CustomFieldDef, len(defs))
	for _, d := range defs {
		defByID[d.ID] = d
	}

	tx, err := s.db.Begin(ctx)
	if err != nil {
		return fmt.Errorf("storage: begin set template field defaults: %w", err)
	}
	defer tx.Rollback(ctx)

	if _, err := tx.Exec(ctx, `DELETE FROM classification_template_field_defaults WHERE template_id = $1`, templateID); err != nil {
		return fmt.Errorf("storage: clear template field defaults: %w", err)
	}
	for _, in := range defaults {
		def, ok := defByID[in.FieldID]
		if !ok {
			return fmt.Errorf("%w: field_id %d", ErrCustomFieldNotFound, in.FieldID)
		}
		var (
			text   *string
			number *float64
			date   *time.Time
			bl     *bool
		)
		switch def.FieldType {
		case "text":
			text = in.ValueText
		case "enum":
			if in.ValueText != nil && *in.ValueText != "" {
				if len(def.EnumOptions) > 0 && !containsString(def.EnumOptions, *in.ValueText) {
					return fmt.Errorf("storage: value %q not in enum options for field %q", *in.ValueText, def.Name)
				}
			}
			text = in.ValueText
		case "number", "monetary":
			number = in.ValueNumber
		case "date":
			if in.ValueDate != nil && *in.ValueDate != "" {
				parsed, err := time.Parse("2006-01-02", *in.ValueDate)
				if err != nil {
					return fmt.Errorf("storage: invalid date %q for field %q: %w", *in.ValueDate, def.Name, err)
				}
				date = &parsed
			}
		case "boolean":
			bl = in.ValueBool
		}
		if _, err := tx.Exec(ctx, `
			INSERT INTO classification_template_field_defaults (template_id, field_id, value_text, value_number, value_date, value_bool, overwrite)
			VALUES ($1, $2, $3, $4, $5, $6, $7)
			ON CONFLICT (template_id, field_id) DO UPDATE
			SET value_text = EXCLUDED.value_text, value_number = EXCLUDED.value_number,
				value_date = EXCLUDED.value_date, value_bool = EXCLUDED.value_bool, overwrite = EXCLUDED.overwrite
		`, templateID, in.FieldID, text, number, date, bl, in.Overwrite); err != nil {
			return fmt.Errorf("storage: insert template field default: %w", err)
		}
	}
	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("storage: commit set template field defaults: %w", err)
	}
	return nil
}