Warehouse and Project Utilities

/**
 * Generates SQL for fixed-width bin custom dimensions
 * Creates bins of equal width (e.g., 0-10, 10-20, 20-30)
 * @param binWidth - Width of each bin
 * @param baseDimensionSql - SQL expression for the base dimension
 * @param warehouseSqlBuilder - Warehouse-specific SQL builder
 * @returns SQL string that generates bin labels like "0 - 9", "10 - 19"
 */
function getFixedWidthBinSelectSql(args: {
  binWidth: number;
  baseDimensionSql: string;
  warehouseSqlBuilder: WarehouseSqlBuilder;
}): string;

/**
 * Generates SQL for custom range bin dimensions
 * Creates bins with custom ranges (e.g., <100, 100-500, 500-1000, ≥1000)
 * @param binRanges - Array of {from, to} range objects
 * @param baseDimensionSql - SQL expression for the base dimension
 * @param warehouseSqlBuilder - Warehouse-specific SQL builder
 * @returns SQL CASE statement that assigns values to range labels
 */
function getCustomRangeSelectSql(args: {
  binRanges: BinRange[];
  baseDimensionSql: string;
  warehouseSqlBuilder: WarehouseSqlBuilder;
}): string;

import {
  getFixedWidthBinSelectSql,
  getCustomRangeSelectSql,
} from '@lightdash/common';

// Fixed width bins (age groups: 0-9, 10-19, 20-29, etc.)
const ageGroupSql = getFixedWidthBinSelectSql({
  binWidth: 10,
  baseDimensionSql: 'customers.age',
  warehouseSqlBuilder,
});
// Generates: CONCAT(FLOOR(customers.age / 10) * 10, ' - ', (FLOOR(customers.age / 10) + 1) * 10 - 1)

// Custom ranges (revenue tiers)
const revenueTierSql = getCustomRangeSelectSql({
  binRanges: [
    { to: 1000 },                    // <1000
    { from: 1000, to: 5000 },        // 1000-5000
    { from: 5000, to: 10000 },       // 5000-10000
    { from: 10000 },                 // ≥10000
  ],
  baseDimensionSql: 'orders.revenue',
  warehouseSqlBuilder,
});

interface DependencyNode {
  name: string;
  dependencies: string[];
}

function detectCircularDependencies(
  dependencies: DependencyNode[],
  errorPrefix?: string
): void;

import { detectCircularDependencies } from '@lightdash/common';

const dependencies = [
  { name: 'A', dependencies: ['B'] },
  { name: 'B', dependencies: ['C'] },
  { name: 'C', dependencies: ['A'] }
];

// Throws error: "Circular dependency detected in table calculations: A -> B -> C -> A"
detectCircularDependencies(dependencies, 'table calculations');

function loadLightdashProjectConfig(
  yamlFileContents: string,
  onLoaded?: (config: LightdashProjectConfig) => Promise<void>
): Promise<LightdashProjectConfig>;

import { loadLightdashProjectConfig } from '@lightdash/common';

const yamlContent = await fs.readFile('lightdash.config.yml', 'utf8');
const config = await loadLightdashProjectConfig(yamlContent);

/**
 * @deprecated Use warehouse client directly for field quoting
 */
function getFieldQuoteChar(warehouseType: WarehouseTypes | undefined): string;

/**
 * Generates SQL for aggregated fields across different warehouse types
 * Supports BigQuery, Snowflake, Postgres, Redshift, Databricks, Trino, and ClickHouse
 */
function getAggregatedField(
  warehouseSqlBuilder: WarehouseSqlBuilder,
  aggregation: VizAggregationOptions,
  reference: string
): string;

import { getAggregatedField } from '@lightdash/common';

// Generate warehouse-specific aggregation SQL
const avgSql = getAggregatedField(
  warehouseSqlBuilder,
  VizAggregationOptions.AVERAGE,
  'sales_amount'
);
// Result varies by warehouse: AVG(sales_amount), MEAN(sales_amount), etc.

function getProjectDirectory(dbtConnection?: DbtProjectConfig): string | undefined;

import { getProjectDirectory } from '@lightdash/common';

const projectDir = getProjectDirectory(project.dbtConnection);
// For DBT local: returns dbtConnection.project_dir
// For git integrations: returns dbtConnection.project_sub_path
// For DBT Cloud IDE or NONE: returns undefined

/**
 * Represents a node in a dependency graph
 */
interface DependencyNode {
  name: string;
  dependencies: string[];
}

/**
 * Detects circular dependencies in a dependency graph using depth-first search.
 * Throws an error if a circular dependency is found.
 * @param dependencies - Array of nodes with their dependencies
 * @param errorPrefix - Prefix for the error message (e.g., "table calculations", "metrics")
 * @throws Error if a circular dependency is detected, with the full cycle path
 */
function detectCircularDependencies(
  dependencies: DependencyNode[],
  errorPrefix?: string
): void;

import { detectCircularDependencies, type DependencyNode } from '@lightdash/common';

const dependencies: DependencyNode[] = [
  { name: 'A', dependencies: ['B'] },
  { name: 'B', dependencies: ['C'] },
  { name: 'C', dependencies: ['A'] } // Creates a cycle: A -> B -> C -> A
];

