import { createHash } from "node:crypto";
import { readFile } from "node:fs/promises";
import path from "node:path";
import process from "node:process";
import { setTimeout as delay } from "node:timers/promises";
import { fileURLToPath } from "node:url";

import { createClient, type SupabaseClient } from "@supabase/supabase-js";

type ContentFormat = "text" | "text_template" | "chat_bundle" | "multimodal_bundle";

type PromptSeed = {
  promptKey: string;
  module: string;
  scene: string;
  contentFormat: ContentFormat;
  description: string;
  payload: Record<string, unknown>;
  variables: Array<Record<string, unknown>>;
  notes: string;
  version: number;
};

type WriteMode = "auto" | "management-api" | "service-role";

type CliOptions = {
  accessToken: string;
  projectRef: string;
  targetEnv: string;
  providerScope: string;
  modelScope: string;
  applySchema: boolean;
  seedFile: string;
  promptStoreUrl: string;
  promptStoreServiceRoleKey: string;
  writeMode: WriteMode;
};

const SCRIPT_DIR = path.dirname(fileURLToPath(import.meta.url));
const REPO_ROOT = path.resolve(SCRIPT_DIR, "..");
const SCHEMA_PATH = path.join(REPO_ROOT, "supabase/sql/prompt-store-schema.sql");
const DEFAULT_SEED_FILE = path.join(REPO_ROOT, ".prompt-store/prompts.local.json");

const MANAGEMENT_API_BASE = "https://api.supabase.com/v1";
const MANAGEMENT_API_MAX_ATTEMPTS = 4;
const MANAGEMENT_API_RETRIABLE_STATUS_CODES = new Set([429, 502, 503, 504]);
const CREATED_BY = "scripts/seed-prompt-store.ts";

function createChecksum(payload: Record<string, unknown>): string {
  return createHash("sha256").update(JSON.stringify(payload)).digest("hex");
}

function requireOption(value: string | undefined, name: string): string {
  if (!value || !value.trim()) {
    throw new Error(`缺少必填参数: ${name}`);
  }

  return value.trim();
}

function parseProjectRefFromUrl(rawUrl: string): string {
  const hostname = new URL(rawUrl).hostname;
  const [projectRef] = hostname.split(".");

  if (!projectRef) {
    throw new Error(`无法从 Supabase URL 解析 project ref: ${rawUrl}`);
  }

  return projectRef;
}

function parseWriteMode(rawValue: string | undefined, patOnlyRequested: boolean): WriteMode {
  if (patOnlyRequested) {
    return "management-api";
  }

  const normalized = String(rawValue ?? "").trim().toLowerCase();
  if (!normalized) {
    return "management-api";
  }

  if (normalized === "auto") {
    return "auto";
  }

  if (normalized === "management-api" || normalized === "pat" || normalized === "pat-only") {
    return "management-api";
  }

  if (normalized === "service-role" || normalized === "service_role") {
    return "service-role";
  }

  throw new Error(`不支持的写入模式: ${rawValue}`);
}

function resolvePathFromRepo(value: string): string {
  return path.isAbsolute(value) ? value : path.join(REPO_ROOT, value);
}

