import { column, type BucketThreshold, type TableSchema } from "@internal/tsql";
import { z } from "zod";
import { autoFormatSQL } from "~/components/code/TSQLEditor";
import { runFriendlyStatus, runStatusTitleFromStatus } from "~/components/runs/v3/TaskRunStatus";
import { logger } from "~/services/logger.server";

export const QueryScopeSchema = z.enum(["organization", "project", "environment"]);
export type QueryScope = z.infer<typeof QueryScopeSchema>;

/**
 * Environment type values
 */
const ENVIRONMENT_TYPES = ["PRODUCTION", "STAGING", "DEVELOPMENT", "PREVIEW"] as const;

/**
 * Machine preset values
 */
const MACHINE_PRESETS = [
  "micro",
  "small-1x",
  "small-2x",
  "medium-1x",
  "medium-2x",
  "large-1x",
  "large-2x",
] as const;

/**
 * Schema definition for the runs table (trigger_dev.task_runs_v2)
 */
export const runsSchema: TableSchema = {
  name: "runs",
  clickhouseName: "trigger_dev.task_runs_v2",
  description: "Task runs - stores all task execution records",
  timeConstraint: "triggered_at",
  useFinal: true,
  tenantColumns: {
    organizationId: "organization_id",
    projectId: "project_id",
    environmentId: "environment_id",
  },
  requiredFilters: [{ column: "engine", value: "V2" }],
  columns: {
    run_id: {
      name: "run_id",
      clickhouseName: "friendly_id",
      ...column("String", {
        description:
          "A unique ID for a run. They always start with `run_`, e.g., run_cm1a2b3c4d5e6f7g8h9i",
        customRenderType: "runId",
        example: "run_cm1a2b3c4d5e6f7g8h9i",
        coreColumn: true,
      }),
    },
    environment: {
      name: "environment",
      clickhouseName: "environment_id",
      ...column("String", { description: "The environment slug", example: "prod" }),
      fieldMapping: "environment",
      customRenderType: "environment",
    },
    project: {
      name: "project",
      clickhouseName: "project_id",
      ...column("String", {
        description: "The project reference, they always start with `proj_`.",
        example: "proj_howcnaxbfxdmwmxazktx",
      }),
      fieldMapping: "project",
      customRenderType: "project",
    },
    environment_type: {
      name: "environment_type",
      ...column("LowCardinality(String)", {
        description: "Environment type",
        allowedValues: [...ENVIRONMENT_TYPES],
        customRenderType: "environmentType",
        example: "PRODUCTION",
      }),
    },
    attempt_count: {
      name: "attempt_count",
      clickhouseName: "attempt",
      ...column("UInt8", {
        description: "Number of attempts (starts at 1)",
        example: "1",
        customRenderType: "number",
      }),
    },
    status: {
      name: "status",
      ...column("LowCardinality(String)", {
        description: "Run status",
        allowedValues: [...runFriendlyStatus],
        valueMap: runStatusTitleFromStatus,
        customRenderType: "runStatus",
        example: "Completed",
        coreColumn: true,
      }),
    },
    is_finished: {
      name: "is_finished",
      ...column("UInt8", {
        description:
          "Whether the run is finished. This includes failed and successful runs. (0 or 1)",
        example: "0",
      }),
      expression:
        "if(status IN ('COMPLETED_SUCCESSFULLY', 'COMPLETED_WITH_ERRORS', 'CANCELED', 'TIMED_OUT', 'CRASHED', 'SYSTEM_FAILURE', 'EXPIRED', 'PAUSED'), true, false)",
    },

    // Task & queue
    task_identifier: {
      name: "task_identifier",
      ...column("String", {
        description: "Task identifier/slug",
        example: "my-background-task",
        coreColumn: true,
      }),
    },
    queue: {
      name: "queue",
      ...column("String", {
        description: "Queue name",
        example: "task/my-background-task",
        customRenderType: "queue",
      }),
    },

    batch_id: {
      name: "batch_id",
      ...column("String", {
        description: "Batch ID (if part of a batch)",
        example: "batch_5678efgh",
        expression: "if(batch_id = '', NULL, 'batch_' || batch_id)",
      }),
      whereTransform: (value: string) => value.replace(/^batch_/, ""),
    },

    // Related runs
    root_run_id: {
      name: "root_run_id",
      ...column("String", {
        description: "Root run ID (for child runs)",
        example: "run_cm1a2b3c4d5e6f7g8h9i",
        customRenderType: "runId",
        expression: "if(root_run_id = '', NULL, 'run_' || root_run_id)",
      }),
      whereTransform: (value: string) => value.replace(/^run_/, ""),
    },
    parent_run_id: {
      name: "parent_run_id",
      ...column("String", {
        description: "Parent run ID (for child runs)",
        example: "run_cm1a2b3c4d5e6f7g8h9i",
        customRenderType: "runId",
        expression: "if(parent_run_id = '', NULL, 'run_' || parent_run_id)",
      }),
      whereTransform: (value: string) => value.replace(/^run_/, ""),
    },
    depth: {
      name: "depth",
      ...column("UInt8", { description: "Nesting depth (0 for root runs)", example: "0" }),
    },
    is_root_run: {
      name: "is_root_run",
      ...column("UInt8", { description: "Whether this is a root run (0 or 1)", example: "0" }),
      expression: "if(depth = 0, true, false)",
    },
    is_child_run: {
      name: "is_child_run",
      ...column("UInt8", { description: "Whether this is a child run (0 or 1)", example: "0" }),
      expression: "if(depth > 0, true, false)",
    },

    idempotency_key: {
      name: "idempotency_key",
      clickhouseName: "idempotency_key_user",
      ...column("String", {
        description: "Idempotency key (available from 4.3.3)",
        example: "user-123-action-456",
      }),
    },
    idempotency_key_scope: {
      name: "idempotency_key_scope",
      ...column("String", {
        description:
          "The idempotency key scope determines whether a task should be considered unique within a parent run, a specific attempt, or globally. An empty value means there's no idempotency key set (available from 4.3.3).",
        example: "run",
        allowedValues: ["global", "run", "attempt"],
      }),
    },
    region: {
      name: "region",
      clickhouseName: "region",
      ...column("String", {
        description: "Region",
        example: "us-east-1",
      }),
      // No whereTransform: the expression drives WHERE too, so pre-region rows still match.
      expression:
        "multiIf(region != '', region, startsWith(worker_queue, 'cm'), NULL, worker_queue)",
    },

    // Timing
    triggered_at: {
      name: "triggered_at",
      clickhouseName: "created_at",
      ...column("DateTime64", {
        description: "When the run was triggered.",
        example: "2024-01-15 09:30:00.000",
        coreColumn: true,
      }),
    },
    queued_at: {
      name: "queued_at",
      ...column("Nullable(DateTime64)", {
        description:
          "When the run was added to the queue. This is normally the same time as the triggered_at time, unless a delay is passed in or it's a scheduled run.",
        example: "2024-01-15 09:30:01.000",
      }),
    },
    dequeued_at: {
      name: "dequeued_at",
      clickhouseName: "started_at",
      ...column("Nullable(DateTime64)", {
        description:
          "When the run was dequeued for execution. This happens when there is available concurrency to execute your run.",
        example: "2024-01-15 09:30:01.000",
      }),
    },
    executed_at: {
      name: "executed_at",
      ...column("Nullable(DateTime64)", {
        description: "When execution of the run began.",
        example: "2024-01-15 09:30:01.500",
      }),
    },
    completed_at: {
      name: "completed_at",
      ...column("Nullable(DateTime64)", {
        description: "When the run completed",
        example: "2024-01-15 09:30:05.000",
      }),
    },
    delay_until: {
      name: "delay_until",
      ...column("Nullable(DateTime64)", {
        description: "Delayed execution until this time",
        example: "2024-01-15 10:00:00.000",
      }),
    },
    has_delay: {
      name: "has_delay",
      ...column("UInt8", { description: "Whether the run had a delay passed in", example: "1" }),
      expression: "if(isNotNull(delay_until), true, false)",
    },
    expired_at: {
      name: "expired_at",
      ...column("Nullable(DateTime64)", {
        description:
          'If there was a TTL on the run, this is when the run "expired". By default dev runs have a TTL of 10 minutes.',
        example: "2024-01-15 09:35:00.000",
      }),
    },
    ttl: {
      name: "ttl",
      clickhouseName: "expiration_ttl",
      ...column("String", {
        description: "The TTL string for expiration – by default dev runs have a TTL of '10m'.",
        example: "10m",
      }),
    },

    // Useful time periods
    execution_duration: {
      name: "execution_duration",
      ...column("Nullable(Int64)", {
        description:
          "The time between starting to execute and completing. This includes any time spent waiting (it is not compute time, use `usage_duration` for that).",
        customRenderType: "duration",
        example: "4000",
      }),
      expression: "dateDiff('millisecond', executed_at, completed_at)",
    },
    total_duration: {
      name: "total_duration",
      ...column("Nullable(Int64)", {
        description:
          "The time between being triggered and completing (if it has). This includes any time spent waiting (it is not compute time, use `usage_duration` for that).",
        customRenderType: "duration",
        example: "4000",
      }),
      expression: "dateDiff('millisecond', created_at, completed_at)",
    },
    queued_duration: {
      name: "queued_duration",
      ...column("Nullable(Int64)", {
        description:
          "The time between being queued and dequeued. Remember you need enough available concurrency for runs to be dequeued and start executing.",
        customRenderType: "duration",
        example: "4000",
      }),
      expression: "dateDiff('millisecond', queued_at, started_at)",
    },

    // Cost & usage
    usage_duration: {
      name: "usage_duration",
      clickhouseName: "usage_duration_ms",
      ...column("UInt32", {
        description: "Compute usage duration in milliseconds.",
        customRenderType: "duration",
        example: "3500",
      }),
    },
    compute_cost: {
      name: "compute_cost",
      ...column("Float64", {
        description: "Compute cost in dollars",
        customRenderType: "costInDollars",
        example: "0.000676",
      }),
      expression: "cost_in_cents / 100.0",
    },
    invocation_cost: {
      name: "invocation_cost",
      ...column("Float64", {
        description: "Invocation cost in dollars – the cost to start a run.",
        customRenderType: "costInDollars",
        example: "0.000025",
      }),
      expression: "base_cost_in_cents / 100.0",
    },
    total_cost: {
      name: "total_cost",
      ...column("Float64", {
        description: "Total cost in dollars (compute_cost + invocation_cost)",
        customRenderType: "costInDollars",
        example: "0.000701",
      }),
      expression: "(cost_in_cents + base_cost_in_cents) / 100.0",
    },

    // Output & error (JSON columns)
    // For JSON columns, NULL checks are transformed to check for empty object '{}'
    // So `error IS NULL` becomes `error = '{}'` and `error IS NOT NULL` becomes `error != '{}'`
    // textColumn uses the pre-materialized text columns for better performance
    // dataPrefix handles the internal {"data": ...} wrapper transparently
    output: {
      name: "output",
      ...column("JSON", {
        description: "The data you returned from the task.",
        example: '{"result": "success"}',
      }),
      nullValue: "'{}'", // Transform NULL checks to compare against empty object
      textColumn: "output_text", // Use output_text for full JSON value queries
      dataPrefix: "data", // Internal data is wrapped in {"data": ...}
    },
    error: {
      name: "error",
      ...column("JSON", {
        description:
          "If a run completely failed (after all attempts) then this error will be populated.",
        example: '{"message": "Task failed"}',
      }),
      nullValue: "'{}'", // Transform NULL checks to compare against empty object
      textColumn: "error_text", // Use error_text for full JSON value queries
      dataPrefix: "data", // Internal data is wrapped in {"data": ...}
    },

    // Tags & versions
    tags: {
      name: "tags",
      ...column("Array(String)", {
        description: "Tags you have added to the run.",
        customRenderType: "tags",
        example: '["user:123", "priority:high"]',
      }),
    },
    task_version: {
      name: "task_version",
      ...column("String", {
        description: "The version of your code in reverse date format.",
        example: "20240115.1",
      }),
    },
    sdk_version: {
      name: "sdk_version",
      ...column("String", {
        description: "The SDK package version for this run.",
        example: "3.3.0",
      }),
    },
    cli_version: {
      name: "cli_version",
      ...column("String", {
        description: "The CLI package version for this run.",
        example: "3.3.0",
      }),
    },
    machine: {
      name: "machine",
      clickhouseName: "machine_preset",
      ...column("LowCardinality(String)", {
        description: "The machine that the run executed on.",
        allowedValues: [...MACHINE_PRESETS],
        customRenderType: "machine",
        example: "small-1x",
      }),
    },
    is_test: {
      name: "is_test",
      ...column("UInt8", { description: "Whether this is a test run (0 or 1)", example: "0" }),
      expression: "if(is_test > 0, true, false)",
    },
    is_warm_start: {
      name: "is_warm_start",
      ...column("Nullable(UInt8)", {
        description: "Whether this run used a warm start vs a cold start.",
        example: "1",
      }),
    },
    concurrency_key: {
      name: "concurrency_key",
      ...column("String", {
        description: "The concurrency key you passed in when triggering the run.",
        example: "user:1234567",
      }),
    },
    max_duration: {
      name: "max_duration",
      clickhouseName: "max_duration_in_seconds",
      ...column("Nullable(UInt32)", {
        description:
          "The maximum allowed compute duration for this run in seconds. If the run exceeds this duration, the run will fail with an error. Can be set on an individual task, in the trigger.config, or per-run when triggering.",
        example: "300",
        customRenderType: "durationSeconds",
      }),
    },
    bulk_action_group_ids: {
      name: "bulk_action_group_ids",
      ...column("Array(String)", {
        description: "Any bulk actions that operated on this run.",
        example: '["bulk_12345678", "bulk_34567890"]',
        whereTransform: (value: string) => {
          logger.log(`WHERE TRANSFORM: ${value}`);
          return value.replace(/^bulk_/, "");
        },
      }),
    },
  },
};

