import { MaterializedView, OlapTable, ClickHouseEngines, sql } from "@514labs/moose-lib";
import { sourceTable } from "path/to/SourceTable"; // or a view
 
// Define the schema of the transformed rows-- this is static and it must match the results of your SELECT. It also represents the schema of your entire destination table.
interface TargetSchema {
  id: string;
  average_rating: number;
  num_reviews: number;
}
export const mv = new MaterializedView<TargetSchema>({
  // The transformation to run on the source table
  selectStatement: sql`
  SELECT
    ${sourceTable.columns.id},
    avg(${sourceTable.columns.rating}) AS average_rating, 
    count(*) AS num_reviews 
    FROM ${sourceTable}
    GROUP BY ${sourceTable.columns.id}
  `;,
  // Reference to the source table(s) that the SELECT reads from
  selectTables: [sourceTable], 
 
  // Creates a new OlapTable named "target_table" where the transformed rows are written to.
  targetTable: {
    name: "target_table",
    orderByFields: ["id"],
  },
  // The name of the materialized view in ClickHouse
  materializedViewName: "mv_to_target_table", 
});

import type { Sql } from "@514labs/moose-lib";
 
interface MaterializedViewConfig<T> {
  // Static SELECT that computes the destination rows
  selectStatement: string | Sql;
 
  // Tables/views the query reads from
  selectTables: (OlapTable<any> | View)[];
 
  // Name of the ClickHouse MATERIALIZED VIEW object
  materializedViewName: string;
 
  // Destination table where materialized rows are stored
  targetTable?:
    | OlapTable<T>
    | {
        name: string;
        engine?: ClickHouseEngines;
        orderByFields?: (keyof T & string)[];
      };
 
  /** @deprecated prefer targetTable */
  tableName?: string;
  /** @deprecated prefer targetTable */
  engine?: ClickHouseEngines;
  /** @deprecated prefer targetTable */
  orderByFields?: (keyof T & string)[];
}

import { MaterializedView, sql } from "@514labs/moose-lib";
 
interface Dest { id: string; value: number; created_at: string }
 
new MaterializedView<Dest>({
  selectStatement: sql`
    SELECT id, toInt32(value) AS value, created_at
    FROM ${sourceTable}
    WHERE active = 1
  `,
  selectTables: [sourceTable],
  targetTable: { name: "proj_table" },
  materializedViewName: "mv_to_proj_table",
});

interface DailyCounts { day: string; user_id: string; events: number }
 
// Create the destination table explicitly
const daily = new OlapTable<DailyCounts>("daily_counts", {
  engine: ClickHouseEngines.SummingMergeTree,
  orderByFields: ["day", "user_id"],
});
 
 
// MV 1 - write to the daily_counts table
const webStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${webEvents}`;
const mv1 = new MaterializedView<DailyCounts>({
  selectStatement: webStmt,
  selectTables: [webEvents],
  targetTable: daily,
  materializedViewName: "mv_web_to_daily_counts",
});
 
// MV 2 - write to the daily_counts table
const mobileStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${mobileEvents}`;
const mv2 = new MaterializedView<DailyCounts>({
  selectStatement: mobileStmt,
  selectTables: [mobileEvents],
  targetTable: daily,
  materializedViewName: "mv_mobile_to_daily_counts",
});

import { MaterializedView, sql } from "@514labs/moose-lib";
 
interface Dest { id: string; name: string; day: string }
 
const transformation = sql`
  SELECT
    ${users.columns.id}        AS id,
    ${users.columns.name}      AS name,
    toDate(${events.columns.ts}) AS day
  FROM ${events}
  JOIN ${users} ON ${events.columns.user_id} = ${users.columns.id}
  WHERE ${events.columns.active} = 1
`;
 
new MaterializedView<Dest>({
  selectStatement: transformation,
  selectTables: [events, users],
  targetTable: { name: "user_activity_by_day" },
  materializedViewName: "mv_user_activity_by_day",
});

import { MaterializedView, ClickHouseEngines, Aggregated, sql } from "@514labs/moose-lib";
 
 
interface MetricsById {
  id: string;
  /**
   * Result of avgState(events.rating)
   *   - avgState(number) returns number, so model the type as number
   *   - Aggregated arg type is [number] because the column (events.rating) is a number
   *   - Aggregated function name is "avg"
   */
  avg_rating: number & Aggregated<"avg", [number]>;
  /** 
  * Result of uniqExactState(events.user_id)
  *  - uniqExact returns an integer; use number & ClickHouseInt<"uint64"> for precision
  *  - Aggregated arg type is [string] because the column (events.user_id) is a string
  *  - Aggregated function name is "uniqExact"
  */
  daily_uniques: number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]>;
}
 
//  All Aggregate Functions in this query have a [functionName][State]() suffix.
const stmt = sql`
  SELECT
    ${events.columns.id} AS id,
    avgState(${events.columns.rating})      AS avg_rating,
    uniqExactState(${events.columns.user_id}) AS daily_uniques
  FROM ${events}
  GROUP BY ${events.columns.id}
`;
 
 
new MaterializedView<MetricsById>({
  selectStatement: stmt,
  selectTables: [events],
  targetTable: {
    name: "metrics_by_id",
    engine: ClickHouseEngines.AggregatingMergeTree,
    orderByFields: ["id"],
  },
  materializedViewName: "mv_metrics_by_id",
});

number & Aggregated<"avg", [number]> // avgState(col: number)
number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]> // uniqExactState(col: string)
number & ClickHouseInt<"uint64"> & Aggregated<"count", []> // countState(col: any)
 
string & Aggregated<"argMax", [string, Date]> // argMaxState(col: string, value: Date)
string & Aggregated<"argMin", [string, Date]> // argMinState(col: string, value: Date)
 
number & Aggregated<"corr", [number, number]> // corrState(col1: number, col2: number)
 

import { MaterializedView, ClickHouseEngines, Aggregated, sql } from "@514labs/moose-lib";
 
interface MetricsById {
  id: string;
  avg_rating: number & Aggregated<"avg", [number]>;
  total_reviews: number & Aggregated<"sum", [number]>;
}
 
const aggStmt = sql`
  SELECT
    ${reviews.columns.id} AS id,
    avgState(${reviews.columns.rating}) AS avg_rating,
    countState(${reviews.columns.id})   AS total_reviews
  FROM ${reviews}
  GROUP BY ${reviews.columns.id}
`;
 
const mv = new MaterializedView<MetricsById>({
  selectStatement: aggStmt,
  selectTables: [reviews],
  targetTable: {
    name: "metrics_by_id",
    engine: ClickHouseEngines.AggregatingMergeTree,
    orderByFields: ["id"],
  },
  materializedViewName: "mv_metrics_by_id",
});

import { sql } from "@514labs/moose-lib";
 
// Auto-finalized via Aggregated + sql
const cols = mv.targetTable.columns; // mv from earlier Agg example
const autoFinalized = sql`
  SELECT ${cols.avg_rating}, ${cols.total_reviews}
  FROM ${mv.targetTable}
  WHERE ${cols.id} = '123'
`;
 
// Manual finalization (explicit ...Merge)
const manual = sql`
  SELECT
    avgMerge(avg_rating)   AS avg_rating,
    countMerge(total_reviews) AS total_reviews
  FROM metrics_by_id
  WHERE id = '123'
`;