package teamworkflow

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

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

type rowScanner interface {
	Scan(...any) error
}

const teamWorkflowColumns = `
tw.id, tw.workspace_id, tw.team_id, tw.plan_id, tw.revision,
tw.authoring_state, tw.source_scope_id, tw.source_message_id,
tw.requested_by_member_id, tw.proposal_sequence, tw.resolution_sequence,
tw.last_error_code, tw.last_error_message, tw.activated_at,
tw.deleted_by_member_id, tw.deleted_at, tw.created_at, tw.updated_at`

const viewSelectSQL = `SELECT
` + teamWorkflowColumns + `,
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_member_id, p.created_by_actor_type, p.created_by_actor_id,
p.updated_by_member_id, p.updated_by_actor_type, p.updated_by_actor_id,
p.archived_by_member_id, p.archived_by_actor_type, p.archived_by_actor_id,
p.archived_at, p.created_at, p.updated_at
FROM workspace_team_workflows tw
LEFT JOIN workflow_plans p
  ON p.workspace_id = tw.workspace_id AND p.id = tw.plan_id`

func scanTeamWorkflow(scanner rowScanner) (TeamWorkflow, error) {
	var (
		value                  TeamWorkflow
		planID                 sql.NullString
		activatedAt, deletedAt sql.NullTime
		deletedByMemberID      sql.NullString
	)
	if err := scanner.Scan(
		&value.ID, &value.WorkspaceID, &value.TeamID, &planID, &value.Revision,
		&value.AuthoringState, &value.SourceScopeID, &value.SourceMessageID,
		&value.RequestedByMemberID, &value.ProposalSequence, &value.ResolutionSequence,
		&value.LastErrorCode, &value.LastErrorMessage, &activatedAt,
		&deletedByMemberID, &deletedAt, &value.CreatedAt, &value.UpdatedAt,
	); err != nil {
		return TeamWorkflow{}, err
	}
	value.PlanID = ptrString(planID)
	value.ActivatedAt = ptrTime(activatedAt)
	value.DeletedByMemberID = ptrString(deletedByMemberID)
	value.DeletedAt = ptrTime(deletedAt)
	value.CreatedAt = value.CreatedAt.UTC()
	value.UpdatedAt = value.UpdatedAt.UTC()
	return value, nil
}

