import {
  ADMIN_ROLE_NAME,
  APP_RUNTIME_SYSTEM_PROMPT,
  ARCHESTRA_MCP_CATALOG_ID,
  BUILT_IN_AGENT_IDS,
  BUILT_IN_AGENT_NAMES,
  CHAT_TITLE_GENERATION_SYSTEM_PROMPT,
  CONTEXT_COMPACTION_SYSTEM_PROMPT,
  DUAL_LLM_MAIN_SYSTEM_PROMPT,
  DUAL_LLM_QUARANTINE_SYSTEM_PROMPT,
  PLAYWRIGHT_MCP_CATALOG_ID,
  PLAYWRIGHT_MCP_ICON,
  PLAYWRIGHT_MCP_SERVER_NAME,
  POLICY_CONFIG_SYSTEM_PROMPT,
  PREVIOUS_POLICY_CONFIG_SYSTEM_PROMPT,
  PROVIDERS_REQUIRING_BASE_URL,
  type PredefinedRoleName,
  providerRequiresPerUserCredential,
  type SupportedProvider,
  SupportedProviders,
  testMcpServerCommand,
} from "@archestra/shared";
import { and, eq, inArray, isNull } from "drizzle-orm";
import { archestraMcpBranding } from "@/archestra-mcp-server/branding";
import config, {
  getProviderConfiguredBaseUrl,
  getProviderEnvApiKey,
} from "@/config";
import db, { schema, withDbTransaction } from "@/database";
import logger from "@/logging";
import {
  AgentExcludedToolModel,
  AgentModel,
  InternalMcpCatalogModel,
  LlmProviderApiKeyModel,
  McpHttpSessionModel,
  MemberModel,
  OrganizationModel,
  SkillFileModel,
  SkillModel,
  TeamModel,
  TeamTokenModel,
  ToolModel,
  UserModel,
} from "@/models";
import { secretManager } from "@/secrets-manager";
import { modelSyncService } from "@/services/model-sync";
import {
  BUILT_IN_SKILLS,
  builtInSkillShippedWrite,
  builtInSkillSourceRef,
  builtInSkillVersion,
} from "@/skills/built-in-skills";
import type { Organization } from "@/types";
import {
  encryptSecretValue,
  ensureEncryptionKeyAvailable,
  isEncryptedSecret,
} from "@/utils/crypto";

/**
 * Seeds admin user
 */
export async function seedDefaultUserAndOrg(
  config: {
    email?: string;
    password?: string;
    role?: PredefinedRoleName;
    name?: string;
  } = {},
) {
  const user = await UserModel.createOrGetExistingDefaultAdminUser(config);
  const org = await OrganizationModel.getOrCreateDefaultOrganization();
  if (!user || !org) {
    throw new Error("Failed to seed admin user and default organization");
  }

  const existingMember = await MemberModel.getByUserId(user.id, org.id);

  if (!existingMember) {
    await MemberModel.create(user.id, org.id, config.role || ADMIN_ROLE_NAME);
  }
  logger.info("Seeded admin user and default organization");
  return user;
}

