package plan

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

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

const workflowScopeType = "workflow"

type workflowScope struct {
	ID                string
	WorkspaceID       string
	Type              string
	IdentityKey       sql.NullString
	Status            string
	CreatedByMemberID sql.NullString
	ParentScopeID     sql.NullString
	RootMessageID     sql.NullString
	RootMessageShort  sql.NullString
	ArchivedAt        sql.NullTime
}

type workflowScopeMembershipBasis struct {
	Visibility    string
	OwnerMemberID sql.NullString
}

// ResolvePlanAuthoringMember returns the Human member that may directly
// author one ordinary Workflow Plan. Human-created Plans retain their creator
// member; Agent-created Plans use the owner recorded on the canonical
// Workflow Scope creator membership.
func (store *Store) ResolvePlanAuthoringMember(
	ctx context.Context,
	workspaceID string,
	planID string,
) (string, error) {
	if err := store.Validate(); err != nil {
		return "", err
	}
	workspaceID, planID = trim(workspaceID), trim(planID)
	if workspaceID == "" || planID == "" {
		return "", storeerr.BadRequest("workspaceId and planId are required")
	}

	var actorType string
	var creatorMemberID string
	var visibility sql.NullString
	var ownerMemberID sql.NullString
	err := store.db.QueryRow(ctx, `
SELECT plan.created_by_actor_type,
       plan.created_by_member_id,
       creator_membership.visibility,
       creator_membership.owner_member_id
FROM workflow_plans plan
LEFT JOIN scope_memberships creator_membership
  ON creator_membership.scope_id = plan.scope_id
 AND creator_membership.member_id = plan.created_by_member_id
 AND creator_membership.effective_to_seq IS NULL
 AND creator_membership.removed_at IS NULL
 AND creator_membership.archived_at IS NULL
WHERE plan.workspace_id = $1
  AND plan.id = $2
ORDER BY creator_membership.effective_from_seq DESC
LIMIT 1
`, workspaceID, planID).Scan(
		&actorType,
		&creatorMemberID,
		&visibility,
		&ownerMemberID,
	)
	if err != nil {
		return "", mapWriteError(err)
	}

	switch actorType {
	case ActorTypeHuman:
		if creatorMemberID == "" {
			return "", storeerr.Conflict("workflow Plan authoring authority is unavailable")
		}
		return creatorMemberID, nil
	case ActorTypeAgent:
		if !visibility.Valid || visibility.String != "private" ||
			!ownerMemberID.Valid || trim(ownerMemberID.String) == "" {
			return "", storeerr.Conflict("workflow Plan authoring authority is unavailable")
		}
		return trim(ownerMemberID.String), nil
	default:
		return "", storeerr.Conflict("workflow Plan creator type is invalid")
	}
}

func createWorkflowScope(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	planID string,
	creator Actor,
	now time.Time,
) (workflowScope, workflowScopeMembershipBasis, bool, error) {
	membershipBasis, err := loadWorkflowScopeCreatorMembershipBasis(ctx, tx, workspaceID, creator)
	if err != nil {
		return workflowScope{}, workflowScopeMembershipBasis{}, false, err
	}

	scopeID, err := newID("wfs", "Scope")
	if err != nil {
		return workflowScope{}, workflowScopeMembershipBasis{}, false, err
	}
	identityKey := "workflow:" + planID
	result, err := tx.Exec(ctx, `
INSERT INTO scopes (
    id, workspace_id, type,
    parent_scope_id, root_message_id, root_message_short_id,
    identity_key, status, created_by_member_id,
    created_at, updated_at, archived_at
) VALUES (
    $1, $2, 'workflow',
    NULL, NULL, NULL,
    $3, 'active', $4,
    $5, $5, NULL
)
ON DUPLICATE KEY UPDATE id = id
`, scopeID, workspaceID, identityKey, creator.MemberID, now)
	if err != nil {
		return workflowScope{}, workflowScopeMembershipBasis{}, false, fmt.Errorf("insert workflow Plan Scope: %w", mapWriteError(err))
	}

	scope, err := loadWorkflowScopeByIdentity(ctx, tx, identityKey, true)
	if err != nil {
		if errors.Is(err, storeerr.ErrNotFound) {
			return workflowScope{}, workflowScopeMembershipBasis{}, false, storeerr.Conflict("workflow Plan Scope identity or id already exists")
		}
		return workflowScope{}, workflowScopeMembershipBasis{}, false, err
	}
	if scope.WorkspaceID != workspaceID ||
		scope.Type != workflowScopeType ||
		!scope.IdentityKey.Valid || scope.IdentityKey.String != identityKey ||
		scope.Status != "active" ||
		!scope.CreatedByMemberID.Valid || scope.CreatedByMemberID.String != creator.MemberID ||
		scope.ParentScopeID.Valid || scope.RootMessageID.Valid || scope.RootMessageShort.Valid ||
		scope.ArchivedAt.Valid {
		return workflowScope{}, workflowScopeMembershipBasis{}, false, storeerr.Conflict("workflow Plan Scope identity conflicts with an existing Scope")
	}
	return scope, membershipBasis, result.RowsAffected() == 1 && scope.ID == scopeID, nil
}

