package repo

import (
	"context"
	"fmt"
	"strings"

	"github.com/krow/krow-backend/go-api/internal/authctx"
	"github.com/krow/krow-backend/go-api/internal/domain"
)

// DefinitionListParams holds the query parameters for listing authored definitions.
type DefinitionListParams struct {
	Visibility   string
	Status       string
	DefinitionID string
	Sort         string
	Desc         bool
	Limit        int
	Offset       int
}

// AgentInsertInput holds the fields to insert into agent_definitions.
type AgentInsertInput struct {
	DefinitionID string
	OrgID        string
	Visibility   string
	OwnerUserID  *string
	CreatedBy    *string
	Markdown     string
	Status       string
	Version      int
	Name         string
	Description  string
	Pages        []string
}

// AgentUpdateInput holds the fields to update in agent_definitions.
type AgentUpdateInput struct {
	DefinitionID *string
	Name         *string
	Description  *string
	Status       *string
	Version      *int
	Pages        []string
	Markdown     *string
	Visibility   *string
	OwnerUserID  *string
}

// SkillInsertInput holds the fields to insert into skill_definitions.
type SkillInsertInput struct {
	DefinitionID string
	OrgID        string
	Visibility   string
	OwnerUserID  *string
	CreatedBy    *string
	Markdown     string
	Status       string
	Name         string
	Description  string
	Pages        []string
}

// SkillUpdateInput holds the fields to update in skill_definitions.
type SkillUpdateInput struct {
	DefinitionID *string
	Name         *string
	Description  *string
	Status       *string
	Pages        []string
	Markdown     *string
	Visibility   *string
	OwnerUserID  *string
}

const agentDefinitionColumns = `id::text AS id,
	definition_id,
	org_id::text AS org_id,
	visibility,
	owner_user_id::text AS owner_user_id,
	created_by::text AS created_by,
	markdown,
	status,
	version,
	name,
	description,
	pages,
	to_char(created_date AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS created_date,
	to_char(updated_date AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS updated_date`

const skillDefinitionColumns = `id::text AS id,
	definition_id,
	org_id::text AS org_id,
	visibility,
	owner_user_id::text AS owner_user_id,
	created_by::text AS created_by,
	markdown,
	status,
	name,
	description,
	pages,
	to_char(created_date AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS created_date,
	to_char(updated_date AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS updated_date`

// DefinitionsRepo handles persistence for agent_definitions and skill_definitions.
type DefinitionsRepo struct {
	db Querier
}

// NewDefinitionsRepo builds a repository over a pool or transaction.
func NewDefinitionsRepo(db Querier) *DefinitionsRepo {
	return &DefinitionsRepo{db: db}
}

/* ── Agents ─────────────────────────────────────────────────────────────── */

// ListAgents lists agent definitions matching the criteria with tenant isolation.
func (r *DefinitionsRepo) ListAgents(ctx context.Context, ident authctx.Identity, p DefinitionListParams) ([]domain.Record, int, error) {
	b := &builder{}
	b.where = append(b.where, fmt.Sprintf("org_id = %s::uuid", b.add(ident.OrgID)))

	switch p.Visibility {
	case "personal":
		b.where = append(b.where, fmt.Sprintf("(visibility = 'personal' AND owner_user_id = %s::uuid)", b.add(ident.UserID)))
	case "organization":
		b.where = append(b.where, "visibility = 'organization'")
	default:
		b.where = append(b.where, fmt.Sprintf("(visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = %s::uuid))", b.add(ident.UserID)))
	}

	if p.Status != "" {
		b.where = append(b.where, fmt.Sprintf("status = %s::text", b.add(p.Status)))
	}
	if p.DefinitionID != "" {
		b.where = append(b.where, fmt.Sprintf("definition_id = %s::text", b.add(p.DefinitionID)))
	}

	countSQL := "SELECT count(*)::int FROM agent_definitions" + b.clause()
	var total int
	if err := r.db.QueryRow(ctx, countSQL, b.args...).Scan(&total); err != nil {
		return nil, 0, translate(err)
	}

	sortCol := "created_date"
	switch p.Sort {
	case "created_date", "updated_date", "name", "definition_id", "status", "version":
		sortCol = p.Sort
	}
	dir := "ASC"
	if p.Desc {
		dir = "DESC"
	}
	orderClause := fmt.Sprintf(" ORDER BY %s %s, id %s", sortCol, dir, dir)

	limit := p.Limit
	if limit <= 0 {
		limit = 100
	}
	offset := p.Offset
	if offset < 0 {
		offset = 0
	}

	limitClause := fmt.Sprintf(" LIMIT %s OFFSET %s", b.add(limit), b.add(offset))
	querySQL := "SELECT " + agentDefinitionColumns + " FROM agent_definitions" + b.clause() + orderClause + limitClause

	rows, err := r.db.Query(ctx, querySQL, b.args...)
	if err != nil {
		return nil, 0, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, 0, translate(err)
	}
	return records, total, nil
}