/** @public — exported for testability */
export async function syncBuiltInAgents(): Promise<void> {
  const organizations = await getOrganizationsForBuiltInAgentSync();

  const builtInAgents = [
    {
      builtInAgentId: BUILT_IN_AGENT_IDS.POLICY_CONFIG,
      name: BUILT_IN_AGENT_NAMES.POLICY_CONFIG,
      description:
        "Analyzes tool metadata with AI to generate deterministic security policies for handling untrusted data",
      systemPrompt: POLICY_CONFIG_SYSTEM_PROMPT,
      builtInAgentConfig: {
        name: BUILT_IN_AGENT_IDS.POLICY_CONFIG,
        autoConfigureOnToolDiscovery: false,
      } as const,
    },
    {
      builtInAgentId: BUILT_IN_AGENT_IDS.DUAL_LLM_MAIN,
      name: BUILT_IN_AGENT_NAMES.DUAL_LLM_MAIN,
      description:
        "Privileged built-in agent that questions quarantined tool results and writes the final safe summary",
      systemPrompt: DUAL_LLM_MAIN_SYSTEM_PROMPT,
      builtInAgentConfig: {
        name: BUILT_IN_AGENT_IDS.DUAL_LLM_MAIN,
        maxRounds: 5,
      } as const,
    },
    {
      builtInAgentId: BUILT_IN_AGENT_IDS.DUAL_LLM_QUARANTINE,
      name: BUILT_IN_AGENT_NAMES.DUAL_LLM_QUARANTINE,
      description:
        "Quarantine built-in agent that inspects untrusted tool output and returns constrained answers only",
      systemPrompt: DUAL_LLM_QUARANTINE_SYSTEM_PROMPT,
      builtInAgentConfig: {
        name: BUILT_IN_AGENT_IDS.DUAL_LLM_QUARANTINE,
      } as const,
    },
    {
      builtInAgentId: BUILT_IN_AGENT_IDS.CONTEXT_COMPACTION,
      name: BUILT_IN_AGENT_NAMES.CONTEXT_COMPACTION,
      description:
        "Summarizes older chat context into a durable handoff so long-running conversations can continue near model context limits",
      systemPrompt: CONTEXT_COMPACTION_SYSTEM_PROMPT,
      builtInAgentConfig: {
        name: BUILT_IN_AGENT_IDS.CONTEXT_COMPACTION,
      } as const,
    },
    {
      builtInAgentId: BUILT_IN_AGENT_IDS.CHAT_TITLE_GENERATION,
      name: BUILT_IN_AGENT_NAMES.CHAT_TITLE_GENERATION,
      description:
        "Generates concise titles for chat conversations using the configured title generation model",
      systemPrompt: CHAT_TITLE_GENERATION_SYSTEM_PROMPT,
      builtInAgentConfig: {
        name: BUILT_IN_AGENT_IDS.CHAT_TITLE_GENERATION,
      } as const,
    },
    {
      builtInAgentId: BUILT_IN_AGENT_IDS.APP_RUNTIME,
      name: BUILT_IN_AGENT_NAMES.APP_RUNTIME,
      description:
        "Backs archestra.llm.complete() for MCP Apps — the proxy identity that attributes app LLM completions to org usage limits",
      systemPrompt: APP_RUNTIME_SYSTEM_PROMPT,
      builtInAgentConfig: {
        name: BUILT_IN_AGENT_IDS.APP_RUNTIME,
      } as const,
    },
  ];

  for (const organization of organizations) {
    for (const builtInAgent of builtInAgents) {
      const existing = await AgentModel.getBuiltInAgent(
        builtInAgent.builtInAgentId,
        organization.id,
      );

      if (!existing) {
        await db.insert(schema.agentsTable).values({
          organizationId: organization.id,
          name: builtInAgent.name,
          agentType: "agent",
          scope: "org",
          description: builtInAgent.description,
          systemPrompt: builtInAgent.systemPrompt,
          builtInAgentConfig: builtInAgent.builtInAgentConfig,
        });
        logger.info(
          {
            builtInAgentId: builtInAgent.builtInAgentId,
            organizationId: organization.id,
          },
          "Seeded built-in agent",
        );
        continue;
      }

      if (
        shouldSyncBuiltInAgentSystemPrompt({
          builtInAgentId: builtInAgent.builtInAgentId,
          systemPrompt: existing.systemPrompt,
        })
      ) {
        await db
          .update(schema.agentsTable)
          .set({ systemPrompt: builtInAgent.systemPrompt })
          .where(eq(schema.agentsTable.id, existing.id));

        logger.info(
          {
            builtInAgentId: builtInAgent.builtInAgentId,
            organizationId: organization.id,
          },
          "Updated built-in agent legacy system prompt",
        );
        continue;
      }

      logger.info(
        {
          builtInAgentId: builtInAgent.builtInAgentId,
          organizationId: organization.id,
        },
        "Built-in agent already exists, skipping seed",
      );
    }
  }
}

/**
 * Reconciles Archestra's shipped built-in skills into every organization.
 *
 * Insert when missing. When present and still pristine (its live content hashes
 * to the version we last wrote), auto-upgrade it to the current shipped
 * revision. When the user has edited it, leave it untouched — administrators
 * reset to default explicitly. Identity is the stable `builtin:<id>` source
 * ref, so a rename never detaches a skill from its definition.
 *
 * @public — exported for testability
 */