func loadWorkflowScopeCreatorMembershipBasis(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	creator Actor,
) (workflowScopeMembershipBasis, error) {
	switch creator.Type {
	case ActorTypeHuman:
		var memberID string
		err := queryer.QueryRow(ctx, `
SELECT id
FROM members
WHERE workspace_id = $1
  AND id = $2
  AND type = 'human'
  AND user_id = $3
  AND status = 'active'
FOR UPDATE
`, workspaceID, creator.MemberID, creator.ID).Scan(&memberID)
		if err != nil {
			if coredb.IsNotFound(err) {
				return workflowScopeMembershipBasis{}, storeerr.BadRequest("human creator member authority is unavailable")
			}
			return workflowScopeMembershipBasis{}, fmt.Errorf("lock human workflow Plan creator authority: %w", mapWriteError(err))
		}
		return workflowScopeMembershipBasis{Visibility: "public"}, nil
	case ActorTypeAgent:
		var ownerMemberID string
		err := queryer.QueryRow(ctx, `
SELECT owner_member.id
FROM agents agent
JOIN machines machine
  ON machine.workspace_id = agent.workspace_id
 AND machine.id = agent.machine_id
 AND machine.status = 'active'
JOIN members agent_member
  ON agent_member.workspace_id = agent.workspace_id
 AND agent_member.id = agent.member_id
 AND agent_member.type = 'agent'
 AND agent_member.status = 'active'
JOIN members owner_member
  ON owner_member.workspace_id = agent.workspace_id
 AND owner_member.user_id = machine.created_by_user_id
 AND owner_member.type = 'human'
 AND owner_member.status = 'active'
WHERE agent.workspace_id = $1
  AND agent.id = $2
  AND agent.member_id = $3
  AND agent.status <> 'archived'
FOR UPDATE
`, workspaceID, creator.ID, creator.MemberID).Scan(&ownerMemberID)
		if err != nil {
			if coredb.IsNotFound(err) {
				return workflowScopeMembershipBasis{}, storeerr.BadRequest("Agent creator owner authority is unavailable")
			}
			return workflowScopeMembershipBasis{}, fmt.Errorf("lock Agent workflow Plan creator authority: %w", mapWriteError(err))
		}
		return workflowScopeMembershipBasis{
			Visibility:    "private",
			OwnerMemberID: sql.NullString{String: ownerMemberID, Valid: true},
		}, nil
	default:
		return workflowScopeMembershipBasis{}, storeerr.BadRequest("workflow Plan creator type is invalid")
	}
}

func loadWorkflowScopeByIdentity(
	ctx context.Context,
	queryer coredb.QueryRower,
	identityKey string,
	forUpdate bool,
) (workflowScope, error) {
	query := `
SELECT id, workspace_id, type, identity_key, status, created_by_member_id,
       parent_scope_id, root_message_id, root_message_short_id, archived_at
FROM scopes
WHERE identity_key = $1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	var scope workflowScope
	if err := queryer.QueryRow(ctx, query, identityKey).Scan(
		&scope.ID, &scope.WorkspaceID, &scope.Type, &scope.IdentityKey, &scope.Status, &scope.CreatedByMemberID,
		&scope.ParentScopeID, &scope.RootMessageID, &scope.RootMessageShort, &scope.ArchivedAt,
	); err != nil {
		return workflowScope{}, mapWriteError(err)
	}
	return scope, nil
}

func ensureWorkflowScopeCreatorMembership(
	ctx context.Context,
	tx coredb.Tx,
	scopeID string,
	creatorMemberID string,
	basis workflowScopeMembershipBasis,
	now time.Time,
) error {
	if _, err := tx.Exec(ctx, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id,
    last_read_seq, last_read_global_seq, last_activity_global_seq,
    owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, authority_from_at, authority_to_at,
    created_at, updated_at, removed_at, archived_at
) VALUES (
    $1, $2, $3, $4,
    0, 0, 0, 0,
    1, NULL, LEAST($5, CURRENT_TIMESTAMP(6)), NULL,
    $5, $5, NULL, NULL
)
ON DUPLICATE KEY UPDATE scope_id = scope_id
`, scopeID, creatorMemberID, basis.Visibility, basis.OwnerMemberID, now); err != nil {
		return fmt.Errorf("insert workflow Plan creator membership: %w", mapWriteError(err))
	}
	var visibility string
	var ownerMemberID sql.NullString
	if err := tx.QueryRow(ctx, `
SELECT visibility, owner_member_id
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND effective_to_seq IS NULL
  AND removed_at IS NULL
  AND archived_at IS NULL
ORDER BY effective_from_seq DESC
LIMIT 1
FOR UPDATE
`, scopeID, creatorMemberID).Scan(&visibility, &ownerMemberID); err != nil {
		return fmt.Errorf("load workflow Plan creator membership: %w", mapWriteError(err))
	}
	if visibility != basis.Visibility ||
		ownerMemberID.Valid != basis.OwnerMemberID.Valid ||
		(ownerMemberID.Valid && ownerMemberID.String != basis.OwnerMemberID.String) {
		return storeerr.Conflict("workflow Plan creator membership authority conflicts with existing membership")
	}
	return nil
}

func deleteCreatedWorkflowScope(ctx context.Context, tx coredb.Tx, scope workflowScope, planID string) error {
	result, err := tx.Exec(ctx, `
DELETE FROM scopes
WHERE id = $1
  AND identity_key = $2
  AND type = 'workflow'
`, scope.ID, "workflow:"+planID)
	if err != nil {
		return fmt.Errorf("delete losing workflow Plan Scope: %w", mapWriteError(err))
	}
	if result.RowsAffected() != 1 {
		return fmt.Errorf("delete losing workflow Plan Scope: expected one generated Scope")
	}
	return nil
}