function parseArgs(argv: string[]): CliOptions {
  const values = new Map<string, string>();
  let applySchema = true;
  let patOnlyRequested = false;

  for (const arg of argv) {
    if (!arg.startsWith("--")) {
      continue;
    }

    if (arg === "--skip-schema") {
      applySchema = false;
      continue;
    }

    if (arg === "--pat-only") {
      patOnlyRequested = true;
      continue;
    }

    const [flag, rawValue] = arg.slice(2).split("=", 2);
    values.set(flag, rawValue ?? "");
  }

  const accessToken = requireOption(
    values.get("access-token") || process.env.SUPABASE_ACCESS_TOKEN,
    "SUPABASE_ACCESS_TOKEN 或 --access-token",
  );
  const rawProjectRef = values.get("project-ref") || process.env.PROMPT_SUPABASE_PROJECT_REF || "";
  const rawSupabaseUrl = values.get("supabase-url") || process.env.SUPABASE_URL || process.env.PROMPT_SUPABASE_URL || "";
  const projectRef = rawProjectRef.trim() || (rawSupabaseUrl.trim() ? parseProjectRefFromUrl(rawSupabaseUrl.trim()) : "");
  const promptStoreUrl = values.get("prompt-store-url") || process.env.PROMPT_SUPABASE_URL || rawSupabaseUrl || "";
  const promptStoreServiceRoleKey = values.get("prompt-store-service-role-key")
    || process.env.PROMPT_SUPABASE_SERVICE_ROLE_KEY
    || process.env.PROMPT_SUPABASE_service_key
    || "";
  const writeMode = parseWriteMode(
    values.get("write-mode") || process.env.PROMPT_STORE_WRITE_MODE,
    patOnlyRequested,
  );

  return {
    accessToken,
    projectRef: requireOption(projectRef, "--project-ref 或 SUPABASE_URL/PROMPT_SUPABASE_URL"),
    targetEnv: values.get("target-env") || process.env.PROMPT_ENV || "prod",
    providerScope: values.get("provider-scope") || "*",
    modelScope: values.get("model-scope") || "*",
    applySchema,
    seedFile: resolvePathFromRepo(values.get("seed-file") || process.env.PROMPT_STORE_SEED_FILE || DEFAULT_SEED_FILE),
    promptStoreUrl: promptStoreUrl.trim(),
    promptStoreServiceRoleKey: promptStoreServiceRoleKey.trim(),
    writeMode,
  };
}

function createPromptStoreClient(options: CliOptions): SupabaseClient | null {
  if (options.writeMode === "management-api") {
    return null;
  }

  if (!options.promptStoreUrl || !options.promptStoreServiceRoleKey) {
    if (options.writeMode === "service-role") {
      throw new Error("当前已强制 service_role 写入，但缺少 PROMPT_SUPABASE_URL 或 PROMPT_SUPABASE_SERVICE_ROLE_KEY");
    }

    return null;
  }

  return createClient(options.promptStoreUrl, options.promptStoreServiceRoleKey, {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  });
}

function describeWriteChannel(options: CliOptions, promptStoreClient: SupabaseClient | null): string {
  if (promptStoreClient) {
    return options.writeMode === "service-role" ? "service_role (forced)" : "service_role";
  }

  return options.writeMode === "management-api" ? "management_api (PAT-only)" : "management_api";
}

function formatManagementApiError(status: number, rawText: string): string {
  if (status === 403 && rawText.includes("error code: 1010")) {
    return [
      "Supabase Management API 被 Cloudflare 拒绝（1010）。",
      "请改用 service_role 写入：设置 PROMPT_SUPABASE_URL 和 PROMPT_SUPABASE_SERVICE_ROLE_KEY，或使用 --write-mode=service-role。",
    ].join(" ");
  }

  return `Supabase Management API 请求失败: ${status} ${rawText}`;
}

async function runManagementQuery(
  projectRef: string,
  accessToken: string,
  query: string,
  variables: unknown[] = [],
): Promise<unknown[]> {
  const url = `${MANAGEMENT_API_BASE}/projects/${projectRef}/database/query`;
  let lastError = "";

  for (let attempt = 0; attempt < MANAGEMENT_API_MAX_ATTEMPTS; attempt += 1) {
    const response = await fetch(url, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${accessToken}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ query, variables }),
    });

    const rawText = await response.text();
    if (response.ok) {
      if (!rawText.trim()) {
        return [];
      }

      const parsed = JSON.parse(rawText) as unknown;
      return Array.isArray(parsed) ? parsed : [];
    }

    lastError = formatManagementApiError(response.status, rawText);
    if (!MANAGEMENT_API_RETRIABLE_STATUS_CODES.has(response.status) || attempt === MANAGEMENT_API_MAX_ATTEMPTS - 1) {
      throw new Error(lastError);
    }

    await delay(500 * 2 ** attempt);
  }

  throw new Error(lastError || "Supabase Management API 请求失败");
}

function normalizeQueryRows(value: unknown): Array<Record<string, unknown>> {
  return Array.isArray(value)
    ? value.filter((row): row is Record<string, unknown> => Boolean(row) && typeof row === "object" && !Array.isArray(row))
    : [];
}

