create periodic views with typescript and prisma

create_views.ts

import { format, zonedTimeToUtc } from "date-fns-tz";
import { Group, Period } from "./types";
import { prisma } from './db';

/*
 * This was copied from http://npm.im/strip-indent
 */
export const stripIndent = (str: string) => {
    const match = str.match(/^[ \t]*(?=\S)/gm)

    if (!match) {
        return str
    }

    const indent = Math.min(
        ...match.map(x => {
        return x.length
        }),
    )
    const re = new RegExp(`^[ \\t]{${indent}}`, 'gm')

    return indent > 0 ? str.replace(re, '') : str
}

const timezone = 'America/New_York';

const createView = async (name: string, query: string, materialized?: boolean) => {
  return await prisma.$executeRawUnsafe<any>(`
CREATE ${materialized ? `MATERIALIZED` : ''} VIEW "${name}" AS
${query}
${materialized ? 'WITH DATA;' : ';'}
`)
}

const removeView = async (name: string) => {
  let e1, e2;
  try {
    await prisma.$executeRawUnsafe(`DROP VIEW IF EXISTS "${name}";`);
  } catch (e) {
    e1 = e;
  }
  try {
    await prisma.$executeRawUnsafe(`DROP MATERIALIZED VIEW IF EXISTS "${name}";`);
  } catch (e) {
    e2 = e;
  }
  if (e1 && e2) {
    throw e1;
  }
}

const createViewManager = (name: string, query: string) => {
  return {
    create: async (materialized?: boolean) => {
      try {
        return await createView(name, query, materialized);
      } catch (e: any) {
        console.error(name);
        e.message += `\n\n// ${name}\n${query}`;
        throw e;
      }
    },
    remove: async () => {
      return await removeView(name);
    }
  }
}

interface QueryProps {
  where?: string | string[];
  groupBy?: string | string[];
  select?: string | string[];
}

const normalizeQueryProps = ({ where, groupBy, select }: QueryProps) => {
  if (Array.isArray(groupBy)) {
    groupBy = groupBy.join(', ');
  }
  if (Array.isArray(select)) {
    select = select.join(', ');
  }
  if (Array.isArray(where)) {
    where = where.join(' AND ');
  }
  return { where, groupBy, select };
}

const getRatingQuery = (props: QueryProps) => {
  const { where, groupBy, select } = normalizeQueryProps(props);
  return `
SELECT
  avg("rating") as rating,
  count(*) as count
  ${select ? `, ${select}` : ''}
FROM
  "Call"
WHERE
  "rating" IS NOT NULL 
  AND "rating" > 0
  AND "agentId" IS NOT NULL
${where ? `AND ${where}` : ''}
GROUP BY
  ${groupBy ? `${groupBy}` : ''}
`
}

const getCallTypeCountQuery = (props: QueryProps) => {
  const { where, groupBy, select } = normalizeQueryProps(props);
  return `
SELECT
  type as type,
  COUNT(*) as count
  ${select ? `, ${select}` : ''}
FROM
  "Call"
WHERE
direction = 'Inbound'
${where ? `AND ${where}` : ''}
GROUP BY
  type
  ${groupBy ? `, ${groupBy}` : ''}
`
}

const getTagCountQuery = (props: QueryProps) => {
  const { where, groupBy, select } = normalizeQueryProps(props);
  return `
SELECT
  t.name as tag,
  COUNT(*) as count
  ${select ? `, ${select}` : ''}
FROM
  "Call" as c
JOIN
  "_CallToTag" as ct ON ct."A" = c.id
JOIN
  "Tag" as t ON t.id = ct."B"
${where ? `AND ${where}` : ''}
GROUP BY
  tag
  ${groupBy ? `, ${groupBy}` : ''}
`;
}

const queryGetters = [
  {
    name: 'Rating',
    query: getRatingQuery,
    schema: `
      rating Float
      count  BigInt
    `,
  },
  {
    name: 'CallTypeCount',
    query: getCallTypeCountQuery,
    schema: `
      type  CallType?
      count BigInt
    `,
  },
  {
    name: 'TagCount',
    query: getTagCountQuery,
    schema: `
      tag   String @unique
      count BigInt
    `,
  },
]

const periods = Object.values(Period)
  .map(period => ({
    name: period,
    select: `DATE_TRUNC('${period}', "createdOn" AT TIME ZONE '${timezone}') as ${period.toLowerCase()}`,
    groupBy: period.toLowerCase(),
    schema: `${period.toLowerCase()} DateTime @unique`,
  }));

const modifiers = [
  {
    name: Group.Agent,
    select: '"agentId"',
    groupBy: '"agentId"',
    schema: '  agentId Int @unique',
  },
  {
    name: Group.Company,
  },
]

const combineProps = (props: any[]) => {
  const combined: any = {};
  for (const prop of props) {
    for (const key in prop) {
      if (combined[key]) {
        combined[key] = [...combined[key], prop[key]];
      } else {
        combined[key] = [prop[key]];
      }
    }
  }
  return combined;
}

const queries: { name: string, query: string }[] = []
export const schemas: string[] = []

for (const getter of queryGetters) {
  for (const modifier of modifiers) {
    for (const period of periods) {
      const name = `${getter.name}${period.name}${modifier.name}`;
      queries.push({
        name,
        query: getter.query(combineProps([modifier, period])),
      })
      const schema = [getter.schema, modifier.schema, period.schema]
        .filter(Boolean)
        .map(s => s!.trim())
        .map(s => s.replace(/^\s*/gm, '          '))
        .join('\n');

      schemas.push(stripIndent(`
        view ${name} {
          ${schema.trim()}
        }`));
    }
  }
}
export const views = queries.map(({ name }) => name);

const viewManagers = queries.map(({ name, query }) => createViewManager(name, query));

export async function runQuery(query: string) {
  const results = await prisma.$queryRawUnsafe<any>(query);
  for (const result of results) {
    console.log('result', result);
  }
}

export const removeViews = async () => {
  for (const manager of viewManagers) {
    await manager.remove();
  }
};

export const createViews = async (materialized?: boolean) => {
  for (const manager of viewManagers) {
    await manager.create(materialized);
  }
};

export const refreshViews = async () => {
  for (const view of views) {
    await prisma.$executeRawUnsafe(`REFRESH MATERIALIZED VIEW "${view}"`);
  }
};

export const printSchemas = (schemas: string[]) => {
  for (const schema of schemas) {
    console.log(schema);
  }
}
 export const printQueries = (queries: { name: string, query: string }[]) => {
  for (const { name, query } of queries) {
    console.log(`\n\n// ${name}\n\n${query}`);
  }
}


export function getStartOfDayEST() {
  const easternTime = 'America/New_York';
  const now = new Date();
  const startOfDayInEasternTime = format(now, 'yyyy-MM-dd\'T00:00:00.000\'', { timeZone: easternTime });
  return zonedTimeToUtc(startOfDayInEasternTime, easternTime);
}