try {
  detectCircularDependencies(dependencies, 'table calculations');
} catch (error) {
  console.error(error);
  // Error: "Circular dependency detected in table calculations: A -> B -> C -> A"
}

/**
 * Creates a virtual view explore from a SQL query and column definitions.
 * Virtual views allow querying arbitrary SQL as if it were a regular explore.
 * @param virtualViewName - Name for the virtual view
 * @param sql - SQL query that defines the view
 * @param columns - Column definitions with types
 * @param warehouseClient - Warehouse client for SQL compilation
 * @param label - Optional display label for the view
 * @returns Explore object with type ExploreType.VIRTUAL
 */
function createVirtualView(
  virtualViewName: string,
  sql: string,
  columns: VizColumn[],
  warehouseClient: WarehouseClient,
  label?: string
): Explore;

/**
 * Creates a temporary virtual view using a fake warehouse client.
 * Useful for creating virtual views without requiring actual warehouse connection.
 * @param virtualViewName - Name for the virtual view
 * @param sql - SQL query that defines the view
 * @param columns - Column definitions with types
 * @returns Explore object with type ExploreType.VIRTUAL
 */
function createTemporaryVirtualView(
  virtualViewName: string,
  sql: string,
  columns: VizColumn[]
): Explore;

import { createVirtualView, createTemporaryVirtualView, type VizColumn } from '@lightdash/common';

const columns: VizColumn[] = [
  { reference: 'customer_id', type: DimensionType.STRING },
  { reference: 'total_orders', type: DimensionType.NUMBER },
  { reference: 'created_at', type: DimensionType.TIMESTAMP }
];

// With warehouse client
const virtualView = createVirtualView(
  'customer_summary',
  'SELECT customer_id, COUNT(*) as total_orders, MIN(created_at) as created_at FROM orders GROUP BY customer_id',
  columns,
  warehouseClient,
  'Customer Summary'
);

// Without warehouse client (temporary)
const tempView = createTemporaryVirtualView(
  'customer_summary',
  'SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id',
  columns
);

/**
 * Generates SQL for a fixed-width bin dimension.
 * Creates bins of equal width (e.g., 0-10, 10-20, 20-30).
 * @param binWidth - Width of each bin
 * @param baseDimensionSql - SQL expression for the dimension to bin
 * @param warehouseSqlBuilder - Warehouse SQL builder for generating SQL
 * @returns SQL CASE expression for binning
 */
function getFixedWidthBinSelectSql(args: {
  binWidth: number;
  baseDimensionSql: string;
  warehouseSqlBuilder: WarehouseSqlBuilder;
}): string;

/**
 * Generates SQL for a custom range bin dimension.
 * Creates bins with custom boundaries (e.g., <10, 10-50, 50-100, ≥100).
 * @param binRanges - Array of bin range definitions
 * @param baseDimensionSql - SQL expression for the dimension to bin
 * @param warehouseSqlBuilder - Warehouse SQL builder for generating SQL
 * @returns SQL CASE expression for custom range binning
 */
function getCustomRangeSelectSql(args: {
  binRanges: BinRange[];
  baseDimensionSql: string;
  warehouseSqlBuilder: WarehouseSqlBuilder;
}): string;

import {
  getFixedWidthBinSelectSql,
  getCustomRangeSelectSql,
  type BinRange
} from '@lightdash/common';

// Fixed width bins
const fixedWidthSql = getFixedWidthBinSelectSql({
  binWidth: 10,
  baseDimensionSql: 'age',
  warehouseSqlBuilder
});
// Generates: FLOOR(age / 10) * 10 || ' - ' || (FLOOR(age / 10) + 1) * 10 - 1

// Custom range bins
const ranges: BinRange[] = [
  { to: 10 },              // <10
  { from: 10, to: 50 },    // 10-50
  { from: 50, to: 100 },   // 50-100
  { from: 100 }            // ≥100
];

const customRangeSql = getCustomRangeSelectSql({
  binRanges: ranges,
  baseDimensionSql: 'revenue',
  warehouseSqlBuilder
});

/**
 * @deprecated use WarehouseSqlBuilder.getFieldQuoteChar instead
 */
function getFieldQuoteChar(
  warehouseType: WarehouseTypes | undefined
): string;

function getAggregatedField(
  warehouseSqlBuilder: WarehouseSqlBuilder,
  aggregation: VizAggregationOptions,
  reference: string
): string;

import {
  getAggregatedField,
  VizAggregationOptions,
  type WarehouseSqlBuilder,
} from '@lightdash/common';

// Generate aggregated field SQL
function buildAggregationSQL(
  builder: WarehouseSqlBuilder,
  field: string
) {
  const sumSql = getAggregatedField(
    builder,
    VizAggregationOptions.SUM,
    field
  );
  // Returns: SUM("field") for Postgres
  // Returns: SUM(`field`) for BigQuery

  const anySql = getAggregatedField(
    builder,
    VizAggregationOptions.ANY,
    field
  );
  // Returns: ANY_VALUE("field") for BigQuery/Snowflake
  // Returns: (ARRAY_AGG("field"))[1] for Postgres
  // Returns: any("field") for ClickHouse

  return { sumSql, anySql };
}