// GetAgent retrieves one agent definition by id within the caller's tenant and ownership scope.
func (r *DefinitionsRepo) GetAgent(ctx context.Context, ident authctx.Identity, id string) (domain.Record, error) {
	q := fmt.Sprintf(
		`SELECT %s FROM agent_definitions
		  WHERE id = $1::uuid
		    AND org_id = $2::uuid
		    AND (visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = $3::uuid))
		  LIMIT 1`,
		agentDefinitionColumns)

	rows, err := r.db.Query(ctx, q, id, ident.OrgID, ident.UserID)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, nil
	}
	return records[0], nil
}

// GetAgentByDefinitionID retrieves an agent definition by definition_id within the caller's scope,
// giving personal definitions precedence over organization definitions when shadowed.
func (r *DefinitionsRepo) GetAgentByDefinitionID(ctx context.Context, ident authctx.Identity, defID string) (domain.Record, error) {
	q := fmt.Sprintf(
		`SELECT %s FROM agent_definitions
		  WHERE definition_id = $1::text
		    AND org_id = $2::uuid
		    AND (visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = $3::uuid))
		  ORDER BY CASE WHEN visibility = 'personal' THEN 1 ELSE 2 END
		  LIMIT 1`,
		agentDefinitionColumns)

	rows, err := r.db.Query(ctx, q, defID, ident.OrgID, ident.UserID)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, nil
	}
	return records[0], nil
}

// InsertAgent writes an agent definition and returns the stored row.
func (r *DefinitionsRepo) InsertAgent(ctx context.Context, ident authctx.Identity, in AgentInsertInput) (domain.Record, error) {
	q := fmt.Sprintf(
		`INSERT INTO agent_definitions (
			definition_id, org_id, visibility, owner_user_id, created_by,
			markdown, status, version, name, description, pages
		) VALUES (
			$1::text, $2::uuid, $3::text, $4::uuid, $5::uuid,
			$6::text, $7::text, $8::integer, $9::text, $10::text, $11::text[]
		) RETURNING %s`, agentDefinitionColumns)

	pages := in.Pages
	if pages == nil {
		pages = []string{}
	}

	rows, err := r.db.Query(ctx, q,
		in.DefinitionID, in.OrgID, in.Visibility, in.OwnerUserID, in.CreatedBy,
		in.Markdown, in.Status, in.Version, in.Name, in.Description, pages)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, fmt.Errorf("insert agent_definitions returned no row")
	}
	return records[0], nil
}

// UpdateAgent updates fields on an agent definition and returns the full updated record.
func (r *DefinitionsRepo) UpdateAgent(ctx context.Context, ident authctx.Identity, id string, in AgentUpdateInput) (domain.Record, error) {
	b := &builder{}
	sets := []string{"updated_date = now()"}

	if in.DefinitionID != nil {
		sets = append(sets, fmt.Sprintf("definition_id = %s::text", b.add(*in.DefinitionID)))
	}
	if in.Name != nil {
		sets = append(sets, fmt.Sprintf("name = %s::text", b.add(*in.Name)))
	}
	if in.Description != nil {
		sets = append(sets, fmt.Sprintf("description = %s::text", b.add(*in.Description)))
	}
	if in.Status != nil {
		sets = append(sets, fmt.Sprintf("status = %s::text", b.add(*in.Status)))
	}
	if in.Version != nil {
		sets = append(sets, fmt.Sprintf("version = %s::integer", b.add(*in.Version)))
	}
	if in.Pages != nil {
		sets = append(sets, fmt.Sprintf("pages = %s::text[]", b.add(in.Pages)))
	}
	if in.Markdown != nil {
		sets = append(sets, fmt.Sprintf("markdown = %s::text", b.add(*in.Markdown)))
	}
	if in.Visibility != nil {
		sets = append(sets, fmt.Sprintf("visibility = %s::text", b.add(*in.Visibility)))
		sets = append(sets, fmt.Sprintf("owner_user_id = %s::uuid", b.add(in.OwnerUserID)))
	}

	b.where = append(b.where, fmt.Sprintf("id = %s::uuid", b.add(id)))
	b.where = append(b.where, fmt.Sprintf("org_id = %s::uuid", b.add(ident.OrgID)))
	b.where = append(b.where, fmt.Sprintf("(visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = %s::uuid))", b.add(ident.UserID)))

	q := fmt.Sprintf("UPDATE agent_definitions SET %s%s RETURNING %s",
		strings.Join(sets, ", "), b.clause(), agentDefinitionColumns)

	rows, err := r.db.Query(ctx, q, b.args...)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, nil
	}
	return records[0], nil
}

