package taskboard

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

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

// Collaborator request lifecycle statuses. These mirror the Approval terminal
// states so requester status surfaces are uniform.
const (
	CollaboratorRequestStatusPending   = "pending"
	CollaboratorRequestStatusApproved  = "approved"
	CollaboratorRequestStatusRejected  = "rejected"
	CollaboratorRequestStatusCancelled = "cancelled"
	CollaboratorRequestStatusExpired   = "expired"
)

// CollaboratorRequest is the durable requester materialization state. The
// generic Approval (subject_type='collaborator_request', approver = execution
// owner) is the workflow source of truth; this row freezes the requester /
// owner / target facts and records the lifecycle.
type CollaboratorRequest struct {
	ID                    string
	WorkspaceID           string
	ApprovalID            string
	RequesterMemberID     string
	OwnerMemberID         string
	ScopeType             string
	ScopeID               string
	Role                  string
	CollaboratorExpiresAt *time.Time
	ApprovalExpiresAt     *time.Time
	Status                string
	GrantedAt             *time.Time
	CreatedAt             time.Time
	UpdatedAt             time.Time
}

// CreateCollaboratorRequestParams carries the frozen request facts.
type CreateCollaboratorRequestParams struct {
	ID                    string
	WorkspaceID           string
	ApprovalID            string
	RequesterMemberID     string
	OwnerMemberID         string
	ScopeType             string
	ScopeID               string
	Role                  string
	CollaboratorExpiresAt *time.Time
	ApprovalExpiresAt     *time.Time
	Now                   time.Time
}

// createCollaboratorRequestInTx writes the durable request row in the bound
// transaction (the same transaction that created the Approval).
func (txs TxStore) CreateCollaboratorRequest(ctx context.Context, params CreateCollaboratorRequestParams) error {
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.ID = strings.TrimSpace(params.ID)
	params.ApprovalID = strings.TrimSpace(params.ApprovalID)
	params.RequesterMemberID = strings.TrimSpace(params.RequesterMemberID)
	params.OwnerMemberID = strings.TrimSpace(params.OwnerMemberID)
	params.ScopeType = strings.TrimSpace(params.ScopeType)
	params.ScopeID = strings.TrimSpace(params.ScopeID)
	if params.WorkspaceID == "" || params.ID == "" || params.ApprovalID == "" ||
		params.RequesterMemberID == "" || params.OwnerMemberID == "" ||
		params.ScopeType == "" || params.ScopeID == "" {
		return badRequest("collaborator request identity, requester, owner, and target are required")
	}
	if _, err := txs.tx.Exec(ctx, `
INSERT INTO task_collaborator_requests (
    id, workspace_id, approval_id, requester_member_id, owner_member_id,
    scope_type, scope_id, role, collaborator_expires_at, approval_expires_at,
    status, granted_at, created_at, updated_at
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,'pending',NULL,$11,$11)
ON DUPLICATE KEY UPDATE id = id
`, params.ID, params.WorkspaceID, params.ApprovalID, params.RequesterMemberID,
		params.OwnerMemberID, params.ScopeType, params.ScopeID, params.Role,
		params.CollaboratorExpiresAt, params.ApprovalExpiresAt, params.Now); err != nil {
		return fmt.Errorf("insert collaborator request: %w", err)
	}
	return nil
}