export async function syncBuiltInSkills(): Promise<void> {
  const organizations = await getOrganizationsForBuiltInAgentSync();

  for (const organization of organizations) {
    const orgRecord = await OrganizationModel.getById(organization.id);
    if (!orgRecord) continue;
    await syncBuiltInSkillsForOrganization(orgRecord);
  }
}

/**
 * Reconcile the built-in skills into a single organization, branded under its
 * white-label app name. Called per-org by {@link syncBuiltInSkills} on startup
 * and directly when an admin changes the app name (so list_skills/load_skill
 * reflect the new brand immediately, mirroring the built-in MCP tool re-seed).
 *
 * @public — invoked from the organization route on app-name change.
 */
export async function syncBuiltInSkillsForOrganization(
  organization: Pick<Organization, "id" | "appName" | "iconLogo">,
): Promise<void> {
  // Brand the shipped built-in skills under this org's white-label identity (a
  // no-op unless full white-labeling is active), mirroring how built-in MCP
  // tools are seeded under the org's branded tool name. builtInSkillShippedWrite
  // reads the synced singleton, so this must run before it.
  archestraMcpBranding.syncFromOrganization(organization);

  for (const builtInSkill of BUILT_IN_SKILLS) {
    const sourceRef = builtInSkillSourceRef(builtInSkill.builtInSkillId);
    const shipped = builtInSkillShippedWrite(builtInSkill);

    const existing = await SkillModel.findBuiltIn({
      organizationId: organization.id,
      sourceRef,
    });

    if (!existing) {
      const created = await SkillModel.createWithFiles({
        skill: {
          organizationId: organization.id,
          scope: "org",
          sourceType: "built_in",
          sourceRef,
          ...shipped.skill,
        },
        files: shipped.files,
      });
      // createWithFiles is ON CONFLICT DO NOTHING on the per-org shared-name
      // index, so a null means a pre-existing non-built-in skill already
      // holds this name. Surface it instead of reporting a phantom seed — that
      // org has no built-in copy and thus no reset path until the clash clears.
      if (!created) {
        logger.warn(
          {
            builtInSkillId: builtInSkill.builtInSkillId,
            organizationId: organization.id,
            name: builtInSkill.name,
          },
          "Skipped seeding built-in skill: a skill with this name already exists",
        );
        continue;
      }
      logger.info(
        {
          builtInSkillId: builtInSkill.builtInSkillId,
          organizationId: organization.id,
        },
        "Seeded built-in skill",
      );
      continue;
    }

    if (existing.sourceCommit === shipped.skill.sourceCommit) {
      continue;
    }

    const liveFiles = await SkillFileModel.findBySkillId(existing.id);
    const liveVersion = builtInSkillVersion({
      content: existing.content,
      files: liveFiles,
    });

    // Only auto-upgrade copies that still match the revision we last wrote; a
    // diverged copy was edited by the user and is reset explicitly instead.
    if (liveVersion !== existing.sourceCommit) {
      logger.info(
        {
          builtInSkillId: builtInSkill.builtInSkillId,
          organizationId: organization.id,
        },
        "Built-in skill was edited, preserving user changes",
      );
      continue;
    }

    await SkillModel.updateWithFiles({
      id: existing.id,
      skill: shipped.skill,
      files: shipped.files,
    });
    logger.info(
      {
        builtInSkillId: builtInSkill.builtInSkillId,
        organizationId: organization.id,
      },
      "Upgraded built-in skill to current revision",
    );
  }
}

/**
 * Seeds Archestra MCP catalog and tools.
 * ToolModel.seedArchestraTools upserts the catalog and built-in tools idempotently.
 * Tools are NOT automatically assigned to agents - users must assign them manually.
 */
async function seedArchestraCatalogAndTools(): Promise<void> {
  const newlyCreatedToolNames = await ToolModel.seedArchestraTools(
    ARCHESTRA_MCP_CATALOG_ID,
  );
  await ToolModel.backfillNewSkillToolsToEnabledOrgs(newlyCreatedToolNames);
  await ToolModel.backfillNewAppToolsToEnabledOrgs(newlyCreatedToolNames);
  // A brand-new built-in tool must not silently reach existing All-tools-mode
  // agents: pre-exclude it for them. Runs after the assignment backfills above
  // so a tool those just assigned is skipped (assignments beat the pre-fill);
  // exempt short names are skipped inside the model method.
  const newlyCreatedToolIds = await ToolModel.findBuiltInToolIdsByNames(
    newlyCreatedToolNames,
  );
  const excludedRowCount =
    await AgentExcludedToolModel.prefillNewBuiltInToolsForAllToolsAgents(
      newlyCreatedToolIds,
    );
  if (excludedRowCount > 0) {
    logger.info(
      { excludedRowCount, newToolCount: newlyCreatedToolIds.length },
      "Pre-excluded new built-in tools for All-tools-mode agents",
    );
  }
  logger.info("Seeded Archestra catalog and tools");
}