function isPlainObject(value: unknown): value is Record<string, unknown> {
  return Boolean(value) && typeof value === "object" && !Array.isArray(value);
}

function normalizePromptSeed(value: unknown, index: number): PromptSeed {
  if (!isPlainObject(value)) {
    throw new Error(`seed[${index}] 不是对象`);
  }

  const seed = value as Partial<PromptSeed>;
  const contentFormat = String(seed.contentFormat ?? "").trim() as ContentFormat;
  if (!["text", "text_template", "chat_bundle", "multimodal_bundle"].includes(contentFormat)) {
    throw new Error(`seed[${index}] contentFormat 无效`);
  }

  if (!isPlainObject(seed.payload)) {
    throw new Error(`seed[${index}] payload 必须是对象`);
  }

  if (!Array.isArray(seed.variables)) {
    throw new Error(`seed[${index}] variables 必须是数组`);
  }

  return {
    promptKey: requireOption(String(seed.promptKey ?? ""), `seed[${index}].promptKey`),
    module: requireOption(String(seed.module ?? ""), `seed[${index}].module`),
    scene: requireOption(String(seed.scene ?? ""), `seed[${index}].scene`),
    contentFormat,
    description: String(seed.description ?? ""),
    payload: seed.payload,
    variables: seed.variables.filter(isPlainObject),
    notes: String(seed.notes ?? ""),
    version: Number.isInteger(seed.version) && seed.version > 0 ? seed.version : 1,
  };
}

async function loadPromptSeeds(seedFile: string): Promise<PromptSeed[]> {
  const rawText = await readFile(seedFile, "utf8").catch((error: unknown) => {
    const message = error instanceof Error ? error.message : String(error);
    throw new Error(`读取 prompt seed 文件失败: ${seedFile}; ${message}`);
  });
  const parsed = JSON.parse(rawText) as unknown;
  const rawSeeds = isPlainObject(parsed) && Array.isArray(parsed.seeds) ? parsed.seeds : [];
  if (rawSeeds.length === 0) {
    throw new Error(`prompt seed 文件没有 seeds 数组: ${seedFile}`);
  }

  return rawSeeds.map(normalizePromptSeed);
}

async function applySchema(projectRef: string, accessToken: string): Promise<void> {
  const schemaSql = await readFile(SCHEMA_PATH, "utf8");
  await runManagementQuery(projectRef, accessToken, schemaSql);
}

async function upsertPromptSeed(
  projectRef: string,
  accessToken: string,
  seed: PromptSeed,
  targetEnv: string,
  providerScope: string,
  modelScope: string,
  promptStoreClient: SupabaseClient | null,
): Promise<void> {
  const payloadJson = seed.payload;
  const variablesJson = seed.variables;
  const checksum = createChecksum(payloadJson);

  if (promptStoreClient) {
    const { error } = await promptStoreClient.rpc("prompt_store_upsert_active_version", {
      p_prompt_key: seed.promptKey,
      p_module: seed.module,
      p_scene: seed.scene,
      p_content_format: seed.contentFormat,
      p_description: seed.description,
      p_target_env: targetEnv,
      p_provider_scope: providerScope,
      p_model_scope: modelScope,
      p_version: seed.version,
      p_payload_json: payloadJson,
      p_variables_json: variablesJson,
      p_notes: seed.notes,
      p_checksum: checksum,
      p_created_by: CREATED_BY,
    });

    if (error) {
      throw new Error(`service_role upsert 失败 (${seed.promptKey}): ${error.message}`);
    }

    return;
  }

  await runManagementQuery(
    projectRef,
    accessToken,
    `select public.prompt_store_upsert_active_version(
      $1::text,
      $2::text,
      $3::text,
      $4::text,
      $5::text,
      $6::text,
      $7::text,
      $8::text,
      $9::integer,
      $10::jsonb,
      $11::jsonb,
      $12::text,
      $13::text,
      $14::text
    ) as version_id`,
    [
      seed.promptKey,
      seed.module,
      seed.scene,
      seed.contentFormat,
      seed.description,
      targetEnv,
      providerScope,
      modelScope,
      seed.version,
      JSON.stringify(payloadJson),
      JSON.stringify(variablesJson),
      seed.notes,
      checksum,
      CREATED_BY,
    ],
  );
}

