package services

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

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

	"mgit.msbls.de/m/paliad/internal/checklists"
	"mgit.msbls.de/m/paliad/internal/models"
)

// ChecklistCatalogService unifies the static Go template catalog
// (internal/checklists/templates.go) and the user-authored DB catalog
// (paliad.checklists, mig 114) into a single read facade.
//
// Slug uniqueness is enforced across both spaces at write time by
// ChecklistTemplateService (authored slugs get a 'u-' prefix and we
// reject collisions with static slugs). Catalog lookups prefer static
// templates on collision so a stray DB row never shadows curated
// content — see Find().
type ChecklistCatalogService struct {
	db          *sqlx.DB
	staticSlugs map[string]bool
}

// NewChecklistCatalogService wires the service and pre-computes the
// static-slug set used for collision detection at write + read time.
func NewChecklistCatalogService(db *sqlx.DB) *ChecklistCatalogService {
	set := make(map[string]bool, len(checklists.Templates))
	for _, t := range checklists.Templates {
		set[t.Slug] = true
	}
	return &ChecklistCatalogService{db: db, staticSlugs: set}
}

// CatalogEntry is one unified entry — either a static template or an
// authored DB row. Origin identifies the source so the UI can render
// provenance ("Erstellt von <author>" for authored, plain title for
// static).
type CatalogEntry struct {
	Slug             string              `json:"slug"`
	Origin           string              `json:"origin"` // "static" | "authored"
	Visibility       string              `json:"visibility"`
	OwnerID          *uuid.UUID          `json:"owner_id,omitempty"`
	OwnerEmail       string              `json:"owner_email,omitempty"`
	OwnerDisplayName string              `json:"owner_display_name,omitempty"`
	// Version of the underlying row. 1 for static templates (they
	// re-version implicitly with the deploy that ships them); the live
	// counter from paliad.checklists.version for authored rows.
	Version  int                 `json:"version"`
	Template checklists.Template `json:"template"`
}

// IsStaticSlug reports whether the given slug names a curated static
// template. Called by ChecklistTemplateService.Create to reject author
// slugs that would shadow a curated entry.
func (s *ChecklistCatalogService) IsStaticSlug(slug string) bool {
	return s.staticSlugs[slug]
}

// ListVisible returns every catalog entry the caller can see — every
// static template (always visible) plus every authored DB row that
// passes paliad.can_see_checklist via RLS.
//
// Ordering: static templates first in their definition order, then
// authored rows alphabetised by title.
func (s *ChecklistCatalogService) ListVisible(ctx context.Context, userID uuid.UUID) ([]CatalogEntry, error) {
	out := make([]CatalogEntry, 0, len(checklists.Templates))
	for _, t := range checklists.Templates {
		out = append(out, CatalogEntry{
			Slug:       t.Slug,
			Origin:     "static",
			Visibility: "static",
			Version:    1,
			Template:   t,
		})
	}

	if s.db == nil {
		return out, nil
	}

	rows, err := s.fetchVisibleAuthored(ctx, userID)
	if err != nil {
		return nil, err
	}

	sort.SliceStable(rows, func(i, j int) bool {
		return strings.ToLower(rows[i].Title) < strings.ToLower(rows[j].Title)
	})

	for _, r := range rows {
		// Skip the row if it collides with a static slug — static wins.
		if s.staticSlugs[r.Slug] {
			continue
		}
		tpl, err := s.rowToTemplate(r)
		if err != nil {
			return nil, err
		}
		ownerID := r.OwnerID
		out = append(out, CatalogEntry{
			Slug:             r.Slug,
			Origin:           "authored",
			Visibility:       r.Visibility,
			OwnerID:          &ownerID,
			OwnerEmail:       r.OwnerEmail,
			OwnerDisplayName: r.OwnerDisplayName,
			Version:          r.Version,
			Template:         tpl,
		})
	}
	return out, nil
}

// Find resolves a slug to a single catalog entry, applying visibility
// (RLS for authored rows; static always visible). Returns ErrNotVisible
// if the slug is unknown or the caller can't see the authored row.
func (s *ChecklistCatalogService) Find(ctx context.Context, userID uuid.UUID, slug string) (*CatalogEntry, error) {
	if t, ok := checklists.Find(slug); ok {
		return &CatalogEntry{
			Slug:       t.Slug,
			Origin:     "static",
			Visibility: "static",
			Version:    1,
			Template:   t,
		}, nil
	}
	if s.db == nil {
		return nil, ErrNotVisible
	}
	row, err := s.fetchAuthoredBySlug(ctx, userID, slug)
	if err != nil {
		return nil, err
	}
	if row == nil {
		return nil, ErrNotVisible
	}
	tpl, err := s.rowToTemplate(*row)
	if err != nil {
		return nil, err
	}
	ownerID := row.OwnerID
	return &CatalogEntry{
		Slug:             row.Slug,
		Origin:           "authored",
		Visibility:       row.Visibility,
		OwnerID:          &ownerID,
		OwnerEmail:       row.OwnerEmail,
		OwnerDisplayName: row.OwnerDisplayName,
		Version:          row.Version,
		Template:         tpl,
	}, nil
}

// SnapshotBody returns the template body as JSONB suitable for storing
// in paliad.checklist_instances.template_snapshot. For static templates
// we marshal the full Template struct; for authored rows we return the
// body column directly (it already has the right shape — groups[]).
func (s *ChecklistCatalogService) SnapshotBody(ctx context.Context, userID uuid.UUID, slug string) (json.RawMessage, error) {
	entry, err := s.Find(ctx, userID, slug)
	if err != nil {
		return nil, err
	}
	body, err := json.Marshal(entry.Template)
	if err != nil {
		return nil, fmt.Errorf("snapshot marshal: %w", err)
	}
	return body, nil
}

