package plan

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
)

type CreatePlanParams struct {
	ID             string
	WorkspaceID    string
	PlanKey        string
	DisplayName    string
	DescriptionMD  string
	Source         string
	Layout         []byte
	Actor          Actor
	IdempotencyKey string
	Now            time.Time
}

type ListPlansParams struct {
	WorkspaceID     string
	IncludeArchived bool
	State           string
	Search          string
	Limit           int
	BeforeUpdatedAt *time.Time
	BeforeID        string
}

type UpdatePlanMetadataParams struct {
	WorkspaceID      string
	PlanID           string
	ExpectedRevision int64
	DisplayName      *string
	DescriptionMD    *string
	Actor            Actor
	Now              time.Time
}

func (store *Store) CreatePlan(ctx context.Context, params CreatePlanParams) (CreateResult, error) {
	if err := store.Validate(); err != nil {
		return CreateResult{}, err
	}
	normalized, digest, err := normalizeCreatePlan(params)
	if err != nil {
		return CreateResult{}, err
	}
	var result CreateResult
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var txErr error
		result, txErr = createPlanInTx(ctx, tx, normalized, digest)
		return txErr
	})
	return result, err
}

// CreatePlan inserts or replays Plan metadata and its initial draft in the
// caller-owned transaction.
func (store *TxStore) CreatePlan(ctx context.Context, params CreatePlanParams) (CreateResult, error) {
	if err := store.validate(); err != nil {
		return CreateResult{}, err
	}
	normalized, digest, err := normalizeCreatePlan(params)
	if err != nil {
		return CreateResult{}, err
	}
	return createPlanInTx(ctx, store.tx, normalized, digest)
}