func scanView(scanner rowScanner) (View, error) {
	var (
		value                  TeamWorkflow
		teamPlanID             sql.NullString
		activatedAt, deletedAt sql.NullTime
		deletedByMemberID      sql.NullString

		planID, planWorkspaceID, planScopeID, planKey, displayName sql.NullString
		description, currentVersionID, defaultVersionID            sql.NullString
		createKey, createDigest                                    sql.NullString
		createdMemberID, createdActorType, createdActorID          sql.NullString
		updatedMemberID, updatedActorType, updatedActorID          sql.NullString
		archivedMemberID, archivedActorType, archivedActorID       sql.NullString
		planRevision                                               sql.NullInt64
		planArchivedAt, planCreatedAt, planUpdatedAt               sql.NullTime
	)
	if err := scanner.Scan(
		&value.ID, &value.WorkspaceID, &value.TeamID, &teamPlanID, &value.Revision,
		&value.AuthoringState, &value.SourceScopeID, &value.SourceMessageID,
		&value.RequestedByMemberID, &value.ProposalSequence, &value.ResolutionSequence,
		&value.LastErrorCode, &value.LastErrorMessage, &activatedAt,
		&deletedByMemberID, &deletedAt, &value.CreatedAt, &value.UpdatedAt,
		&planID, &planWorkspaceID, &planScopeID, &planKey, &displayName, &description, &planRevision,
		&currentVersionID, &defaultVersionID,
		&createKey, &createDigest,
		&createdMemberID, &createdActorType, &createdActorID,
		&updatedMemberID, &updatedActorType, &updatedActorID,
		&archivedMemberID, &archivedActorType, &archivedActorID,
		&planArchivedAt, &planCreatedAt, &planUpdatedAt,
	); err != nil {
		return View{}, err
	}
	value.PlanID = ptrString(teamPlanID)
	value.ActivatedAt = ptrTime(activatedAt)
	value.DeletedByMemberID = ptrString(deletedByMemberID)
	value.DeletedAt = ptrTime(deletedAt)
	value.CreatedAt = value.CreatedAt.UTC()
	value.UpdatedAt = value.UpdatedAt.UTC()
	view := View{TeamWorkflow: value}
	if planID.Valid {
		plan := planstore.Plan{
			ID: planID.String, WorkspaceID: planWorkspaceID.String, ScopeID: planScopeID.String,
			PlanKey: planKey.String, DisplayName: displayName.String,
			DescriptionMD: description.String, Revision: planRevision.Int64,
			CreateIdempotencyKey: createKey.String, CreateRequestDigest: createDigest.String,
			CreatedBy: planstore.Actor{Type: createdActorType.String, ID: createdActorID.String, MemberID: createdMemberID.String},
			UpdatedBy: planstore.Actor{Type: updatedActorType.String, ID: updatedActorID.String, MemberID: updatedMemberID.String},
			CreatedAt: planCreatedAt.Time.UTC(), UpdatedAt: planUpdatedAt.Time.UTC(),
			ArchivedAt: ptrTime(planArchivedAt), State: planstore.PlanStateActive,
		}
		if currentVersionID.Valid {
			plan.CurrentVersion = &planstore.VersionRef{ID: currentVersionID.String}
		}
		if defaultVersionID.Valid {
			plan.DefaultVersion = &planstore.VersionRef{ID: defaultVersionID.String}
		}
		if plan.ArchivedAt != nil {
			plan.State = planstore.PlanStateArchived
			plan.ArchivedBy = &planstore.Actor{Type: archivedActorType.String, ID: archivedActorID.String, MemberID: archivedMemberID.String}
		}
		view.Plan = &plan
	}
	return view, nil
}

func loadTeamWorkflow(ctx context.Context, queryer coredb.QueryRower, workspaceID, teamID, id string, forUpdate, includeDeleted bool) (TeamWorkflow, error) {
	query := "SELECT " + teamWorkflowColumns + " FROM workspace_team_workflows tw WHERE tw.workspace_id = $1 AND tw.team_id = $2 AND tw.id = $3"
	if !includeDeleted {
		query += " AND tw.deleted_at IS NULL"
	}
	if forUpdate {
		query += " FOR UPDATE"
	}
	value, err := scanTeamWorkflow(queryer.QueryRow(ctx, query, workspaceID, teamID, id))
	if err != nil {
		return TeamWorkflow{}, mapWriteError(err)
	}
	return value, nil
}

func loadBySourceMessage(ctx context.Context, queryer coredb.QueryRower, workspaceID, teamID, messageID string, forUpdate bool) (TeamWorkflow, bool, error) {
	query := "SELECT " + teamWorkflowColumns + " FROM workspace_team_workflows tw WHERE tw.workspace_id = $1 AND tw.team_id = $2 AND tw.source_message_id = $3"
	if forUpdate {
		query += " FOR UPDATE"
	}
	value, err := scanTeamWorkflow(queryer.QueryRow(ctx, query, workspaceID, teamID, messageID))
	if errors.Is(err, sql.ErrNoRows) || errors.Is(err, storeerr.ErrNotFound) {
		return TeamWorkflow{}, false, nil
	}
	if err != nil {
		return TeamWorkflow{}, false, fmt.Errorf("load Team Workflow by source command id: %w", err)
	}
	return value, true, nil
}

func (store *Store) Load(ctx context.Context, workspaceID, teamID, id string, includeDeleted bool) (View, error) {
	if err := store.Validate(); err != nil {
		return View{}, err
	}
	workspaceID, teamID, id = trim(workspaceID), trim(teamID), trim(id)
	if workspaceID == "" || teamID == "" || id == "" {
		return View{}, storeerr.BadRequest("workspaceId, teamId, and teamWorkflowId are required")
	}
	query := viewSelectSQL + "\nWHERE tw.workspace_id = $1 AND tw.team_id = $2 AND tw.id = $3"
	if !includeDeleted {
		query += " AND tw.deleted_at IS NULL"
	}
	value, err := scanView(store.db.QueryRow(ctx, query, workspaceID, teamID, id))
	if err != nil {
		return View{}, mapWriteError(err)
	}
	return value, nil
}

