package storage

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"time"

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

// ErrTaxonomyNotFound is returned when a tag/document_type/correspondent
// lookup, update or delete does not match any row owned by the caller's
// tenant.
var ErrTaxonomyNotFound = errors.New("storage: taxonomy entity not found or not owned by tenant")

// ErrDuplicateTaxonomyName is returned when a tenant already has an entity
// of the same kind with the same name (UNIQUE(tenant_id, name)).
var ErrDuplicateTaxonomyName = errors.New("storage: entity with this name already exists for tenant")

// TaxonomyEntity is the shared shape of tags, document_types and
// correspondents — structurally identical (see lazy-splashing-puppy plan),
// kept as one Go struct/table-set with a `kind` selector rather than three
// near-duplicate types.
type TaxonomyEntity struct {
	ID             int64     `json:"id"`
	TenantID       int64     `json:"tenant_id"`
	Name           string    `json:"name"`
	Color          string    `json:"color,omitempty"`
	MatchAlgorithm string    `json:"match_algorithm"`
	MatchPattern   string    `json:"match_pattern,omitempty"`
	CaseSensitive  bool      `json:"case_sensitive"`
	BarcodeValue   string    `json:"barcode_value,omitempty"`
	CreatedAt      time.Time `json:"created_at"`
}

// TaxonomyEntityRequest holds create/update parameters for a taxonomy entity.
type TaxonomyEntityRequest struct {
	Name           string
	Color          string
	MatchAlgorithm string
	MatchPattern   string
	CaseSensitive  bool
	BarcodeValue   string
}

// taxonomyTable maps the three supported "kinds" to their table name. Kept
// as an allowlist so a caller can never inject an arbitrary table name.
func taxonomyTable(kind string) (string, error) {
	switch kind {
	case "tags":
		return "tags", nil
	case "document_types":
		return "document_types", nil
	case "correspondents":
		return "correspondents", nil
	default:
		return "", fmt.Errorf("storage: unknown taxonomy kind %q", kind)
	}
}