/**
 * Seeds Playwright browser preview MCP catalog.
 * This is a globally available catalog - tools are auto-included for all agents in chat.
 * Each user gets their own personal Playwright server instance when they click the Browser button.
 */
async function seedPlaywrightCatalog(): Promise<void> {
  const LEGACY_PLAYWRIGHT_MCP_SERVER_NAME = "playwright-browser";
  const playwrightLocalConfig = {
    // Pinned to v0.0.64 digest because v0.0.67 renamed --no-sandbox to --no-chromium-sandbox
    // but the image entrypoint still uses --no-sandbox, causing immediate crashes.
    dockerImage:
      "mcr.microsoft.com/playwright/mcp@sha256:50fee3932984dbf40fe67be11fe22d0050eca40705cf108099d7a1e0fe6a181c",
    transportType: "streamable-http" as const,
    // Explicit command overrides the image ENTRYPOINT to avoid breakage from upstream image changes.
    // v0.0.67 broke the entrypoint by renaming --no-sandbox to --no-chromium-sandbox without
    // updating the Dockerfile. Using explicit command+args makes us resilient to such changes.
    command: "node",
    // Full arguments including cli.js entry point and all Chromium/server flags:
    //   cli.js: the Playwright MCP server entry point
    //   --headless: run Chromium in headless mode
    //   --browser chromium: use Chromium browser
    //   --no-sandbox: required when running as root in containers (renamed to --no-chromium-sandbox in v0.0.67)
    //   --host 0.0.0.0: bind to all interfaces so K8s Service can route traffic to the pod
    //   --port 8080: enable HTTP transport mode (without --port, it runs in stdio mode and exits)
    //   --allowed-hosts *: allow connections from K8s Service DNS (default only allows localhost)
    //   --isolated: each Mcp-Session-Id gets its own browser context for session isolation
    //
    // Multi-replica support: The Mcp-Session-Id is stored in the database after the first
    // connection and reused by all backend pods so they share the same Playwright browser context.
    // See mcp-client.ts for session ID persistence logic.
    arguments: [
      "cli.js",
      "--headless",
      "--browser",
      "chromium",
      "--no-sandbox",
      "--host",
      "0.0.0.0",
      "--port",
      "8080",
      "--allowed-hosts",
      "*",
      "--isolated",
    ],
    httpPort: 8080,
  };

  // Read current catalog config before upsert to detect changes
  let existingCatalog = await InternalMcpCatalogModel.findById(
    PLAYWRIGHT_MCP_CATALOG_ID,
  );
  const legacyCatalogByName = await InternalMcpCatalogModel.findByName(
    LEGACY_PLAYWRIGHT_MCP_SERVER_NAME,
  );

  // One-time migration: remove legacy playwright catalog installations/resources.
  // This runs only when the old catalog name is present in the environment.
  if (
    existingCatalog?.name === LEGACY_PLAYWRIGHT_MCP_SERVER_NAME ||
    legacyCatalogByName
  ) {
    const catalogIdsToDelete = new Set<string>();
    if (existingCatalog?.name === LEGACY_PLAYWRIGHT_MCP_SERVER_NAME) {
      catalogIdsToDelete.add(existingCatalog.id);
    }
    if (legacyCatalogByName) {
      catalogIdsToDelete.add(legacyCatalogByName.id);
    }

    for (const catalogId of catalogIdsToDelete) {
      const deleted = await InternalMcpCatalogModel.delete(catalogId);
      if (deleted) {
        logger.info(
          { catalogId, legacyCatalogName: LEGACY_PLAYWRIGHT_MCP_SERVER_NAME },
          "Removed legacy Playwright catalog and related installations/resources",
        );
      }
    }

    existingCatalog = null;
  }

  // Only insert on first creation; never overwrite user edits on restart.
  // Future config changes (e.g., docker image pin updates) should use database migrations.
  await db
    .insert(schema.internalMcpCatalogTable)
    .values({
      id: PLAYWRIGHT_MCP_CATALOG_ID,
      name: PLAYWRIGHT_MCP_SERVER_NAME,
      description:
        "Browser automation for chat - each user gets their own isolated browser session",
      serverType: "local",
      requiresAuth: false,
      icon: PLAYWRIGHT_MCP_ICON,
      localConfig: playwrightLocalConfig,
    })
    .onConflictDoNothing();

  logger.info("Seeded Playwright browser preview catalog");
}