// LoadByPlan resolves the Team Workflow relation without scanning a Team's
// list. The composite Plan key is unique per workspace, while teamID remains
// in the predicate so callers cannot cross a nested Team boundary.
func (store *Store) LoadByPlan(ctx context.Context, workspaceID, teamID, planID string, includeDeleted bool) (View, error) {
	if err := store.Validate(); err != nil {
		return View{}, err
	}
	workspaceID, teamID = trim(workspaceID), trim(teamID)
	planID = trim(planID)
	if workspaceID == "" || teamID == "" || planID == "" {
		return View{}, storeerr.BadRequest("workspaceId, teamId, and planId are required")
	}
	query := viewSelectSQL + "\nWHERE tw.workspace_id = $1 AND tw.team_id = $2 AND tw.plan_id = $3"
	if !includeDeleted {
		query += " AND tw.deleted_at IS NULL"
	}
	value, err := scanView(store.db.QueryRow(ctx, query, workspaceID, teamID, planID))
	if err != nil {
		return View{}, mapWriteError(err)
	}
	return value, nil
}

// Load locks one Team Workflow relation for a caller-owned transaction.
func (store *TxStore) Load(ctx context.Context, workspaceID, teamID, id string, includeDeleted bool) (TeamWorkflow, error) {
	if err := store.validate(); err != nil {
		return TeamWorkflow{}, err
	}
	workspaceID, teamID, id, err := normalizeIdentity(workspaceID, teamID, id)
	if err != nil {
		return TeamWorkflow{}, err
	}
	return loadTeamWorkflow(ctx, store.tx, workspaceID, teamID, id, true, includeDeleted)
}

// LoadByPlan locks the exact Team Workflow relation associated with planID.
// It exists for cross-store orchestration that already has a Team and Plan
// identity but not the relation id.
func (store *TxStore) LoadByPlan(ctx context.Context, workspaceID, teamID, planID string, includeDeleted bool) (TeamWorkflow, error) {
	if err := store.validate(); err != nil {
		return TeamWorkflow{}, err
	}
	workspaceID, teamID = trim(workspaceID), trim(teamID)
	planID = trim(planID)
	if workspaceID == "" || teamID == "" || planID == "" {
		return TeamWorkflow{}, storeerr.BadRequest("workspaceId, teamId, and planId are required")
	}
	query := "SELECT " + teamWorkflowColumns + " FROM workspace_team_workflows tw WHERE tw.workspace_id = $1 AND tw.team_id = $2 AND tw.plan_id = $3"
	if !includeDeleted {
		query += " AND tw.deleted_at IS NULL"
	}
	query += " FOR UPDATE"
	value, err := scanTeamWorkflow(store.tx.QueryRow(ctx, query, workspaceID, teamID, planID))
	if err != nil {
		return TeamWorkflow{}, mapWriteError(err)
	}
	return value, nil
}

// LoadCurrentForTeam locks the Team's current non-deleted Workflow relation.
// Creation callers must lock the parent Team row first so an empty result and
// the following insert are serialized without requiring a schema change.
func (store *TxStore) LoadCurrentForTeam(ctx context.Context, workspaceID, teamID string) (TeamWorkflow, bool, error) {
	if err := store.validate(); err != nil {
		return TeamWorkflow{}, false, err
	}
	workspaceID, teamID = trim(workspaceID), trim(teamID)
	if workspaceID == "" || teamID == "" {
		return TeamWorkflow{}, false, storeerr.BadRequest("workspaceId and teamId are required")
	}
	query := "SELECT " + teamWorkflowColumns + `
FROM workspace_team_workflows tw
WHERE tw.workspace_id = $1 AND tw.team_id = $2 AND tw.deleted_at IS NULL
ORDER BY tw.updated_at DESC, tw.id DESC
LIMIT 1
FOR UPDATE`
	value, err := scanTeamWorkflow(store.tx.QueryRow(ctx, query, workspaceID, teamID))
	if errors.Is(err, sql.ErrNoRows) || errors.Is(err, storeerr.ErrNotFound) {
		return TeamWorkflow{}, false, nil
	}
	if err != nil {
		return TeamWorkflow{}, false, fmt.Errorf("load current Team Workflow: %w", err)
	}
	return value, true, nil
}