/**
 * Schema definition for the metrics table (trigger_dev.metrics_v1)
 */
export const metricsSchema: TableSchema = {
  name: "metrics",
  clickhouseName: "trigger_dev.metrics_v1",
  description: "Host and runtime metrics collected during task execution",
  timeConstraint: "bucket_start",
  tenantColumns: {
    organizationId: "organization_id",
    projectId: "project_id",
    environmentId: "environment_id",
  },
  columns: {
    environment: {
      name: "environment",
      clickhouseName: "environment_id",
      ...column("String", { description: "The environment slug", example: "prod" }),
      fieldMapping: "environment",
      customRenderType: "environment",
    },
    project: {
      name: "project",
      clickhouseName: "project_id",
      ...column("String", {
        description: "The project reference, they always start with `proj_`.",
        example: "proj_howcnaxbfxdmwmxazktx",
      }),
      fieldMapping: "project",
      customRenderType: "project",
    },
    metric_name: {
      name: "metric_name",
      ...column("LowCardinality(String)", {
        description: "The name of the metric (e.g. process.cpu.utilization, system.memory.usage)",
        example: "process.cpu.utilization",
        coreColumn: true,
      }),
    },
    metric_type: {
      name: "metric_type",
      ...column("LowCardinality(String)", {
        description: "The type of metric",
        allowedValues: ["gauge", "sum", "histogram"],
        example: "gauge",
      }),
    },
    machine_id: {
      name: "machine_id",
      clickhouseName: "metric_subject",
      ...column("String", {
        description: "The machine ID that produced this metric",
        example: "machine-abc123",
      }),
    },
    bucket_start: {
      name: "bucket_start",
      ...column("DateTime", {
        description: "The start of the 10-second aggregation bucket",
        example: "2024-01-15 09:30:00",
        coreColumn: true,
      }),
    },
    metric_value: {
      name: "metric_value",
      clickhouseName: "value",
      ...column("Float64", {
        description: "The metric value",
        example: "0.75",
        coreColumn: true,
      }),
    },

    // Attributes (JSON column for user-defined and system attributes)
    attributes: {
      name: "attributes",
      ...column("JSON", {
        description: "JSON attributes attached to the metric data point.",
        example: '{"region": "us-east-1"}',
      }),
    },

    // Trigger context columns (from attributes.trigger.* JSON subpaths)
    run_id: {
      name: "run_id",
      ...column("String", {
        description: "The run ID associated with this metric",
        customRenderType: "runId",
        example: "run_cm1a2b3c4d5e6f7g8h9i",
        coreColumn: true,
      }),
      expression: "attributes.trigger.run_id",
    },
    task_identifier: {
      name: "task_identifier",
      ...column("String", {
        description: "Task identifier/slug",
        example: "my-background-task",
        coreColumn: true,
      }),
      expression: "attributes.trigger.task_slug",
    },
    attempt_number: {
      name: "attempt_number",
      ...column("UInt64", {
        description: "The attempt number for this metric",
        example: "1",
      }),
      expression: "attributes.trigger.attempt_number",
    },
    machine_name: {
      name: "machine_name",
      ...column("String", {
        description: "The machine preset used for execution",
        allowedValues: [...MACHINE_PRESETS],
        example: "small-1x",
      }),
      expression: "attributes.trigger.machine_name",
    },
    environment_type: {
      name: "environment_type",
      ...column("String", {
        description: "Environment type",
        allowedValues: [...ENVIRONMENT_TYPES],
        customRenderType: "environmentType",
        example: "PRODUCTION",
      }),
      expression: "attributes.trigger.environment_type",
    },
    worker_id: {
      name: "worker_id",
      ...column("String", {
        description: "The worker ID that produced this metric",
        customRenderType: "deploymentId",
        example: "deployment_cm1a2b3c4d5e",
      }),
      expression: "attributes.trigger.worker_id",
    },
    worker_version: {
      name: "worker_version",
      ...column("String", {
        description: "The worker version that produced this metric",
        example: "20240115.1",
      }),
      expression: "attributes.trigger.worker_version",
    },
  },
  timeBucketThresholds: [
    // Metrics are pre-aggregated into 10-second buckets, so 10s is the most granular interval.
    // All thresholds are shifted coarser compared to the runs table defaults.
    { maxRangeSeconds: 3 * 60 * 60, interval: { value: 10, unit: "SECOND" } },
    { maxRangeSeconds: 12 * 60 * 60, interval: { value: 1, unit: "MINUTE" } },
    { maxRangeSeconds: 2 * 24 * 60 * 60, interval: { value: 5, unit: "MINUTE" } },
    { maxRangeSeconds: 7 * 24 * 60 * 60, interval: { value: 15, unit: "MINUTE" } },
    { maxRangeSeconds: 30 * 24 * 60 * 60, interval: { value: 1, unit: "HOUR" } },
    { maxRangeSeconds: 90 * 24 * 60 * 60, interval: { value: 6, unit: "HOUR" } },
    { maxRangeSeconds: 180 * 24 * 60 * 60, interval: { value: 1, unit: "DAY" } },
    { maxRangeSeconds: 365 * 24 * 60 * 60, interval: { value: 1, unit: "WEEK" } },
  ] satisfies BucketThreshold[],
};

