import { createId } from "./id.js";
import { prisma } from "./prisma.js";

let ensureCustomAgentsTablePromise: Promise<void> | null = null;

export async function ensureCustomAgentsTable() {
  if (!ensureCustomAgentsTablePromise) {
    ensureCustomAgentsTablePromise = (async () => {
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS custom_agents (
          id CHAR(36) NOT NULL PRIMARY KEY,
          law_firm_id CHAR(36) NOT NULL,
          team_id CHAR(36) NOT NULL,
          agent_name VARCHAR(180) NOT NULL,
          description VARCHAR(500) NULL,
          initial_instructions_text TEXT NOT NULL,
          system_prompt LONGTEXT NOT NULL,
          last_prompt_suggestion_ai_run_id CHAR(36) NULL,
          created_by_user_id CHAR(36) NULL,
          updated_by_user_id CHAR(36) NULL,
          created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          archived_at DATETIME NULL,
          UNIQUE KEY uq_custom_agents_team_name (team_id, agent_name),
          KEY idx_custom_agents_law_firm_team (law_firm_id, team_id),
          KEY idx_custom_agents_law_firm_active (law_firm_id, archived_at),
          CONSTRAINT fk_custom_agents_law_firm
            FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
          CONSTRAINT fk_custom_agents_team
            FOREIGN KEY (team_id) REFERENCES teams (id),
          CONSTRAINT fk_custom_agents_created_by
            FOREIGN KEY (created_by_user_id) REFERENCES users (id),
          CONSTRAINT fk_custom_agents_updated_by
            FOREIGN KEY (updated_by_user_id) REFERENCES users (id),
          CONSTRAINT fk_custom_agents_last_prompt_ai_run
            FOREIGN KEY (last_prompt_suggestion_ai_run_id) REFERENCES ai_runs (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
    })().catch((error) => {
      ensureCustomAgentsTablePromise = null;
      throw error;
    });
  }

  await ensureCustomAgentsTablePromise;
}

export async function listCatalogCustomAgents(input: { lawFirmId: string }) {
  await ensureCustomAgentsTable();

  return prisma.$queryRaw<
    Array<{
      id: string;
      agent_name: string;
      description: string | null;
      system_prompt: string;
      updated_at: Date;
      team_id: string;
      team_name: string;
    }>
  >`
    SELECT
      ca.id,
      ca.agent_name,
      ca.description,
      ca.system_prompt,
      ca.updated_at,
      ca.team_id,
      t.name AS team_name
    FROM custom_agents ca
    INNER JOIN teams t ON t.id = ca.team_id
    WHERE ca.law_firm_id = ${input.lawFirmId}
      AND ca.archived_at IS NULL
    ORDER BY ca.agent_name ASC
  `;
}

export async function listAccessibleCustomAgents(input: {
  lawFirmId: string;
  userId: string;
}) {
  await ensureCustomAgentsTable();

  return prisma.$queryRaw<
    Array<{
      id: string;
      agent_name: string;
      description: string | null;
      team_id: string;
      team_name: string;
      created_at: Date;
      updated_at: Date;
    }>
  >`
    SELECT
      ca.id,
      ca.agent_name,
      ca.description,
      ca.team_id,
      t.name AS team_name,
      ca.created_at,
      ca.updated_at
    FROM custom_agents ca
    INNER JOIN teams t ON t.id = ca.team_id
    INNER JOIN team_memberships tm
      ON tm.team_id = ca.team_id
     AND tm.user_id = ${input.userId}
     AND tm.law_firm_id = ${input.lawFirmId}
    WHERE ca.law_firm_id = ${input.lawFirmId}
      AND ca.archived_at IS NULL
    ORDER BY ca.agent_name ASC
  `;
}

export async function getAccessibleCustomAgentContext(input: {
  lawFirmId: string;
  userId: string;
  agentId: string;
}) {
  await ensureCustomAgentsTable();

  const [row] = await prisma.$queryRaw<
    Array<{
      id: string;
      agent_name: string;
      description: string | null;
      initial_instructions_text: string;
      system_prompt: string;
      team_id: string;
      team_name: string;
      created_at: Date;
      updated_at: Date;
    }>
  >`
    SELECT
      ca.id,
      ca.agent_name,
      ca.description,
      ca.initial_instructions_text,
      ca.system_prompt,
      ca.team_id,
      t.name AS team_name,
      ca.created_at,
      ca.updated_at
    FROM custom_agents ca
    INNER JOIN teams t ON t.id = ca.team_id
    INNER JOIN team_memberships tm
      ON tm.team_id = ca.team_id
     AND tm.user_id = ${input.userId}
     AND tm.law_firm_id = ${input.lawFirmId}
    WHERE ca.law_firm_id = ${input.lawFirmId}
      AND ca.id = ${input.agentId}
      AND ca.archived_at IS NULL
    LIMIT 1
  `;

  return row ?? null;
}

export async function createCustomAgent(input: {
  lawFirmId: string;
  teamId: string;
  agentName: string;
  description?: string | null;
  initialInstructions: string;
  systemPrompt: string;
  actorUserId: string;
  lastPromptSuggestionAiRunId?: string | null;
}) {
  await ensureCustomAgentsTable();

  const customAgentId = createId();
  await prisma.$executeRaw`
    INSERT INTO custom_agents (
      id,
      law_firm_id,
      team_id,
      agent_name,
      description,
      initial_instructions_text,
      system_prompt,
      last_prompt_suggestion_ai_run_id,
      created_by_user_id,
      updated_by_user_id,
      created_at,
      updated_at
    ) VALUES (
      ${customAgentId},
      ${input.lawFirmId},
      ${input.teamId},
      ${input.agentName},
      ${input.description ?? null},
      ${input.initialInstructions},
      ${input.systemPrompt},
      ${input.lastPromptSuggestionAiRunId ?? null},
      ${input.actorUserId},
      ${input.actorUserId},
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
  `;

  return customAgentId;
}