// DeleteAgent removes an agent definition matching id and access scope.
func (r *DefinitionsRepo) DeleteAgent(ctx context.Context, ident authctx.Identity, id string) (int64, error) {
	q := `DELETE FROM agent_definitions
	       WHERE id = $1::uuid
	         AND org_id = $2::uuid
	         AND (visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = $3::uuid))`

	tag, err := r.db.Exec(ctx, q, id, ident.OrgID, ident.UserID)
	if err != nil {
		return 0, translate(err)
	}
	return tag.RowsAffected(), nil
}

/* ── Skills ─────────────────────────────────────────────────────────────── */

// ListSkills lists skill definitions matching the criteria with tenant isolation.
func (r *DefinitionsRepo) ListSkills(ctx context.Context, ident authctx.Identity, p DefinitionListParams) ([]domain.Record, int, error) {
	b := &builder{}
	b.where = append(b.where, fmt.Sprintf("org_id = %s::uuid", b.add(ident.OrgID)))

	switch p.Visibility {
	case "personal":
		b.where = append(b.where, fmt.Sprintf("(visibility = 'personal' AND owner_user_id = %s::uuid)", b.add(ident.UserID)))
	case "organization":
		b.where = append(b.where, "visibility = 'organization'")
	default:
		b.where = append(b.where, fmt.Sprintf("(visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = %s::uuid))", b.add(ident.UserID)))
	}

	if p.Status != "" {
		b.where = append(b.where, fmt.Sprintf("status = %s::text", b.add(p.Status)))
	}
	if p.DefinitionID != "" {
		b.where = append(b.where, fmt.Sprintf("definition_id = %s::text", b.add(p.DefinitionID)))
	}

	countSQL := "SELECT count(*)::int FROM skill_definitions" + b.clause()
	var total int
	if err := r.db.QueryRow(ctx, countSQL, b.args...).Scan(&total); err != nil {
		return nil, 0, translate(err)
	}

	sortCol := "created_date"
	switch p.Sort {
	case "created_date", "updated_date", "name", "definition_id", "status":
		sortCol = p.Sort
	}
	dir := "ASC"
	if p.Desc {
		dir = "DESC"
	}
	orderClause := fmt.Sprintf(" ORDER BY %s %s, id %s", sortCol, dir, dir)

	limit := p.Limit
	if limit <= 0 {
		limit = 100
	}
	offset := p.Offset
	if offset < 0 {
		offset = 0
	}

	limitClause := fmt.Sprintf(" LIMIT %s OFFSET %s", b.add(limit), b.add(offset))
	querySQL := "SELECT " + skillDefinitionColumns + " FROM skill_definitions" + b.clause() + orderClause + limitClause

	rows, err := r.db.Query(ctx, querySQL, b.args...)
	if err != nil {
		return nil, 0, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, 0, translate(err)
	}
	return records, total, nil
}

// GetSkill retrieves one skill definition by id within the caller's tenant and ownership scope.
func (r *DefinitionsRepo) GetSkill(ctx context.Context, ident authctx.Identity, id string) (domain.Record, error) {
	q := fmt.Sprintf(
		`SELECT %s FROM skill_definitions
		  WHERE id = $1::uuid
		    AND org_id = $2::uuid
		    AND (visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = $3::uuid))
		  LIMIT 1`,
		skillDefinitionColumns)

	rows, err := r.db.Query(ctx, q, id, ident.OrgID, ident.UserID)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, nil
	}
	return records[0], nil
}