/**
 * All available schemas for the query editor
 */
/**
 * Schema definition for the llm_metrics table (trigger_dev.llm_metrics_v1)
 */
export const llmMetricsSchema: TableSchema = {
  name: "llm_metrics",
  clickhouseName: "trigger_dev.llm_metrics_v1",
  description: "LLM metrics: token usage, cost, performance, and behavior from GenAI spans",
  timeConstraint: "start_time",
  tenantColumns: {
    organizationId: "organization_id",
    projectId: "project_id",
    environmentId: "environment_id",
  },
  columns: {
    environment: {
      name: "environment",
      clickhouseName: "environment_id",
      ...column("String", { description: "The environment slug", example: "prod" }),
      fieldMapping: "environment",
      customRenderType: "environment",
    },
    project: {
      name: "project",
      clickhouseName: "project_id",
      ...column("String", {
        description: "The project reference, they always start with `proj_`.",
        example: "proj_howcnaxbfxdmwmxazktx",
      }),
      fieldMapping: "project",
      customRenderType: "project",
    },
    run_id: {
      name: "run_id",
      ...column("String", {
        description: "The run ID",
        customRenderType: "runId",
        coreColumn: true,
      }),
    },
    trace_id: {
      name: "trace_id",
      ...column("String", {
        description: "The trace ID",
      }),
    },
    span_id: {
      name: "span_id",
      ...column("String", {
        description: "The span ID",
      }),
    },
    task_identifier: {
      name: "task_identifier",
      ...column("LowCardinality(String)", {
        description: "The task identifier",
        example: "my-task",
        coreColumn: true,
      }),
    },
    gen_ai_system: {
      name: "gen_ai_system",
      ...column("LowCardinality(String)", {
        description: "AI provider (e.g. openai, anthropic)",
        example: "openai",
        coreColumn: true,
      }),
    },
    request_model: {
      name: "request_model",
      ...column("String", {
        description: "The model name requested",
        example: "gpt-4o",
      }),
    },
    response_model: {
      name: "response_model",
      ...column("String", {
        description: "The model name returned by the provider",
        example: "gpt-4o-2024-08-06",
        coreColumn: true,
      }),
    },
    operation_id: {
      name: "operation_id",
      ...column("LowCardinality(String)", {
        description: "Operation type (e.g. ai.streamText.doStream, ai.generateText.doGenerate)",
        example: "ai.streamText.doStream",
      }),
    },
    finish_reason: {
      name: "finish_reason",
      ...column("LowCardinality(String)", {
        description: "Why the LLM stopped generating (e.g. stop, tool-calls, length)",
        example: "stop",
        coreColumn: true,
      }),
    },
    cost_source: {
      name: "cost_source",
      ...column("LowCardinality(String)", {
        description: "Where cost data came from (registry, gateway, openrouter)",
        example: "registry",
      }),
    },
    input_tokens: {
      name: "input_tokens",
      ...column("UInt64", {
        description: "Number of input tokens",
        example: "702",
      }),
    },
    output_tokens: {
      name: "output_tokens",
      ...column("UInt64", {
        description: "Number of output tokens",
        example: "22",
      }),
    },
    total_tokens: {
      name: "total_tokens",
      ...column("UInt64", {
        description: "Total token count",
        example: "724",
      }),
    },
    cached_read_tokens: {
      name: "cached_read_tokens",
      ...column("UInt64", {
        description:
          "Input tokens served from the provider's prompt cache (cheaper than regular input tokens). Supported by Anthropic and OpenAI.",
        example: "8200",
      }),
      expression: "usage_details['input_cached_tokens']",
    },
    cache_creation_tokens: {
      name: "cache_creation_tokens",
      ...column("UInt64", {
        description:
          "Input tokens written to create a new prompt cache entry. Supported by Anthropic.",
        example: "1751",
      }),
      expression: "usage_details['cache_creation_input_tokens']",
    },
    reasoning_tokens: {
      name: "reasoning_tokens",
      ...column("UInt64", {
        description:
          "Tokens used for chain-of-thought reasoning (e.g. OpenAI o-series, DeepSeek R1). These count toward output but are not visible in the response.",
        example: "512",
      }),
      expression: "usage_details['reasoning_tokens']",
    },
    input_cost: {
      name: "input_cost",
      ...column("Decimal64(12)", {
        description: "Input cost in USD (from pricing registry)",
        customRenderType: "costInDollars",
      }),
    },
    output_cost: {
      name: "output_cost",
      ...column("Decimal64(12)", {
        description: "Output cost in USD (from pricing registry)",
        customRenderType: "costInDollars",
      }),
    },
    total_cost: {
      name: "total_cost",
      ...column("Decimal64(12)", {
        description: "Total cost in USD",
        customRenderType: "costInDollars",
        coreColumn: true,
      }),
    },
    cached_read_cost: {
      name: "cached_read_cost",
      ...column("Decimal64(12)", {
        description:
          "Cost of cached input tokens (discounted vs regular input). Only present when the pricing tier has a separate cached input price.",
        customRenderType: "costInDollars",
      }),
      expression: "cost_details['input_cached_tokens']",
    },
    cache_creation_cost: {
      name: "cache_creation_cost",
      ...column("Decimal64(12)", {
        description: "Cost of tokens written to create a prompt cache entry.",
        customRenderType: "costInDollars",
      }),
      expression: "cost_details['cache_creation_input_tokens']",
    },
    provider_cost: {
      name: "provider_cost",
      ...column("Decimal64(12)", {
        description: "Provider-reported cost in USD (from gateway or openrouter)",
        customRenderType: "costInDollars",
      }),
    },
    ms_to_first_chunk: {
      name: "ms_to_first_chunk",
      ...column("Float64", {
        description: "Time to first chunk in milliseconds (TTFC)",
        example: "245.3",
        coreColumn: true,
      }),
    },
    tokens_per_second: {
      name: "tokens_per_second",
      ...column("Float64", {
        description: "Average output tokens per second",
        example: "72.5",
      }),
    },
    pricing_tier_name: {
      name: "pricing_tier_name",
      ...column("LowCardinality(String)", {
        description: "The matched pricing tier name",
        example: "Standard",
      }),
    },
    start_time: {
      name: "start_time",
      ...column("DateTime64(9)", {
        description: "When the LLM call started",
        coreColumn: true,
      }),
    },
    duration: {
      name: "duration",
      ...column("UInt64", {
        description: "Span duration in nanoseconds",
        customRenderType: "durationNs",
      }),
    },
    prompt_slug: {
      name: "prompt_slug",
      ...column("LowCardinality(String)", {
        description: "The managed prompt slug used for this LLM call",
        example: "customer-support",
        coreColumn: true,
      }),
    },
    prompt_version: {
      name: "prompt_version",
      ...column("UInt32", {
        description: "The managed prompt version number used for this LLM call",
        example: "3",
      }),
    },
    metadata: {
      name: "metadata",
      ...column("Map(LowCardinality(String), String)", {
        description:
          "Key-value metadata from run tags (key:value format) and AI SDK telemetry metadata. Access keys with dot notation (metadata.userId) or bracket syntax (metadata['userId']).",
        example: "{'userId':'user_123','org':'acme'}",
      }),
    },
  },
};