/**
 * Seeds test MCP server for development
 * This creates a simple MCP server in the catalog that has one tool: print_archestra_test
 */
async function seedTestMcpServer(): Promise<void> {
  // Only seed in development, or when ENABLE_TEST_MCP_SERVER is explicitly set (e.g., in CI e2e tests)
  if (config.production && !config.test.enableTestMcpServer) {
    return;
  }

  const existing = await InternalMcpCatalogModel.findByName(
    "internal-dev-test-server",
  );
  if (existing) {
    logger.info("Test MCP server already exists in catalog, skipping");
    return;
  }

  await InternalMcpCatalogModel.create({
    name: "internal-dev-test-server",
    description:
      "Simple test MCP server for development. Has one tool that prints an env var.",
    serverType: "local",
    localConfig: {
      command: "sh",
      arguments: ["-c", testMcpServerCommand],
      transportType: "stdio",
      environment: [
        {
          key: "ARCHESTRA_TEST",
          type: "plain_text",
          promptOnInstallation: true,
          required: true,
          description: "Test value to print (any string)",
        },
      ],
    },
  });
  logger.info("Seeded test MCP server (internal-dev-test-server)");
}

/**
 * Creates team tokens for existing teams and organization
 * - Creates "Organization Token" if missing
 * - Creates team tokens for each team if missing
 */
async function seedTeamTokens(): Promise<void> {
  // Get the default organization
  const org = await OrganizationModel.getOrCreateDefaultOrganization();

  // Ensure organization token exists
  const orgToken = await TeamTokenModel.ensureOrganizationToken();
  logger.info(
    { organizationId: org.id, tokenId: orgToken.id },
    "Ensured organization token exists",
  );

  // Get all teams for this organization and ensure they have tokens
  const teams = await TeamModel.findByOrganization(org.id);
  for (const team of teams) {
    const teamToken = await TeamTokenModel.ensureTeamToken(team.id, team.name);
    logger.info(
      { teamId: team.id, teamName: team.name, tokenId: teamToken.id },
      "Ensured team token exists",
    );
  }
}

/**
 * Seeds chat API keys from environment variables.
 * For each provider with ARCHESTRA_CHAT_<PROVIDER>_API_KEY set, creates an org-wide API key
 * and syncs models from the provider.
 *
 * This enables:
 * - E2E tests: WireMock mock keys are set via env vars, models sync automatically
 * - Production: Admins can bootstrap org-wide keys via env vars
 */
async function seedChatApiKeysFromEnv(): Promise<void> {
  const org = await OrganizationModel.getOrCreateDefaultOrganization();

  for (const provider of SupportedProviders) {
    const apiKeyValue = getProviderEnvApiKey(provider);

    // Skip providers without API keys configured
    if (!apiKeyValue || apiKeyValue.trim() === "") {
      continue;
    }

    const decision = decideEnvSeed(provider);
    if (decision.kind === "skip") {
      logger.warn(
        { provider },
        `Skipping env-seeded provider: ${decision.reason}`,
      );
      continue;
    }

    // Check if API key already exists for this provider
    const existing = await LlmProviderApiKeyModel.findByScope(
      org.id,
      provider,
      "org",
    );

    if (existing) {
      // Sync models if not already synced
      await syncModelsForApiKey(
        existing.id,
        provider,
        apiKeyValue,
        decision.persistedBaseUrl,
      );
      continue;
    }

    // Create a secret with the API key from env
    const secret = await secretManager().createSecret(
      { apiKey: apiKeyValue },
      `chatapikey-env-${provider}`,
    );

    // Create the API key
    const apiKey = await LlmProviderApiKeyModel.create({
      organizationId: org.id,
      name: getProviderDisplayName(provider),
      provider: provider,
      secretId: secret.id,
      scope: "org",
      userId: null,
      teamId: null,
      baseUrl: decision.persistedBaseUrl,
      isPrimary: true,
    });

    logger.info(
      { provider, apiKeyId: apiKey.id },
      "Created chat API key from environment variable",
    );

    // Sync models from provider. persistedBaseUrl carries the required endpoint for
    // azure/vllm (so their fetchers hit the right host) and null elsewhere (the
    // fetchers fall back to their own default — unchanged from before).
    await syncModelsForApiKey(
      apiKey.id,
      provider,
      apiKeyValue,
      decision.persistedBaseUrl,
    );
  }
}