// initTaxonomySchema creates the tags/document_types/correspondents/
// document_tags tables plus the documents-table ALTERs (doc_type_id,
// correspondent_id, barcode_values). Idempotent, called from
// (*Store).initSchema. Documented (not executed) in
// migrations/005_taxonomy.sql.
func (s *Store) initTaxonomySchema(ctx context.Context) error {
	_, err := s.db.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS tags (
			id              BIGSERIAL PRIMARY KEY,
			tenant_id       BIGINT NOT NULL,
			name            TEXT NOT NULL,
			color           TEXT,
			match_algorithm TEXT NOT NULL DEFAULT 'none' CHECK (match_algorithm IN ('none','any','all','exact','regex','fuzzy')),
			match_pattern   TEXT,
			case_sensitive  BOOLEAN NOT NULL DEFAULT false,
			barcode_value   TEXT,
			created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
			UNIQUE(tenant_id, name)
		);
		CREATE TABLE IF NOT EXISTS document_types (
			id              BIGSERIAL PRIMARY KEY,
			tenant_id       BIGINT NOT NULL,
			name            TEXT NOT NULL,
			color           TEXT,
			match_algorithm TEXT NOT NULL DEFAULT 'none' CHECK (match_algorithm IN ('none','any','all','exact','regex','fuzzy')),
			match_pattern   TEXT,
			case_sensitive  BOOLEAN NOT NULL DEFAULT false,
			barcode_value   TEXT,
			created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
			UNIQUE(tenant_id, name)
		);
		CREATE TABLE IF NOT EXISTS correspondents (
			id              BIGSERIAL PRIMARY KEY,
			tenant_id       BIGINT NOT NULL,
			name            TEXT NOT NULL,
			color           TEXT,
			match_algorithm TEXT NOT NULL DEFAULT 'none' CHECK (match_algorithm IN ('none','any','all','exact','regex','fuzzy')),
			match_pattern   TEXT,
			case_sensitive  BOOLEAN NOT NULL DEFAULT false,
			barcode_value   TEXT,
			created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
			UNIQUE(tenant_id, name)
		);
		CREATE TABLE IF NOT EXISTS document_tags (
			document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
			tag_id      BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
			PRIMARY KEY (document_id, tag_id)
		);

		CREATE INDEX IF NOT EXISTS idx_tags_tenant ON tags(tenant_id);
		CREATE INDEX IF NOT EXISTS idx_document_types_tenant ON document_types(tenant_id);
		CREATE INDEX IF NOT EXISTS idx_correspondents_tenant ON correspondents(tenant_id);
		CREATE INDEX IF NOT EXISTS idx_document_tags_tag ON document_tags(tag_id);

		CREATE UNIQUE INDEX IF NOT EXISTS idx_tags_tenant_barcode ON tags(tenant_id, barcode_value) WHERE barcode_value IS NOT NULL;
		CREATE UNIQUE INDEX IF NOT EXISTS idx_document_types_tenant_barcode ON document_types(tenant_id, barcode_value) WHERE barcode_value IS NOT NULL;
		CREATE UNIQUE INDEX IF NOT EXISTS idx_correspondents_tenant_barcode ON correspondents(tenant_id, barcode_value) WHERE barcode_value IS NOT NULL;
	`)
	if err != nil {
		return fmt.Errorf("storage: create taxonomy tables: %w", err)
	}

	// documents-table ALTERs: doc_type_id/correspondent_id/barcode_values.
	// The old doc_type/correspondent free-text columns are NOT touched
	// (Bestandsschutz) — they stay in place, deprecated in favor of these.
	_, err = s.db.Exec(ctx, `
		ALTER TABLE documents ADD COLUMN IF NOT EXISTS doc_type_id BIGINT REFERENCES document_types(id);
		ALTER TABLE documents ADD COLUMN IF NOT EXISTS correspondent_id BIGINT REFERENCES correspondents(id);
		ALTER TABLE documents ADD COLUMN IF NOT EXISTS barcode_values JSONB;
	`)
	if err != nil {
		return fmt.Errorf("storage: alter documents table for taxonomy: %w", err)
	}
	return nil
}

func scanTaxonomyEntity(row interface {
	Scan(dest ...any) error
}) (*TaxonomyEntity, error) {
	var e TaxonomyEntity
	if err := row.Scan(&e.ID, &e.TenantID, &e.Name, &e.Color, &e.MatchAlgorithm, &e.MatchPattern, &e.CaseSensitive, &e.BarcodeValue, &e.CreatedAt); err != nil {
		return nil, err
	}
	return &e, nil
}

// CreateTaxonomyEntity inserts a new tag/document_type/correspondent row.
func (s *Store) CreateTaxonomyEntity(ctx context.Context, kind string, tenantID int64, req TaxonomyEntityRequest) (*TaxonomyEntity, error) {
	table, err := taxonomyTable(kind)
	if err != nil {
		return nil, err
	}
	algo := req.MatchAlgorithm
	if algo == "" {
		algo = "none"
	}
	row := s.db.QueryRow(ctx, fmt.Sprintf(`
		INSERT INTO %s (tenant_id, name, color, match_algorithm, match_pattern, case_sensitive, barcode_value)
		VALUES ($1, $2, $3, $4, $5, $6, $7)
		RETURNING id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
	`, table), tenantID, req.Name, nullIfEmpty(req.Color), algo, nullIfEmpty(req.MatchPattern), req.CaseSensitive, nullIfEmpty(req.BarcodeValue))
	e, err := scanTaxonomyEntity(row)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return nil, ErrDuplicateTaxonomyName
		}
		return nil, fmt.Errorf("storage: create %s: %w", kind, err)
	}
	return e, nil
}

// ListTaxonomyEntities returns all entities of the given kind for a tenant.
func (s *Store) ListTaxonomyEntities(ctx context.Context, kind string, tenantID int64) ([]TaxonomyEntity, error) {
	table, err := taxonomyTable(kind)
	if err != nil {
		return nil, err
	}
	rows, err := s.db.Query(ctx, fmt.Sprintf(`
		SELECT id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
		FROM %s WHERE tenant_id = $1 ORDER BY name ASC
	`, table), tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list %s: %w", kind, err)
	}
	defer rows.Close()

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

// ListActiveMatchers returns all entities of the given kind for a tenant
// whose match_algorithm is not 'none' — the candidate set the matching
// engine runs against on ingest.
func (s *Store) ListActiveMatchers(ctx context.Context, kind string, tenantID int64) ([]TaxonomyEntity, error) {
	table, err := taxonomyTable(kind)
	if err != nil {
		return nil, err
	}
	rows, err := s.db.Query(ctx, fmt.Sprintf(`
		SELECT id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
		FROM %s WHERE tenant_id = $1 AND match_algorithm != 'none'
	`, table), tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list active matchers %s: %w", kind, err)
	}
	defer rows.Close()

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

// GetTaxonomyEntityByBarcode looks up an entity by its barcode_value, scoped
// to tenant. Returns ErrTaxonomyNotFound if none match.
func (s *Store) GetTaxonomyEntityByBarcode(ctx context.Context, kind string, tenantID int64, barcodeValue string) (*TaxonomyEntity, error) {
	table, err := taxonomyTable(kind)
	if err != nil {
		return nil, err
	}
	row := s.db.QueryRow(ctx, fmt.Sprintf(`
		SELECT id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
		FROM %s WHERE tenant_id = $1 AND barcode_value = $2
	`, table), tenantID, barcodeValue)
	e, err := scanTaxonomyEntity(row)
	if err != nil {
		return nil, ErrTaxonomyNotFound
	}
	return e, nil
}

// UpdateTaxonomyEntity updates a tag/document_type/correspondent, scoped to
// tenant ownership.
func (s *Store) UpdateTaxonomyEntity(ctx context.Context, kind string, id, tenantID int64, req TaxonomyEntityRequest) (*TaxonomyEntity, error) {
	table, err := taxonomyTable(kind)
	if err != nil {
		return nil, err
	}
	algo := req.MatchAlgorithm
	if algo == "" {
		algo = "none"
	}
	row := s.db.QueryRow(ctx, fmt.Sprintf(`
		UPDATE %s SET name = $1, color = $2, match_algorithm = $3, match_pattern = $4, case_sensitive = $5, barcode_value = $6
		WHERE id = $7 AND tenant_id = $8
		RETURNING id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
	`, table), req.Name, nullIfEmpty(req.Color), algo, nullIfEmpty(req.MatchPattern), req.CaseSensitive, nullIfEmpty(req.BarcodeValue), id, tenantID)
	e, err := scanTaxonomyEntity(row)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return nil, ErrDuplicateTaxonomyName
		}
		return nil, ErrTaxonomyNotFound
	}
	return e, nil
}

// DeleteTaxonomyEntity deletes a tag/document_type/correspondent, scoped to
// tenant ownership.
func (s *Store) DeleteTaxonomyEntity(ctx context.Context, kind string, id, tenantID int64) error {
	table, err := taxonomyTable(kind)
	if err != nil {
		return err
	}
	tag, err := s.db.Exec(ctx, fmt.Sprintf(`DELETE FROM %s WHERE id = $1 AND tenant_id = $2`, table), id, tenantID)
	if err != nil {
		return fmt.Errorf("storage: delete %s: %w", kind, err)
	}
	if tag.RowsAffected() == 0 {
		return ErrTaxonomyNotFound
	}
	return nil
}

// AttachTag adds a document_tags row (idempotent — repeated attaches are a
// no-op via ON CONFLICT). Ownership of both document and tag by tenantID
// must be verified by the caller beforehand.
func (s *Store) AttachTag(ctx context.Context, documentID, tagID int64) error {
	_, err := s.db.Exec(ctx, `
		INSERT INTO document_tags (document_id, tag_id) VALUES ($1, $2)
		ON CONFLICT (document_id, tag_id) DO NOTHING
	`, documentID, tagID)
	if err != nil {
		return fmt.Errorf("storage: attach tag: %w", err)
	}
	// The document's tag set changed -> its tag_grants layer may differ.
	if err := s.RecomputeVisibility(ctx, documentID); err != nil {
		return fmt.Errorf("storage: recompute visibility after attach tag: %w", err)
	}
	return nil
}

// DetachTag removes a document_tags row.
func (s *Store) DetachTag(ctx context.Context, documentID, tagID int64) error {
	_, err := s.db.Exec(ctx, `DELETE FROM document_tags WHERE document_id = $1 AND tag_id = $2`, documentID, tagID)
	if err != nil {
		return fmt.Errorf("storage: detach tag: %w", err)
	}
	if err := s.RecomputeVisibility(ctx, documentID); err != nil {
		return fmt.Errorf("storage: recompute visibility after detach tag: %w", err)
	}
	return nil
}

// ListDocumentTags returns the tags attached to a document.
func (s *Store) ListDocumentTags(ctx context.Context, documentID, 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 document_tags dt ON dt.tag_id = t.id
		WHERE dt.document_id = $1 AND t.tenant_id = $2
		ORDER BY t.name ASC
	`, documentID, tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list document 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 document tag: %w", err)
		}
		out = append(out, *e)
	}
	return out, rows.Err()
}