// GetSkillByDefinitionID retrieves a skill definition by definition_id within the caller's scope,
// giving personal definitions precedence over organization definitions when shadowed.
func (r *DefinitionsRepo) GetSkillByDefinitionID(ctx context.Context, ident authctx.Identity, defID string) (domain.Record, error) {
	q := fmt.Sprintf(
		`SELECT %s FROM skill_definitions
		  WHERE definition_id = $1::text
		    AND org_id = $2::uuid
		    AND (visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = $3::uuid))
		  ORDER BY CASE WHEN visibility = 'personal' THEN 1 ELSE 2 END
		  LIMIT 1`,
		skillDefinitionColumns)

	rows, err := r.db.Query(ctx, q, defID, ident.OrgID, ident.UserID)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, nil
	}
	return records[0], nil
}

// InsertSkill writes a skill definition and returns the stored row.
func (r *DefinitionsRepo) InsertSkill(ctx context.Context, ident authctx.Identity, in SkillInsertInput) (domain.Record, error) {
	q := fmt.Sprintf(
		`INSERT INTO skill_definitions (
			definition_id, org_id, visibility, owner_user_id, created_by,
			markdown, status, name, description, pages
		) VALUES (
			$1::text, $2::uuid, $3::text, $4::uuid, $5::uuid,
			$6::text, $7::text, $8::text, $9::text, $10::text[]
		) RETURNING %s`, skillDefinitionColumns)

	pages := in.Pages
	if pages == nil {
		pages = []string{}
	}

	rows, err := r.db.Query(ctx, q,
		in.DefinitionID, in.OrgID, in.Visibility, in.OwnerUserID, in.CreatedBy,
		in.Markdown, in.Status, in.Name, in.Description, pages)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, fmt.Errorf("insert skill_definitions returned no row")
	}
	return records[0], nil
}

// UpdateSkill updates fields on a skill definition and returns the full updated record.
func (r *DefinitionsRepo) UpdateSkill(ctx context.Context, ident authctx.Identity, id string, in SkillUpdateInput) (domain.Record, error) {
	b := &builder{}
	sets := []string{"updated_date = now()"}

	if in.DefinitionID != nil {
		sets = append(sets, fmt.Sprintf("definition_id = %s::text", b.add(*in.DefinitionID)))
	}
	if in.Name != nil {
		sets = append(sets, fmt.Sprintf("name = %s::text", b.add(*in.Name)))
	}
	if in.Description != nil {
		sets = append(sets, fmt.Sprintf("description = %s::text", b.add(*in.Description)))
	}
	if in.Status != nil {
		sets = append(sets, fmt.Sprintf("status = %s::text", b.add(*in.Status)))
	}
	if in.Pages != nil {
		sets = append(sets, fmt.Sprintf("pages = %s::text[]", b.add(in.Pages)))
	}
	if in.Markdown != nil {
		sets = append(sets, fmt.Sprintf("markdown = %s::text", b.add(*in.Markdown)))
	}
	if in.Visibility != nil {
		sets = append(sets, fmt.Sprintf("visibility = %s::text", b.add(*in.Visibility)))
		sets = append(sets, fmt.Sprintf("owner_user_id = %s::uuid", b.add(in.OwnerUserID)))
	}

	b.where = append(b.where, fmt.Sprintf("id = %s::uuid", b.add(id)))
	b.where = append(b.where, fmt.Sprintf("org_id = %s::uuid", b.add(ident.OrgID)))
	b.where = append(b.where, fmt.Sprintf("(visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = %s::uuid))", b.add(ident.UserID)))

	q := fmt.Sprintf("UPDATE skill_definitions SET %s%s RETURNING %s",
		strings.Join(sets, ", "), b.clause(), skillDefinitionColumns)

	rows, err := r.db.Query(ctx, q, b.args...)
	if err != nil {
		return nil, translate(err)
	}
	defer rows.Close()

	records, err := collect(rows)
	if err != nil {
		return nil, translate(err)
	}
	if len(records) == 0 {
		return nil, nil
	}
	return records[0], nil
}

// DeleteSkill removes a skill definition matching id and access scope.
func (r *DefinitionsRepo) DeleteSkill(ctx context.Context, ident authctx.Identity, id string) (int64, error) {
	q := `DELETE FROM skill_definitions
	       WHERE id = $1::uuid
	         AND org_id = $2::uuid
	         AND (visibility = 'organization' OR (visibility = 'personal' AND owner_user_id = $3::uuid))`

	tag, err := r.db.Exec(ctx, q, id, ident.OrgID, ident.UserID)
	if err != nil {
		return 0, translate(err)
	}
	return tag.RowsAffected(), nil
}