type EnvSeedDecision =
  | { kind: "skip"; reason: string }
  | { kind: "create"; persistedBaseUrl: string | null };

/**
 * Decide how to seed a provider whose env API key is set. Pure (config-only, no IO)
 * so the gap-handling logic is unit-testable without DB or network.
 *
 * @public — unit-tested in seed.test.ts
 */
export function decideEnvSeed(provider: SupportedProvider): EnvSeedDecision {
  const baseUrl = getProviderConfiguredBaseUrl(provider);

  // Per-user providers (GitHub Copilot) must never be seeded as an org-wide key
  // from a shared env token — each user connects their own account.
  if (providerRequiresPerUserCredential(provider)) {
    return {
      kind: "skip",
      reason: "per-user provider; each user connects their own account",
    };
  }

  if (PROVIDERS_REQUIRING_BASE_URL.has(provider) && baseUrl === undefined) {
    return { kind: "skip", reason: "required base URL is not configured" };
  }

  // Persist the base URL only for providers that require one (azure/vllm): it pins
  // the required infra endpoint on the key, same as a manual UI add. Other providers
  // keep null so a later ARCHESTRA_*_BASE_URL change still takes effect via the
  // runtime fallback (the per-key URL is preferred over config when set).
  return {
    kind: "create",
    persistedBaseUrl: PROVIDERS_REQUIRING_BASE_URL.has(provider)
      ? (baseUrl ?? null)
      : null,
  };
}

/**
 * Sync models for an API key. Bedrock's fetcher throws without a base URL; that is
 * caught here so a key-only/IAM Bedrock seed still creates a usable key (chat falls
 * back to the us-east-1 region) — its model list just stays empty until a base URL
 * is configured.
 */
async function syncModelsForApiKey(
  apiKeyId: string,
  provider: SupportedProvider,
  apiKeyValue: string,
  baseUrl?: string | null,
): Promise<void> {
  try {
    await modelSyncService.syncModelsForApiKey({
      apiKeyId,
      provider,
      apiKeyValue,
      baseUrl,
    });
    logger.info({ provider, apiKeyId }, "Synced models for API key");
  } catch (error) {
    logger.error(
      {
        provider,
        apiKeyId,
        errorMessage: error instanceof Error ? error.message : String(error),
      },
      "Failed to sync models for API key",
    );
  }
}

/**
 * Get display name for a provider.
 */
function getProviderDisplayName(provider: SupportedProvider): string {
  const displayNames: Record<SupportedProvider, string> = {
    anthropic: "Anthropic",
    openai: "OpenAI",
    openrouter: "OpenRouter",
    gemini: "Google",
    cerebras: "Cerebras",
    cohere: "Cohere",
    mistral: "Mistral",
    perplexity: "Perplexity AI",
    groq: "Groq",
    xai: "xAI",
    ollama: "Ollama",
    vllm: "vLLM",
    zhipuai: "ZhipuAI",
    deepseek: "DeepSeek",
    "github-copilot": "GitHub Copilot",
    bedrock: "AWS Bedrock",
    minimax: "MiniMax",
    azure: "Azure AI Foundry",
  };
  return displayNames[provider];
}

/**
 * Migrates existing Playwright tool assignments to use dynamic credentials.
 * Static credentials break user isolation since multiple users would share
 * the same browser session. This ensures all Playwright assignments use
 * credentialResolutionMode="dynamic".
 */