// --- internals ------------------------------------------------------------

const authoredWithOwnerSelect = `SELECT c.id, c.slug, c.owner_id, c.title, c.description,
	c.regime, c.court, c.reference, c.deadline, c.lang, c.body, c.visibility,
	c.promoted_at, c.promoted_by, c.version, c.created_at, c.updated_at,
	u.email        AS owner_email,
	u.display_name AS owner_display_name
	FROM paliad.checklists c
	JOIN paliad.users u ON u.id = c.owner_id`

// checklistVisibilityPredicate mirrors paliad.can_see_checklist for the
// service-role connection (which bypasses RLS). Covers all 6 branches
// from mig 115: owner + firm/global + global_admin + 4 share-recipient
// kinds (user / office / partner_unit / project).
//
// One positional arg ($userArg) for the caller UUID. Reused several
// times across the branches; that's fine — Postgres positional
// placeholders evaluate the arg once per reference, no extra param
// binding overhead.
func checklistVisibilityPredicate(alias string, userArg int) string {
	return fmt.Sprintf(`(%s.owner_id = $%d
		OR %s.visibility IN ('firm', 'global')
		OR EXISTS (
			SELECT 1 FROM paliad.users u
			 WHERE u.id = $%d AND u.global_role = 'global_admin'
		)
		OR EXISTS (
			SELECT 1 FROM paliad.checklist_shares s
			 WHERE s.checklist_id = %s.id
			   AND s.recipient_kind = 'user'
			   AND s.recipient_user_id = $%d
		)
		OR EXISTS (
			SELECT 1
			  FROM paliad.checklist_shares s
			  JOIN paliad.users u ON u.id = $%d
			 WHERE s.checklist_id = %s.id
			   AND s.recipient_kind = 'office'
			   AND (s.recipient_office = u.office
			        OR s.recipient_office = ANY(u.additional_offices))
		)
		OR EXISTS (
			SELECT 1
			  FROM paliad.checklist_shares s
			  JOIN paliad.partner_unit_members pum
			    ON pum.partner_unit_id = s.recipient_partner_unit_id
			   AND pum.user_id = $%d
			 WHERE s.checklist_id = %s.id
			   AND s.recipient_kind = 'partner_unit'
		)
		OR EXISTS (
			-- Share-to-project resolution: inline ltree walk over
			-- paliad.projects.path because paliad.can_see_project
			-- reads auth.uid() which is NULL on the service-role
			-- connection (same pattern as visibility.go).
			SELECT 1
			  FROM paliad.checklist_shares s
			  JOIN paliad.projects      p
			    ON p.id = s.recipient_project_id
			  JOIN paliad.project_teams pt
			    ON pt.user_id = $%d
			   AND pt.project_id = ANY(CAST(string_to_array(p.path, '.') AS uuid[]))
			 WHERE s.checklist_id = %s.id
			   AND s.recipient_kind = 'project'
		))`,
		alias, userArg,            // owner
		alias,                      // firm/global visibility col
		userArg,                    // global_admin
		alias, userArg,             // share: user
		userArg, alias,             // share: office
		userArg, alias,             // share: partner_unit
		userArg, alias,             // share: project (ltree walk)
	)
}

func (s *ChecklistCatalogService) fetchVisibleAuthored(ctx context.Context, userID uuid.UUID) ([]models.ChecklistWithOwner, error) {
	rows := []models.ChecklistWithOwner{}
	q := authoredWithOwnerSelect + `
		WHERE ` + checklistVisibilityPredicate("c", 1) + `
		ORDER BY c.title ASC`
	if err := s.db.SelectContext(ctx, &rows, q, userID); err != nil {
		return nil, fmt.Errorf("list authored checklists: %w", err)
	}
	return rows, nil
}

func (s *ChecklistCatalogService) fetchAuthoredBySlug(ctx context.Context, userID uuid.UUID, slug string) (*models.ChecklistWithOwner, error) {
	var row models.ChecklistWithOwner
	q := authoredWithOwnerSelect + `
		WHERE c.slug = $2
		  AND ` + checklistVisibilityPredicate("c", 1)
	err := s.db.GetContext(ctx, &row, q, userID, slug)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("fetch authored checklist: %w", err)
	}
	return &row, nil
}

func (s *ChecklistCatalogService) rowToTemplate(row models.ChecklistWithOwner) (checklists.Template, error) {
	// body jsonb holds { "groups": [...] }. Unmarshal into a thin local
	// shape because the full checklists.Template has DE/EN sibling
	// fields the author only fills one side of.
	var bodyShape struct {
		Groups []checklists.Group `json:"groups"`
	}
	if err := json.Unmarshal(row.Body, &bodyShape); err != nil {
		return checklists.Template{}, fmt.Errorf("unmarshal authored body for %s: %w", row.Slug, err)
	}
	t := checklists.Template{
		Slug:        row.Slug,
		Regime:      row.Regime,
		Groups:      bodyShape.Groups,
		ReferenceDE: row.Reference,
		ReferenceEN: row.Reference,
		DeadlineDE:  row.Deadline,
		DeadlineEN:  row.Deadline,
		CourtDE:     row.Court,
		CourtEN:     row.Court,
	}
	// Author picks one language per template — surface their title /
	// description on both sides so the existing bilingual frontend
	// renders without a special-case for authored entries.
	t.TitleDE = row.Title
	t.TitleEN = row.Title
	t.DescriptionDE = row.Description
	t.DescriptionEN = row.Description
	return t, nil
}