/**
 * Schema definition for the llm_models table (trigger_dev.llm_model_aggregates_v1)
 * Global table — no tenant columns. Contains anonymized cross-tenant model performance data.
 */
export const llmModelsSchema: TableSchema = {
  name: "llm_models",
  clickhouseName: "trigger_dev.llm_model_aggregates_v1",
  description:
    "Cross-tenant model performance aggregates: calls, cost, latency, and throughput per model per minute. No tenant-specific data.",
  timeConstraint: "minute",
  // No tenantColumns — this is a global table with anonymized data
  columns: {
    response_model: {
      name: "response_model",
      ...column("String", {
        description: "The model name as returned by the provider",
        example: "gpt-4o-2024-08-06",
        coreColumn: true,
      }),
    },
    base_response_model: {
      name: "base_response_model",
      ...column("String", {
        description: "The base model name with dated variants grouped",
        example: "gpt-4o",
        coreColumn: true,
      }),
    },
    gen_ai_system: {
      name: "gen_ai_system",
      ...column("String", {
        description: "The AI provider system identifier",
        example: "openai.responses",
        coreColumn: true,
      }),
    },
    minute: {
      name: "minute",
      ...column("DateTime", {
        description: "Aggregation time bucket (per minute)",
        coreColumn: true,
      }),
    },
    call_count: {
      name: "call_count",
      ...column("UInt64", {
        description: "Number of LLM calls in this time bucket",
        coreColumn: true,
      }),
    },
    total_input_tokens: {
      name: "total_input_tokens",
      ...column("UInt64", {
        description: "Total input tokens consumed",
      }),
    },
    total_output_tokens: {
      name: "total_output_tokens",
      ...column("UInt64", {
        description: "Total output tokens generated",
      }),
    },
    total_cost: {
      name: "total_cost",
      ...column("Float64", {
        description: "Total cost in USD",
        customRenderType: "costInDollars",
        coreColumn: true,
      }),
    },
    // Aggregate state columns — use quantilesMerge() in queries to extract values
    // Example: quantilesMerge(0.5)(ttfc_quantiles)[1] AS ttfc_p50
    ttfc_quantiles: {
      name: "ttfc_quantiles",
      ...column("String", {
        description:
          "Time to first chunk quantile state. Use quantilesMerge(0.5)(ttfc_quantiles)[1] AS ttfc_p50 in queries.",
        example: "quantilesMerge(0.5)(ttfc_quantiles)[1]",
      }),
    },
    tps_quantiles: {
      name: "tps_quantiles",
      ...column("String", {
        description:
          "Tokens per second quantile state. Use quantilesMerge(0.5)(tps_quantiles)[1] AS tps_p50 in queries.",
        example: "quantilesMerge(0.5)(tps_quantiles)[1]",
      }),
    },
    duration_quantiles: {
      name: "duration_quantiles",
      ...column("String", {
        description:
          "Duration quantile state. Use quantilesMerge(0.5)(duration_quantiles)[1] AS duration_p50 in queries.",
        example: "quantilesMerge(0.5)(duration_quantiles)[1]",
      }),
    },
  },
};

export const querySchemas: TableSchema[] = [
  runsSchema,
  metricsSchema,
  llmMetricsSchema,
  llmModelsSchema,
];

/**
 * Default query for the query editor
 */
export const defaultQuery = autoFormatSQL(`SELECT run_id, task_identifier, triggered_at, status
FROM runs
ORDER BY triggered_at DESC
LIMIT 100`);