async function migratePlaywrightToolsToDynamicCredential(): Promise<void> {
  // Find all tool IDs belonging to the Playwright catalog
  const playwrightTools = await db
    .select({ id: schema.toolsTable.id })
    .from(schema.toolsTable)
    .where(eq(schema.toolsTable.catalogId, PLAYWRIGHT_MCP_CATALOG_ID));

  if (playwrightTools.length === 0) return;

  const playwrightToolIds = playwrightTools.map((t) => t.id);

  // Update all assignments that still use static credentials
  const result = await db
    .update(schema.agentToolsTable)
    .set({
      credentialResolutionMode: "dynamic",
      mcpServerId: null,
    })
    .where(
      and(
        inArray(schema.agentToolsTable.toolId, playwrightToolIds),
        eq(schema.agentToolsTable.credentialResolutionMode, "static"),
      ),
    );

  const count = result.rowCount ?? 0;
  if (count > 0) {
    logger.info(
      { updatedCount: count },
      "Migrated Playwright tool assignments to dynamic credentials",
    );
  }
}

async function migrateSecretsToEncrypted(): Promise<void> {
  await withDbTransaction(async (tx) => {
    const rows = await tx.select().from(schema.secretsTable);
    let migrated = 0;

    for (const row of rows) {
      if (isEncryptedSecret(row.secret)) continue;

      await tx
        .update(schema.secretsTable)
        .set({ secret: encryptSecretValue(row.secret) })
        .where(eq(schema.secretsTable.id, row.id));
      migrated++;
    }

    if (migrated > 0) {
      logger.info(
        { migratedCount: migrated },
        "Migrated plaintext secrets to encrypted format",
      );
    }
  });
}

/**
 * Ensures all existing members have a personal default chat agent.
 * Runs on startup to backfill members created before this feature.
 */
async function ensureExistingUsersHavePersonalChatAgents(): Promise<void> {
  const membersWithoutDefault = await db
    .select({
      userId: schema.membersTable.userId,
      organizationId: schema.membersTable.organizationId,
    })
    .from(schema.membersTable)
    .where(isNull(schema.membersTable.defaultAgentId));

  if (membersWithoutDefault.length === 0) return;

  let created = 0;
  for (const member of membersWithoutDefault) {
    try {
      await AgentModel.ensurePersonalChatAgent({
        userId: member.userId,
        organizationId: member.organizationId,
      });
      created++;
    } catch (error) {
      logger.error(
        {
          err: error,
          userId: member.userId,
          organizationId: member.organizationId,
        },
        "Failed to create personal chat agent for existing member",
      );
    }
  }

  if (created > 0) {
    logger.info(
      { count: created },
      "Created personal chat agents for existing members",
    );
  }
}

/**
 * Ensures every member has a personal MCP gateway. Runs on startup to backfill
 * members created before this feature. Single LEFT JOIN + bulk INSERT.
 */
async function ensureExistingUsersHavePersonalMcpGateways(): Promise<void> {
  try {
    const created = await AgentModel.bulkBackfillPersonalMcpGateways();
    if (created > 0) {
      logger.info(
        { count: created },
        "Created personal MCP gateways for existing members",
      );
    }
  } catch (error) {
    logger.error(
      { err: error },
      "Failed to backfill personal MCP gateways for existing members",
    );
  }
}

/**
 * Ensures every member has a personal LLM proxy. Runs on startup to backfill
 * members created before this feature. Single LEFT JOIN + bulk INSERT.
 */
async function ensureExistingUsersHavePersonalLlmProxies(): Promise<void> {
  try {
    const created = await AgentModel.bulkBackfillPersonalLlmProxies();
    if (created > 0) {
      logger.info(
        { count: created },
        "Created personal LLM proxies for existing members",
      );
    }
  } catch (error) {
    logger.error(
      { err: error },
      "Failed to backfill personal LLM proxies for existing members",
    );
  }
}

/**
 * Turn on the Agent Skill tools for every organization that hasn't already
 * opted in. Skills are a default capability: newly created agents inherit the
 * model-facing skill tools and the slash-command toggle unlocks without an
 * admin first clicking "enable". Pre-existing agents are not retrofitted.
 */
async function enableSkillToolsForExistingOrgs(): Promise<void> {
  try {
    const enabled = await OrganizationModel.enableSkillToolsForAllOrgs();
    if (enabled > 0) {
      logger.info(
        { count: enabled },
        "Enabled Agent Skill tools by default for organizations",
      );
    }
  } catch (error) {
    logger.error(
      { err: error },
      "Failed to enable Agent Skill tools by default",
    );
  }
}