func (store *Store) List(ctx context.Context, params ListParams) ([]View, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	params.WorkspaceID, params.TeamID = trim(params.WorkspaceID), trim(params.TeamID)
	params.State, params.Search, params.BeforeID = strings.ToLower(trim(params.State)), trim(params.Search), trim(params.BeforeID)
	if params.WorkspaceID == "" || params.TeamID == "" {
		return nil, storeerr.BadRequest("workspaceId and teamId are required")
	}
	if (params.BeforeUpdatedAt == nil) != (params.BeforeID == "") {
		return nil, storeerr.BadRequest("beforeUpdatedAt and beforeId must be provided together")
	}
	query := viewSelectSQL + "\nWHERE tw.workspace_id = $1 AND tw.team_id = $2\n"
	args := []any{params.WorkspaceID, params.TeamID}
	var err error
	query, args, err = appendStateFilter(query, args, params.State)
	if err != nil {
		return nil, err
	}
	if params.Search != "" {
		args = append(args, "%"+strings.ToLower(params.Search)+"%")
		placeholder := len(args)
		query += fmt.Sprintf("  AND (LOWER(tw.id) LIKE $%d OR LOWER(COALESCE(p.display_name, '')) LIKE $%d OR LOWER(COALESCE(p.plan_key, '')) LIKE $%d)\n", placeholder, placeholder, placeholder)
	}
	if params.BeforeUpdatedAt != nil {
		args = append(args, params.BeforeUpdatedAt.UTC(), params.BeforeID)
		query += fmt.Sprintf("  AND (tw.updated_at < $%d OR (tw.updated_at = $%d AND tw.id < $%d))\n", len(args)-1, len(args)-1, len(args))
	}
	args = append(args, normalizedListLimit(params.Limit))
	query += fmt.Sprintf("ORDER BY tw.updated_at DESC, tw.id DESC\nLIMIT $%d", len(args))
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list Team Workflows: %w", err)
	}
	defer rows.Close()
	items := make([]View, 0)
	for rows.Next() {
		item, scanErr := scanView(rows)
		if scanErr != nil {
			return nil, fmt.Errorf("scan Team Workflow list: %w", scanErr)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate Team Workflow list: %w", err)
	}
	return items, nil
}

func appendStateFilter(query string, args []any, state string) (string, []any, error) {
	switch state {
	case "":
		query += "  AND tw.deleted_at IS NULL\n"
	case "all":
	case StatusDeleted:
		query += "  AND tw.deleted_at IS NOT NULL\n"
	case StatusActive:
		query += "  AND tw.deleted_at IS NULL AND tw.authoring_state = 'idle' AND p.archived_at IS NULL\n"
	case StatusArchived:
		query += "  AND tw.deleted_at IS NULL AND tw.authoring_state = 'idle' AND p.archived_at IS NOT NULL\n"
	case StateFilterAvailable:
		query += "  AND tw.deleted_at IS NULL AND p.archived_at IS NULL AND p.current_version_id IS NOT NULL\n"
	case AuthoringStateGenerating, AuthoringStateIdle, AuthoringStateFailed:
		args = append(args, state)
		query += fmt.Sprintf("  AND tw.deleted_at IS NULL AND tw.authoring_state = $%d\n", len(args))
	default:
		return "", nil, storeerr.BadRequest("invalid Team Workflow state filter")
	}
	return query, args, nil
}