// SetDocumentBarcodeValues stores the raw barcode payloads detected during
// ingest on documents.barcode_values (JSONB array), regardless of whether
// they matched any taxonomy entity — kept for GoBD-Nachvollziehbarkeit.
func (s *Store) SetDocumentBarcodeValues(ctx context.Context, documentID, tenantID int64, values []string) error {
	if len(values) == 0 {
		return nil
	}
	b, err := json.Marshal(values)
	if err != nil {
		return fmt.Errorf("storage: marshal barcode values: %w", err)
	}
	_, err = s.db.Exec(ctx, `UPDATE documents SET barcode_values = $1 WHERE id = $2 AND tenant_id = $3`, b, documentID, tenantID)
	if err != nil {
		return fmt.Errorf("storage: set document barcode values: %w", err)
	}
	return nil
}

// SetDocumentDocType sets documents.doc_type_id, scoped to tenant.
func (s *Store) SetDocumentDocType(ctx context.Context, documentID, tenantID, docTypeID int64) error {
	_, err := s.db.Exec(ctx, `UPDATE documents SET doc_type_id = $1 WHERE id = $2 AND tenant_id = $3`, docTypeID, documentID, tenantID)
	if err != nil {
		return fmt.Errorf("storage: set document doc_type_id: %w", err)
	}
	// The document's type changed -> its document_type_grants layer may differ.
	if err := s.RecomputeVisibility(ctx, documentID); err != nil {
		return fmt.Errorf("storage: recompute visibility after set doc_type: %w", err)
	}
	return nil
}

// SetDocumentCorrespondent sets documents.correspondent_id, scoped to tenant.
func (s *Store) SetDocumentCorrespondent(ctx context.Context, documentID, tenantID, correspondentID int64) error {
	_, err := s.db.Exec(ctx, `UPDATE documents SET correspondent_id = $1 WHERE id = $2 AND tenant_id = $3`, correspondentID, documentID, tenantID)
	if err != nil {
		return fmt.Errorf("storage: set document correspondent_id: %w", err)
	}
	// correspondent is not part of the ACL, so no RecomputeVisibility runs
	// here — sync the index directly. Best-effort.
	s.SyncIndex(ctx, documentID)
	return nil
}