export async function seedRequiredStartingData(): Promise<void> {
  ensureEncryptionKeyAvailable();
  await migrateSecretsToEncrypted();
  await seedDefaultUserAndOrg();
  // Create default agents before seeding internal agents
  await AgentModel.getLLMProxyOrCreateDefault();
  await syncBuiltInAgents();
  await syncBuiltInSkills();
  await seedArchestraCatalogAndTools();
  await enableSkillToolsForExistingOrgs();
  await seedPlaywrightCatalog();
  await migratePlaywrightToolsToDynamicCredential();
  await seedTestMcpServer();
  await seedTeamTokens();
  await seedChatApiKeysFromEnv();
  // Ensure all existing members have a personal default chat agent
  await ensureExistingUsersHavePersonalChatAgents();
  // Ensure all existing members have a personal MCP gateway
  await ensureExistingUsersHavePersonalMcpGateways();
  await ensureExistingUsersHavePersonalLlmProxies();
  // Clean up orphaned MCP HTTP sessions (older than 24h)
  await McpHttpSessionModel.deleteExpired();
}

async function getOrganizationsForBuiltInAgentSync(): Promise<
  Array<{ id: string }>
> {
  const organizations = await db
    .select({ id: schema.organizationsTable.id })
    .from(schema.organizationsTable);

  if (organizations.length > 0) {
    return organizations;
  }

  const organization = await OrganizationModel.getOrCreateDefaultOrganization();
  return [{ id: organization.id }];
}

function shouldSyncBuiltInAgentSystemPrompt(params: {
  builtInAgentId: string;
  systemPrompt: string | null;
}): boolean {
  if (params.systemPrompt === null) {
    return false;
  }

  return (
    params.builtInAgentId === BUILT_IN_AGENT_IDS.POLICY_CONFIG &&
    SUPERSEDED_POLICY_CONFIG_SYSTEM_PROMPTS.includes(params.systemPrompt)
  );
}

const LEGACY_POLICY_CONFIG_SYSTEM_PROMPT = `Analyze this MCP tool and determine security policies:

Tool: {tool.name}
Description: {tool.description}
MCP Server: {mcpServerName}
Parameters: {tool.parameters}

Determine:

1. toolInvocationAction (enum) - When should this tool be allowed?
   - "allow_when_context_is_untrusted": Safe to invoke even with untrusted data (read-only, doesn't leak sensitive data)
   - "block_when_context_is_untrusted": Only invoke when context is trusted (could leak data if untrusted input is present)
   - "block_always": Never invoke automatically (writes data, executes code, sends data externally)

2. trustedDataAction (enum) - How should the tool's results be treated?
   - "mark_as_trusted": Internal systems (databases, APIs, dev tools like list-endpoints/get-config)
   - "mark_as_untrusted": External/filesystem data where exact values are safe to use directly
   - "sanitize_with_dual_llm": Untrusted data that needs summarization without exposing exact values
   - "block_always": Highly sensitive or dangerous output that should be blocked entirely

Examples:
- Internal dev tools: invocation="allow_when_context_is_untrusted", result="mark_as_trusted"
- Database queries: invocation="allow_when_context_is_untrusted", result="mark_as_trusted"
- File reads (code/config): invocation="allow_when_context_is_untrusted", result="mark_as_untrusted"
- Web search/scraping: invocation="allow_when_context_is_untrusted", result="sanitize_with_dual_llm"
- File writes: invocation="block_always", result="mark_as_trusted"
- External APIs (raw data): invocation="block_when_context_is_untrusted", result="mark_as_untrusted"
- Code execution: invocation="block_always", result="mark_as_untrusted"`;

// Shipped policy-config prompts we have since replaced. An org still on any of
// these is pristine (never customized) and is auto-upgraded to the current
// POLICY_CONFIG_SYSTEM_PROMPT on startup; any other stored prompt is treated as
// admin-edited and left untouched.
const SUPERSEDED_POLICY_CONFIG_SYSTEM_PROMPTS: readonly string[] = [
  LEGACY_POLICY_CONFIG_SYSTEM_PROMPT,
  PREVIOUS_POLICY_CONFIG_SYSTEM_PROMPT,
];