async function verifyPromptSeeds(
  projectRef: string,
  accessToken: string,
  seeds: PromptSeed[],
  targetEnv: string,
  promptStoreClient: SupabaseClient | null,
): Promise<void> {
  const activeRows = promptStoreClient
    ? await (async () => {
      const { data, error } = await promptStoreClient
        .from("prompt_active_versions")
        .select("prompt_key, version, content_format")
        .order("prompt_key");

      if (error) {
        throw new Error(`service_role 读取 active prompts 失败: ${error.message}`);
      }

      return normalizeQueryRows(data ?? []);
    })()
    : normalizeQueryRows(await runManagementQuery(
      projectRef,
      accessToken,
      "select prompt_key, version, content_format from public.prompt_active_versions order by prompt_key",
    ));

  const activeKeys = new Set(activeRows.map((row) => String(row.prompt_key ?? "")));
  for (const seed of seeds) {
    if (!activeKeys.has(seed.promptKey)) {
      throw new Error(`未在 active 视图中找到 prompt_key=${seed.promptKey}`);
    }
  }

  for (const seed of seeds) {
    const resolvedRows = promptStoreClient
      ? await (async () => {
        const { data, error } = await promptStoreClient.rpc("prompt_store_resolve_active_version", {
          p_prompt_key: seed.promptKey,
          p_target_env: targetEnv,
          p_provider: "mimo",
          p_model: "mimo-v2.5",
        });

        if (error) {
          throw new Error(`service_role 解析 prompt 失败 (${seed.promptKey}): ${error.message}`);
        }

        return normalizeQueryRows(data ?? []);
      })()
      : normalizeQueryRows(await runManagementQuery(
        projectRef,
        accessToken,
        `select prompt_key, version, content_format, payload_json
         from public.prompt_store_resolve_active_version($1::text, $2::text, $3::text, $4::text)`,
        [seed.promptKey, targetEnv, "mimo", "mimo-v2.5"],
      ));

    if (resolvedRows.length !== 1) {
      throw new Error(`prompt_key=${seed.promptKey} 解析结果异常，返回 ${resolvedRows.length} 条`);
    }

    const payload = resolvedRows[0]?.payload_json;
    if (!payload || typeof payload !== "object") {
      throw new Error(`prompt_key=${seed.promptKey} 未返回有效 payload_json`);
    }
  }
}

async function main(): Promise<void> {
  const options = parseArgs(process.argv.slice(2));
  const seeds = await loadPromptSeeds(options.seedFile);
  const promptStoreClient = createPromptStoreClient(options);

  console.log(`[prompt-store] 目标项目: ${options.projectRef}`);
  console.log(`[prompt-store] 目标环境: ${options.targetEnv}`);
  console.log(`[prompt-store] seed 文件: ${options.seedFile}`);
  console.log(`[prompt-store] 准备写入 ${seeds.length} 条 prompt`);
  console.log(`[prompt-store] 写入通道: ${describeWriteChannel(options, promptStoreClient)}`);

  if (options.applySchema) {
    console.log("[prompt-store] 开始应用 schema SQL ...");
    await applySchema(options.projectRef, options.accessToken);
    console.log("[prompt-store] schema 应用完成");
  }

  for (const seed of seeds) {
    console.log(`[prompt-store] upsert ${seed.promptKey}`);
    await upsertPromptSeed(
      options.projectRef,
      options.accessToken,
      seed,
      options.targetEnv,
      options.providerScope,
      options.modelScope,
      promptStoreClient,
    );
  }

  console.log("[prompt-store] 开始验证 active prompt 与解析函数 ...");
  await verifyPromptSeeds(options.projectRef, options.accessToken, seeds, options.targetEnv, promptStoreClient);
  console.log(`[prompt-store] 完成，共验证 ${seeds.length} 条 prompt`);
}

main().catch((error) => {
  console.error("[prompt-store] 失败:", error instanceof Error ? error.message : error);
  process.exitCode = 1;
});