func createPlanInTx(ctx context.Context, tx coredb.Tx, normalized CreatePlanParams, digest string) (CreateResult, error) {
	existing, found, err := loadPlanByCreateIdempotency(ctx, tx, normalized.WorkspaceID, normalized.Actor, normalized.IdempotencyKey, true)
	if err != nil {
		return CreateResult{}, err
	}
	if found {
		if existing.CreateRequestDigest != digest {
			return CreateResult{}, IdempotencyConflictError{Operation: "create"}
		}
		draft, err := loadDraft(ctx, tx, existing.WorkspaceID, existing.ID, true)
		if err != nil {
			return CreateResult{}, err
		}
		return CreateResult{Plan: existing, Draft: draft, Replayed: true}, nil
	}

	planID := normalized.ID
	if planID == "" {
		planID, err = newID("pln", "record")
		if err != nil {
			return CreateResult{}, err
		}
	}
	now := utcOrNow(normalized.Now)
	scope, membershipBasis, scopeCreated, err := createWorkflowScope(
		ctx,
		tx,
		normalized.WorkspaceID,
		planID,
		normalized.Actor,
		now,
	)
	if err != nil {
		return CreateResult{}, err
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO workflow_plans (
    id, workspace_id, scope_id, plan_key, display_name, description_md, revision,
    current_version_id, default_version_id,
    create_idempotency_key, create_request_digest,
    created_by_member_id, created_by_actor_type, created_by_actor_id,
    updated_by_member_id, updated_by_actor_type, updated_by_actor_id,
    archived_by_member_id, archived_by_actor_type, archived_by_actor_id, archived_at,
    created_at, updated_at
) VALUES (
    $1, $2, $3, $4, $5, $6, 1,
    NULL, NULL,
    $7, $8,
    $9, $10, $11,
    $9, $10, $11,
    NULL, NULL, NULL, NULL,
    $12, $12
)
ON DUPLICATE KEY UPDATE id = id
`, planID, normalized.WorkspaceID, scope.ID, normalized.PlanKey, normalized.DisplayName, normalized.DescriptionMD,
		normalized.IdempotencyKey, digest,
		normalized.Actor.MemberID, normalized.Actor.Type, normalized.Actor.ID,
		now); err != nil {
		return CreateResult{}, fmt.Errorf("insert workflow plan: %w", mapWriteError(err))
	}

	created, found, err := loadPlanByCreateIdempotency(ctx, tx, normalized.WorkspaceID, normalized.Actor, normalized.IdempotencyKey, true)
	if err != nil {
		return CreateResult{}, err
	}
	if !found {
		return CreateResult{}, storeerr.Conflict("workflow plan key or id already exists")
	}
	if created.CreateRequestDigest != digest {
		return CreateResult{}, IdempotencyConflictError{Operation: "create"}
	}
	if created.ID != planID {
		if scopeCreated {
			if err := deleteCreatedWorkflowScope(ctx, tx, scope, planID); err != nil {
				return CreateResult{}, err
			}
		}
		draft, err := loadDraft(ctx, tx, created.WorkspaceID, created.ID, true)
		if err != nil {
			return CreateResult{}, err
		}
		return CreateResult{Plan: created, Draft: draft, Replayed: true}, nil
	}
	if created.ScopeID != scope.ID {
		return CreateResult{}, storeerr.Conflict("workflow Plan is bound to another canonical Scope")
	}
	if err := ensureWorkflowScopeCreatorMembership(ctx, tx, scope.ID, normalized.Actor.MemberID, membershipBasis, now); err != nil {
		return CreateResult{}, err
	}

	if _, err := tx.Exec(ctx, `
INSERT INTO workflow_plan_drafts (
    plan_id, workspace_id, source, layout, revision,
    updated_by_member_id, updated_by_actor_type, updated_by_actor_id,
    created_at, updated_at
) VALUES ($1, $2, $3, $4, 1, $5, $6, $7, $8, $8)
`, planID, normalized.WorkspaceID, normalized.Source, string(normalized.Layout),
		normalized.Actor.MemberID, normalized.Actor.Type, normalized.Actor.ID, now); err != nil {
		return CreateResult{}, fmt.Errorf("insert workflow plan draft: %w", mapWriteError(err))
	}
	draft, err := loadDraft(ctx, tx, normalized.WorkspaceID, planID, false)
	if err != nil {
		return CreateResult{}, err
	}
	return CreateResult{Plan: created, Draft: draft}, nil
}

func (store *Store) LoadPlan(ctx context.Context, workspaceID string, planID string) (Plan, error) {
	if err := store.Validate(); err != nil {
		return Plan{}, err
	}
	workspaceID, planID = trim(workspaceID), trim(planID)
	if workspaceID == "" || planID == "" {
		return Plan{}, storeerr.BadRequest("workspaceId and planId are required")
	}
	return loadPlan(ctx, store.db, workspaceID, planID, false)
}

// LoadPlanForUpdate locks one Plan through the caller-owned transaction.
func (store *TxStore) LoadPlanForUpdate(ctx context.Context, workspaceID string, planID string) (Plan, error) {
	if err := store.validate(); err != nil {
		return Plan{}, err
	}
	workspaceID, planID = trim(workspaceID), trim(planID)
	if workspaceID == "" || planID == "" {
		return Plan{}, storeerr.BadRequest("workspaceId and planId are required")
	}
	return loadPlan(ctx, store.tx, workspaceID, planID, true)
}

func (store *Store) ListPlans(ctx context.Context, params ListPlansParams) ([]Plan, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	params.WorkspaceID = trim(params.WorkspaceID)
	if params.WorkspaceID == "" {
		return nil, storeerr.BadRequest("workspaceId is required")
	}
	if params.BeforeUpdatedAt != nil && trim(params.BeforeID) == "" {
		return nil, storeerr.BadRequest("beforeId is required with beforeUpdatedAt")
	}
	query := planSelectSQL() + "\nWHERE p.workspace_id = $1\n"
	args := []any{params.WorkspaceID}
	switch strings.ToLower(trim(params.State)) {
	case "":
		if !params.IncludeArchived {
			query += "  AND p.archived_at IS NULL\n"
		}
	case "active":
		query += "  AND p.archived_at IS NULL\n"
	case "archived":
		query += "  AND p.archived_at IS NOT NULL\n"
	case "all":
		// no filter
	default:
		return nil, storeerr.BadRequest("invalid state filter; expected active, archived, or all")
	}
	if search := trim(params.Search); search != "" {
		args = append(args, "%"+strings.ToLower(search)+"%")
		query += fmt.Sprintf("  AND (LOWER(p.display_name) LIKE $%d OR LOWER(p.plan_key) LIKE $%d)\n", len(args), len(args))
	}
	if params.BeforeUpdatedAt != nil {
		args = append(args, params.BeforeUpdatedAt.UTC(), trim(params.BeforeID))
		query += fmt.Sprintf("  AND (p.updated_at < $%d OR (p.updated_at = $%d AND p.id < $%d))\n", len(args)-1, len(args)-1, len(args))
	}
	args = append(args, normalizeLimit(params.Limit))
	query += fmt.Sprintf("ORDER BY p.updated_at DESC, p.id DESC\nLIMIT $%d\n", len(args))
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list workflow plans: %w", mapWriteError(err))
	}
	defer rows.Close()
	plans := make([]Plan, 0)
	for rows.Next() {
		plan, err := scanPlan(rows)
		if err != nil {
			return nil, fmt.Errorf("scan workflow plan: %w", err)
		}
		plans = append(plans, plan)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workflow plans: %w", err)
	}
	return plans, nil
}

func (store *Store) UpdatePlanMetadata(ctx context.Context, params UpdatePlanMetadataParams) (Plan, error) {
	if err := store.Validate(); err != nil {
		return Plan{}, err
	}
	params.WorkspaceID, params.PlanID = trim(params.WorkspaceID), trim(params.PlanID)
	if params.WorkspaceID == "" || params.PlanID == "" || params.ExpectedRevision <= 0 {
		return Plan{}, storeerr.BadRequest("workspaceId, planId, and a positive expectedRevision are required")
	}
	if params.DisplayName == nil && params.DescriptionMD == nil {
		return Plan{}, storeerr.BadRequest("displayName or descriptionMd is required")
	}
	actor, err := validateActor(params.Actor)
	if err != nil {
		return Plan{}, err
	}
	displayNameSet, displayName := params.DisplayName != nil, ""
	if displayNameSet {
		displayName = trim(*params.DisplayName)
		if displayName == "" {
			return Plan{}, storeerr.BadRequest("displayName is required")
		}
	}
	descriptionSet, description := params.DescriptionMD != nil, ""
	if descriptionSet {
		description = trim(*params.DescriptionMD)
	}
	var updated Plan
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		current, err := loadPlan(ctx, tx, params.WorkspaceID, params.PlanID, true)
		if err != nil {
			return err
		}
		if err := requirePlanRevision(current, params.ExpectedRevision); err != nil {
			return err
		}
		if current.ArchivedAt != nil {
			return ErrArchived
		}
		result, err := tx.Exec(ctx, `
UPDATE workflow_plans
SET display_name = CASE WHEN $1 THEN $2 ELSE display_name END,
    description_md = CASE WHEN $3 THEN $4 ELSE description_md END,
    revision = revision + 1,
    updated_by_member_id = $5,
    updated_by_actor_type = $6,
    updated_by_actor_id = $7,
    updated_at = $8
WHERE workspace_id = $9 AND id = $10 AND revision = $11
`, displayNameSet, displayName, descriptionSet, description,
			actor.MemberID, actor.Type, actor.ID, utcOrNow(params.Now),
			params.WorkspaceID, params.PlanID, params.ExpectedRevision)
		if err != nil {
			return fmt.Errorf("update workflow plan metadata: %w", mapWriteError(err))
		}
		if result.RowsAffected() == 0 {
			return RevisionConflictError{Resource: "plan", Expected: params.ExpectedRevision, Actual: current.Revision}
		}
		updated, err = loadPlan(ctx, tx, params.WorkspaceID, params.PlanID, false)
		return err
	})
	return updated, err
}

func normalizeCreatePlan(params CreatePlanParams) (CreatePlanParams, string, error) {
	params.ID = trim(params.ID)
	params.WorkspaceID = trim(params.WorkspaceID)
	params.PlanKey = trim(params.PlanKey)
	params.DisplayName = trim(params.DisplayName)
	params.DescriptionMD = trim(params.DescriptionMD)
	params.IdempotencyKey = trim(params.IdempotencyKey)
	if params.WorkspaceID == "" || params.PlanKey == "" || params.DisplayName == "" || params.IdempotencyKey == "" {
		return CreatePlanParams{}, "", storeerr.BadRequest("workspaceId, planKey, displayName, and idempotencyKey are required")
	}
	actor, err := validateActor(params.Actor)
	if err != nil {
		return CreatePlanParams{}, "", err
	}
	params.Actor = actor
	digest := requestDigest(
		"create", params.WorkspaceID, params.PlanKey, params.DisplayName, params.DescriptionMD,
		params.Source, string(params.Layout), actor.Type, actor.ID,
	)
	return params, digest, nil
}

func requirePlanRevision(plan Plan, expected int64) error {
	if plan.Revision != expected {
		return RevisionConflictError{Resource: "plan", Expected: expected, Actual: plan.Revision}
	}
	return nil
}

func loadPlan(ctx context.Context, queryer coredb.QueryRower, workspaceID string, planID string, forUpdate bool) (Plan, error) {
	if forUpdate {
		return loadPlanForUpdate(ctx, queryer, planAggregateSelectSQL()+`
WHERE p.workspace_id = $1 AND p.id = $2
FOR UPDATE
`, workspaceID, planID)
	}
	query := planSelectSQL() + "\nWHERE p.workspace_id = $1 AND p.id = $2\n"
	plan, err := scanPlan(queryer.QueryRow(ctx, query, workspaceID, planID))
	if err != nil {
		return Plan{}, mapWriteError(err)
	}
	return plan, nil
}

func loadPlanByCreateIdempotency(ctx context.Context, queryer coredb.QueryRower, workspaceID string, actor Actor, idempotencyKey string, forUpdate bool) (Plan, bool, error) {
	if forUpdate {
		plan, err := loadPlanForUpdate(ctx, queryer, planAggregateSelectSQL()+`
WHERE p.workspace_id = $1
  AND p.created_by_actor_type = $2
  AND p.created_by_actor_id = $3
  AND p.create_idempotency_key = $4
FOR UPDATE
`, workspaceID, actor.Type, actor.ID, idempotencyKey)
		if errors.Is(err, storeerr.ErrNotFound) {
			return Plan{}, false, nil
		}
		if err != nil {
			return Plan{}, false, err
		}
		return plan, true, nil
	}

	query := planSelectSQL() + `
WHERE p.workspace_id = $1
  AND p.created_by_actor_type = $2
  AND p.created_by_actor_id = $3
  AND p.create_idempotency_key = $4
`
	plan, err := scanPlan(queryer.QueryRow(ctx, query, workspaceID, actor.Type, actor.ID, idempotencyKey))
	if coredb.IsNotFound(err) {
		return Plan{}, false, nil
	}
	if err != nil {
		return Plan{}, false, mapWriteError(err)
	}
	return plan, true, nil
}

// loadPlanForUpdate locks the aggregate row and snapshots every mutable
// parent field in that same current read. Immutable version pointers are IDs;
// their rows never need to be joined merely to decorate the Plan response.
func loadPlanForUpdate(ctx context.Context, queryer coredb.QueryRower, query string, args ...any) (Plan, error) {
	aggregate, err := scanPlanAggregate(queryer.QueryRow(ctx, query, args...))
	if err != nil {
		return Plan{}, mapWriteError(err)
	}
	return aggregate.finish(), nil
}

type scannedPlanAggregate struct {
	value             Plan
	currentID         sql.NullString
	defaultID         sql.NullString
	archivedActorType sql.NullString
	archivedActorID   sql.NullString
	archivedMemberID  sql.NullString
	archivedAt        sql.NullTime
}

func planAggregateSelectSQL() string {
	return `
SELECT p.id, p.workspace_id, p.scope_id, p.plan_key, p.display_name, p.description_md,
       p.revision, p.current_version_id, p.default_version_id,
       p.create_idempotency_key, p.create_request_digest,
       p.created_by_actor_type, p.created_by_actor_id, p.created_by_member_id, p.created_at,
       p.updated_by_actor_type, p.updated_by_actor_id, p.updated_by_member_id, p.updated_at,
       p.archived_by_actor_type, p.archived_by_actor_id, p.archived_by_member_id, p.archived_at
FROM workflow_plans p`
}

func scanPlanAggregate(row coredb.Row) (scannedPlanAggregate, error) {
	var aggregate scannedPlanAggregate
	if err := row.Scan(
		&aggregate.value.ID, &aggregate.value.WorkspaceID, &aggregate.value.ScopeID, &aggregate.value.PlanKey,
		&aggregate.value.DisplayName, &aggregate.value.DescriptionMD, &aggregate.value.Revision,
		&aggregate.currentID, &aggregate.defaultID,
		&aggregate.value.CreateIdempotencyKey, &aggregate.value.CreateRequestDigest,
		&aggregate.value.CreatedBy.Type, &aggregate.value.CreatedBy.ID, &aggregate.value.CreatedBy.MemberID, &aggregate.value.CreatedAt,
		&aggregate.value.UpdatedBy.Type, &aggregate.value.UpdatedBy.ID, &aggregate.value.UpdatedBy.MemberID, &aggregate.value.UpdatedAt,
		&aggregate.archivedActorType, &aggregate.archivedActorID, &aggregate.archivedMemberID, &aggregate.archivedAt,
	); err != nil {
		return scannedPlanAggregate{}, err
	}
	return aggregate, nil
}

func (aggregate scannedPlanAggregate) finish() Plan {
	plan := aggregate.value
	plan.State = PlanStateActive
	if aggregate.currentID.Valid {
		plan.CurrentVersion = &VersionRef{ID: aggregate.currentID.String}
	}
	if aggregate.defaultID.Valid {
		plan.DefaultVersion = &VersionRef{ID: aggregate.defaultID.String}
	}
	plan.ArchivedAt = ptrTime(aggregate.archivedAt)
	if plan.ArchivedAt != nil {
		plan.State = PlanStateArchived
		plan.ArchivedBy = &Actor{
			Type: aggregate.archivedActorType.String, ID: aggregate.archivedActorID.String, MemberID: aggregate.archivedMemberID.String,
		}
	}
	plan.CreatedAt = plan.CreatedAt.UTC()
	plan.UpdatedAt = plan.UpdatedAt.UTC()
	return plan
}

func planSelectSQL() string {
	return `
SELECT p.id, p.workspace_id, p.scope_id, p.plan_key, p.display_name, p.description_md,
       p.revision,
       p.current_version_id, p.default_version_id,
       p.create_idempotency_key, p.create_request_digest,
       p.created_by_actor_type, p.created_by_actor_id, p.created_by_member_id, p.created_at,
       p.updated_by_actor_type, p.updated_by_actor_id, p.updated_by_member_id, p.updated_at,
       p.archived_by_actor_type, p.archived_by_actor_id, p.archived_by_member_id, p.archived_at
FROM workflow_plans p
`
}

func scanPlan(row coredb.Row) (Plan, error) {
	var (
		plan              Plan
		currentID         sql.NullString
		defaultID         sql.NullString
		archivedActorType sql.NullString
		archivedActorID   sql.NullString
		archivedMemberID  sql.NullString
		archivedAt        sql.NullTime
	)
	if err := row.Scan(
		&plan.ID, &plan.WorkspaceID, &plan.ScopeID, &plan.PlanKey, &plan.DisplayName, &plan.DescriptionMD,
		&plan.Revision,
		&currentID, &defaultID,
		&plan.CreateIdempotencyKey, &plan.CreateRequestDigest,
		&plan.CreatedBy.Type, &plan.CreatedBy.ID, &plan.CreatedBy.MemberID, &plan.CreatedAt,
		&plan.UpdatedBy.Type, &plan.UpdatedBy.ID, &plan.UpdatedBy.MemberID, &plan.UpdatedAt,
		&archivedActorType, &archivedActorID, &archivedMemberID, &archivedAt,
	); err != nil {
		return Plan{}, err
	}
	plan.State = PlanStateActive
	if currentID.Valid {
		plan.CurrentVersion = &VersionRef{ID: currentID.String}
	}
	if defaultID.Valid {
		plan.DefaultVersion = &VersionRef{ID: defaultID.String}
	}
	plan.ArchivedAt = ptrTime(archivedAt)
	if plan.ArchivedAt != nil {
		plan.State = PlanStateArchived
		plan.ArchivedBy = &Actor{Type: archivedActorType.String, ID: archivedActorID.String, MemberID: archivedMemberID.String}
	}
	plan.CreatedAt = plan.CreatedAt.UTC()
	plan.UpdatedAt = plan.UpdatedAt.UTC()
	return plan, nil
}