// loadCollaboratorRequestInTx loads a request row in the bound transaction (FOR
// UPDATE) so the decision effect revalidates under the row lock.
func (txs TxStore) LoadCollaboratorRequest(ctx context.Context, requestID string) (CollaboratorRequest, error) {
	row := txs.tx.QueryRow(ctx, `
SELECT id, workspace_id, approval_id, requester_member_id, owner_member_id,
       scope_type, scope_id, role, collaborator_expires_at, approval_expires_at,
       status, granted_at, created_at, updated_at
FROM task_collaborator_requests
WHERE id = $1
FOR UPDATE
`, strings.TrimSpace(requestID))
	var request CollaboratorRequest
	var collaboratorExpiresAt, approvalExpiresAt, grantedAt, createdAt, updatedAt sql.NullTime
	if err := row.Scan(
		&request.ID, &request.WorkspaceID, &request.ApprovalID, &request.RequesterMemberID,
		&request.OwnerMemberID, &request.ScopeType, &request.ScopeID, &request.Role,
		&collaboratorExpiresAt, &approvalExpiresAt, &request.Status, &grantedAt,
		&createdAt, &updatedAt,
	); err != nil {
		if coredb.IsNotFound(err) {
			return CollaboratorRequest{}, storeerr.NotFound("collaborator request not found")
		}
		return CollaboratorRequest{}, fmt.Errorf("load collaborator request: %w", err)
	}
	if collaboratorExpiresAt.Valid {
		request.CollaboratorExpiresAt = &collaboratorExpiresAt.Time
	}
	if approvalExpiresAt.Valid {
		request.ApprovalExpiresAt = &approvalExpiresAt.Time
	}
	if grantedAt.Valid {
		request.GrantedAt = &grantedAt.Time
	}
	request.CreatedAt = createdAt.Time
	request.UpdatedAt = updatedAt.Time
	return request, nil
}

// terminalizeCollaboratorRequestInTx sets the request lifecycle status in the
// bound transaction, exactly once.
func (txs TxStore) TerminalizeCollaboratorRequest(ctx context.Context, requestID string, status string, grantedAt *time.Time) error {
	requestID = strings.TrimSpace(requestID)
	if requestID == "" || status == "" {
		return badRequest("collaborator request id and status are required")
	}
	updated, err := txs.tx.Exec(ctx, `
UPDATE task_collaborator_requests
SET status = $2, granted_at = $3, updated_at = $4
WHERE id = $1 AND status = 'pending'
`, requestID, status, grantedAt, time.Now().UTC())
	if err != nil {
		return fmt.Errorf("terminalize collaborator request: %w", err)
	}
	if updated.RowsAffected() != 1 {
		return storeerr.Conflict("collaborator request is no longer pending")
	}
	return nil
}

// ListRequesterCollaboratorRequests returns the requester's collaborator
// requests across scopes (for the requester status surface).
func (store *Store) ListRequesterCollaboratorRequests(ctx context.Context, workspaceID, requesterMemberID string) ([]CollaboratorRequest, error) {
	workspaceID = strings.TrimSpace(workspaceID)
	requesterMemberID = strings.TrimSpace(requesterMemberID)
	if workspaceID == "" || requesterMemberID == "" {
		return nil, badRequest("workspace and requester are required")
	}
	rows, err := store.db.Query(ctx, `
SELECT id, workspace_id, approval_id, requester_member_id, owner_member_id,
       scope_type, scope_id, role, collaborator_expires_at, approval_expires_at,
       status, granted_at, created_at, updated_at
FROM task_collaborator_requests
WHERE workspace_id = $1 AND requester_member_id = $2
ORDER BY created_at DESC, id DESC
`, workspaceID, requesterMemberID)
	if err != nil {
		return nil, fmt.Errorf("list collaborator requests: %w", err)
	}
	defer rows.Close()
	result := make([]CollaboratorRequest, 0)
	for rows.Next() {
		var request CollaboratorRequest
		var collaboratorExpiresAt, approvalExpiresAt, grantedAt, createdAt, updatedAt sql.NullTime
		if err := rows.Scan(
			&request.ID, &request.WorkspaceID, &request.ApprovalID, &request.RequesterMemberID,
			&request.OwnerMemberID, &request.ScopeType, &request.ScopeID, &request.Role,
			&collaboratorExpiresAt, &approvalExpiresAt, &request.Status, &grantedAt,
			&createdAt, &updatedAt,
		); err != nil {
			return nil, err
		}
		if collaboratorExpiresAt.Valid {
			request.CollaboratorExpiresAt = &collaboratorExpiresAt.Time
		}
		if approvalExpiresAt.Valid {
			request.ApprovalExpiresAt = &approvalExpiresAt.Time
		}
		if grantedAt.Valid {
			request.GrantedAt = &grantedAt.Time
		}
		request.CreatedAt = createdAt.Time
		request.UpdatedAt = updatedAt.Time
		result = append(result, request)
	}
	return result, nil
}