package app

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/app/registry"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
)

func deleteGitHubSetupProposalByIDInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	proposalID string,
	requesterMemberID string,
) error {
	result, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE id = $1
  AND workspace_id = $2
	AND service_definition_id = $3
	AND service_kind = $4
	AND request_kind = $5
	AND requested_by_member_id = $6
	AND status = $7
`, strings.TrimSpace(proposalID), strings.TrimSpace(workspaceID), registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall,
		strings.TrimSpace(requesterMemberID), AppInstallationRequestStatusSetupReady)
	if err != nil {
		return fmt.Errorf("delete GitHub setup proposal: %w", err)
	}
	if result.RowsAffected() != 1 {
		return conflict("GitHub setup proposal changed. Restart setup.")
	}
	return releaseGitHubAppSetupClaimByTokenInTx(ctx, tx, workspaceID, proposalID)
}

func deleteExpiredGitHubSetupProposalsInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	cutoff time.Time,
) error {
	if _, err := tx.Exec(ctx, `
DELETE claim
FROM service_provider_workspace_claims claim
JOIN service_installation_requests req
  ON req.id = claim.claim_token
 AND req.workspace_id = claim.workspace_id
WHERE claim.workspace_id = $1
  AND claim.claim_state = $2
  AND req.service_definition_id = $3
  AND req.service_kind = $4
  AND req.request_kind = $5
  AND req.status = $6
  AND req.created_at < $7
`, strings.TrimSpace(workspaceID), githubAppClaimStateSetup, registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall,
		AppInstallationRequestStatusSetupReady, cutoff.UTC()); err != nil {
		return fmt.Errorf("release expired GitHub setup proposal claims: %w", err)
	}
	if err := deleteExpiredGitHubAppSetupClaimsInTx(
		ctx, tx, cutoff.UTC().Add(githubSetupProposalTTL),
	); err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE workspace_id = $1
	AND service_definition_id = $2
	AND service_kind = $3
	AND request_kind = $4
	AND status = $5
	AND created_at < $6
ORDER BY created_at ASC, id ASC
LIMIT 100
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		AppInstallationRequestKindInstall, AppInstallationRequestStatusSetupReady, cutoff.UTC()); err != nil {
		return fmt.Errorf("delete expired GitHub setup proposals: %w", err)
	}
	return nil
}

func deleteAllGitHubSetupProposalsInTx(ctx context.Context, tx coredb.Tx, workspaceID string) error {
	if _, err := tx.Exec(ctx, `
DELETE claim
FROM service_provider_workspace_claims claim
WHERE claim.workspace_id = $1
  AND claim.claim_state = $2
  AND EXISTS (
      SELECT 1
      FROM service_installation_requests req
      WHERE req.id = claim.claim_token
        AND req.workspace_id = claim.workspace_id
        AND req.service_definition_id = $3
        AND req.service_kind = $4
        AND req.request_kind = $5
        AND req.status = $6
  )
`, strings.TrimSpace(workspaceID), githubAppClaimStateSetup, registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall,
		AppInstallationRequestStatusSetupReady); err != nil {
		return fmt.Errorf("release GitHub App setup proposal claim: %w", err)
	}
	if _, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE workspace_id = $1
	AND service_definition_id = $2
	AND service_kind = $3
	AND request_kind = $4
	AND status = $5
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		AppInstallationRequestKindInstall, AppInstallationRequestStatusSetupReady); err != nil {
		return fmt.Errorf("delete GitHub setup proposals: %w", err)
	}
	return nil
}

// deleteRevokedAdminGitHubSetupProposalsInTx prevents a pre-Begin draft from
// becoming a one-hour availability fence after its owner is removed or loses
// administrator authority. Begun verifying/patching leases are deliberately
// not setup-ready and retain their authorization-at-Begin recovery semantics.
func deleteRevokedAdminGitHubSetupProposalsInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
) error {
	if _, err := tx.Exec(ctx, `
DELETE claim
FROM service_provider_workspace_claims claim
JOIN service_installation_requests req
  ON req.id = claim.claim_token
 AND req.workspace_id = claim.workspace_id
LEFT JOIN members owner
  ON owner.id = req.requested_by_member_id
 AND owner.workspace_id = req.workspace_id
WHERE claim.workspace_id = $1
  AND claim.claim_state = $2
  AND req.service_definition_id = $3
  AND req.service_kind = $4
  AND req.request_kind = $5
  AND req.status = $6
  AND req.request_reason = $7
  AND (
      owner.id IS NULL
      OR owner.type <> 'human'
      OR owner.status <> 'active'
      OR owner.workspace_role IS NULL
      OR owner.workspace_role NOT IN ($8, $9)
  )
`, strings.TrimSpace(workspaceID), githubAppClaimStateSetup, registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall,
		AppInstallationRequestStatusSetupReady, GitHubAppSetupModeAdmin,
		string(domain.WorkspaceRoleOwner), string(domain.WorkspaceRoleAdmin)); err != nil {
		return fmt.Errorf("release revoked administrator GitHub setup draft claims: %w", err)
	}
	if _, err := tx.Exec(ctx, `
DELETE req
FROM service_installation_requests req
LEFT JOIN members owner
  ON owner.id = req.requested_by_member_id
 AND owner.workspace_id = req.workspace_id
WHERE req.workspace_id = $1
  AND req.service_definition_id = $2
  AND req.service_kind = $3
  AND req.request_kind = $4
  AND req.status = $5
  AND req.request_reason = $6
  AND (
      owner.id IS NULL
      OR owner.type <> 'human'
      OR owner.status <> 'active'
      OR owner.workspace_role IS NULL
      OR owner.workspace_role NOT IN ($7, $8)
  )
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		AppInstallationRequestKindInstall, AppInstallationRequestStatusSetupReady,
		GitHubAppSetupModeAdmin, string(domain.WorkspaceRoleOwner), string(domain.WorkspaceRoleAdmin)); err != nil {
		return fmt.Errorf("delete revoked administrator GitHub setup drafts: %w", err)
	}
	return nil
}