db.ts

import * as _ from "lodash-es"
import knex, { Knex } from "knex"
import {
    GRAPHER_DB_HOST,
    GRAPHER_DB_USER,
    GRAPHER_DB_PASS,
    GRAPHER_DB_NAME,
    GRAPHER_DB_PORT,
    BAKED_BASE_URL,
} from "../settings/serverSettings.js"
import { IS_ARCHIVE } from "../settings/clientSettings.js"
import { PROD_URL } from "../site/SiteConstants.js"
import { registerExitHandler } from "./cleanup.js"
import { createTagGraph, Url } from "@ourworldindata/utils"
import {
    ImageMetadata,
    MinimalDataInsightInterface,
    OwidGdocType,
    DBRawPostGdocWithTags,
    parsePostsGdocsWithTagsRow,
    DBEnrichedPostGdocWithTags,
    parsePostsGdocsRow,
    TagGraphRootName,
    FlatTagGraph,
    FlatTagGraphNode,
    MinimalTagWithIsTopic,
    DbPlainPostGdocLink,
    ContentGraphLinkType,
    OwidGdoc,
    DbPlainTag,
    TagGraphNode,
    MinimalExplorerInfo,
    DbEnrichedImage,
    DbEnrichedImageWithUserId,
    DbEnrichedImageWithPageviews,
    MinimalTag,
    BreadcrumbItem,
    PostsGdocsTableName,
    OwidGdocBaseInterface,
    TagGraphRoot,
    FeaturedMetricByParentTagNameDictionary,
    ChartConfigsTableName,
    FeaturedMetricsTableName,
    TagsTableName,
    TagGraphTableName,
    ExplorersTableName,
    MultiDimDataPagesTableName,
    OwidGdocMinimalPostInterface,
    DbRawPostGdoc,
} from "@ourworldindata/types"
import { gdocFromJSON } from "./model/Gdoc/GdocFactory.js"
import { getCanonicalUrl } from "@ourworldindata/components"
import { rawGdocToMinimalPost } from "./model/Gdoc/GdocBase.js"

/**
 * TEMPORARY: Transaction-scoped caching helper for baking performance
 *
 * Caches the result of an async function on the knex transaction object.
 * Cache is automatically cleared when the transaction ends, making it safe
 * for both long-running baking transactions and short web app requests.
 *
 * @param knex - The knex transaction object
 * @param cacheKey - Unique key for this cached function
 * @param fn - The async function to cache
 * @returns The cached or computed result
 *
 * @example
 * export async function getDods(knex: KnexReadonlyTransaction) {
 *   return cachedInTransaction(knex, 'dods', async () => {
 *     return knexRaw(knex, `SELECT * FROM dods`)
 *   })
 * }
 */
export async function cachedInTransaction<T>(
    knex: KnexReadonlyTransaction,
    cacheKey: string,
    fn: () => Promise<T>
): Promise<T> {
    const cache = ((knex as any).__queryCache =
        (knex as any).__queryCache || {})
    if (cache[cacheKey] !== undefined) {
        return cache[cacheKey]
    }
    const result = await fn()
    cache[cacheKey] = result
    return result
}

// Return the first match from a mysql query
export const closeTypeOrmAndKnexConnections = async (): Promise<void> => {
    if (_knexInstance) {
        await _knexInstance.destroy()
        _knexInstance = undefined
    }
}

let _knexInstance: Knex | undefined = undefined

export function setKnexInstance(knexInstance: Knex<any, any[]>): void {
    _knexInstance = knexInstance
}

const getNewKnexInstance = (): Knex<any, any[]> => {
    return knex({
        client: "mysql2",
        connection: {
            host: GRAPHER_DB_HOST,
            user: GRAPHER_DB_USER,
            password: GRAPHER_DB_PASS,
            database: GRAPHER_DB_NAME,
            port: GRAPHER_DB_PORT,
            charset: "utf8mb4",
            typeCast: (field: any, next: any) => {
                if (field.type === "TINY" && field.length === 1) {
                    return field.string() === "1" // 1 = true, 0 = false
                }

                return next()
            },

            // The mysql2 driver will return JSON objects by default, which is nice, but we have many code paths that
            // expect JSON strings, so we instead tell it to return JSON strings.
            //@ts-expect-error This is an option in mysql2 v3.10+, but it's not yet reflected in the knex types
            jsonStrings: true,
        },
    })
}

export const knexInstance = (): Knex<any, any[]> => {
    if (_knexInstance) return _knexInstance

    _knexInstance = getNewKnexInstance()

    registerExitHandler(async () => {
        if (_knexInstance) await _knexInstance.destroy()
    })

    return _knexInstance
}

declare const __read_capability: unique symbol
declare const __write_capability: unique symbol
export type KnexReadonlyTransaction = Knex.Transaction<any, any[]> & {
    readonly [__read_capability]: "read"
}

export type KnexReadWriteTransaction = Knex.Transaction<any, any[]> & {
    readonly [__read_capability]: "read"
    readonly [__write_capability]: "write"
}

export enum TransactionCloseMode {
    Close,
    KeepOpen,
}

async function knexTransaction<T, KT>(
    transactionFn: (trx: KT) => Promise<T>,
    closeConnection: TransactionCloseMode,
    readonly: boolean,
    knex: Knex<any, any[]>
): Promise<T> {
    try {
        const options = readonly ? { readOnly: true } : {}
        const result = await knex.transaction(
            async (trx) => transactionFn(trx as KT),
            options
        )
        return result
    } finally {
        if (closeConnection === TransactionCloseMode.Close) {
            await knex.destroy()
            if (knex === _knexInstance) _knexInstance = undefined
        }
    }
}

export async function knexReadonlyTransaction<T>(
    transactionFn: (trx: KnexReadonlyTransaction) => Promise<T>,
    closeConnection: TransactionCloseMode = TransactionCloseMode.KeepOpen,
    knex: Knex<any, any[]> = knexInstance()
): Promise<T> {
    return knexTransaction(transactionFn, closeConnection, true, knex)
}

export async function knexReadWriteTransaction<T>(
    transactionFn: (trx: KnexReadWriteTransaction) => Promise<T>,
    closeConnection: TransactionCloseMode = TransactionCloseMode.KeepOpen,
    knex: Knex<any, any[]> = knexInstance()
): Promise<T> {
    return knexTransaction(transactionFn, closeConnection, false, knex)
}
export const knexRaw = async <TRow = unknown>(
    knex: Knex<any, any[]>,
    str: string,
    params?: any[] | Record<string, any>
): Promise<TRow[]> => {
    try {
        const rawReturnConstruct = await knex.raw(str, params ?? [])
        return rawReturnConstruct[0]
    } catch (e) {
        console.error("Exception when executing SQL statement!", {
            sql: str,
            params,
            error: e,
        })
        throw e
    }
}

export const knexRawFirst = async <TRow = unknown>(
    knex: KnexReadonlyTransaction,
    str: string,
    params?: any[] | Record<string, any>
): Promise<TRow | undefined> => {
    const results = await knexRaw<TRow>(knex, str, params)
    if (results.length === 0) return undefined
    return results[0]
}

export const knexRawInsert = async (
    knex: KnexReadWriteTransaction,
    str: string,
    params?: any[]
): Promise<{ insertId: number }> => (await knex.raw(str, params ?? []))[0]

export const getExplorerTags = async (
    knex: KnexReadonlyTransaction
): Promise<{ slug: string; tags: Pick<DbPlainTag, "name" | "id">[] }[]> => {
    return knexRaw<{ slug: string; tags: string }>(
        knex,
        `-- sql
        SELECT
        ext.explorerSlug as slug,
        CASE
            WHEN COUNT(t.id) = 0 THEN JSON_ARRAY()
            ELSE JSON_ARRAYAGG(JSON_OBJECT('name', t.name, 'id', t.id))
        END AS tags
        FROM
            explorer_tags ext
        LEFT JOIN tags t ON
            ext.tagId = t.id
        GROUP BY
            ext.explorerSlug`
    ).then((rows) =>
        rows.map((row) => ({
            slug: row.slug,
            tags: JSON.parse(row.tags) as Pick<DbPlainTag, "name" | "id">[],
        }))
    )
}

export const getPublishedExplorersBySlug = async (
    knex: KnexReadonlyTransaction,
    includeUnlisted: boolean = true
): Promise<Record<string, MinimalExplorerInfo>> => {
    return cachedInTransaction(
        knex,
        includeUnlisted
            ? "publishedExplorersBySlug"
            : "unlistedPublishedExplorersBySlug",
        async () => {
            const tags = await getExplorerTags(knex)
            const tagsBySlug = _.keyBy(tags, "slug")
            return knexRaw(
                knex,
                `-- sql
            SELECT
                e.slug,
                e.config->>"$.explorerTitle" as title,
                e.config->>"$.explorerSubtitle" as subtitle,
                e.createdAt,
                e.updatedAt
            FROM
                explorers e
            WHERE
                e.isPublished = TRUE`
            ).then((rows) => {
                let processed = rows.map((row: any) => {
                    const tagsForExplorer = tagsBySlug[row.slug]
                    return {
                        slug: row.slug,
                        title: row.title,
                        subtitle: row.subtitle === "null" ? "" : row.subtitle,
                        tags: tagsForExplorer
                            ? tagsForExplorer.tags.map((tag) => tag.name)
                            : [],
                        createdAt: row.createdAt,
                        updatedAt: row.updatedAt,
                    }
                })
                if (!includeUnlisted) {
                    processed = processed.filter(
                        (row) => !row.tags.includes("Unlisted")
                    )
                }
                return _.keyBy(processed, "slug")
            })
        }
    )
}

export const getPublishedDataInsights = (
    knex: KnexReadonlyTransaction,
    limit = Number.MAX_SAFE_INTEGER // default to no limit
): Promise<MinimalDataInsightInterface[]> => {
    return knexRaw(
        knex,
        `-- sql
        SELECT
            content->>'$.title' AS title,
            authors,
            publishedAt,
            updatedAt,
            slug,
            ROW_NUMBER() OVER (ORDER BY publishedAt DESC) - 1 AS \`index\`
        FROM posts_gdocs
        WHERE type = '${OwidGdocType.DataInsight}'
            AND published = TRUE
            AND publishedAt <= NOW()
        ORDER BY publishedAt DESC
        LIMIT ?`,
        [limit]
    ).then((results) =>
        results.map((record: any) => ({
            ...record,
            index: Number(record.index),
            authors: JSON.parse(record.authors),
        }))
    ) as Promise<MinimalDataInsightInterface[]>
}

export async function checkIfSlugCollides(
    knex: KnexReadonlyTransaction,
    gdoc: OwidGdocBaseInterface
): Promise<boolean> {
    const existingGdoc = await knex(PostsGdocsTableName)
        .where({
            slug: gdoc.slug,
            published: true,
        })
        .whereNot({
            id: gdoc.id,
        })
        .first()
        .then((row) => (row ? parsePostsGdocsRow(row) : undefined))

    if (!existingGdoc) return false

    const existingCanonicalUrl = getCanonicalUrl("", existingGdoc)
    const incomingCanonicalUrl = getCanonicalUrl("", gdoc)

    return existingCanonicalUrl === incomingCanonicalUrl
}

export const getPublishedDataInsightCount = (
    knex: KnexReadonlyTransaction,
    topicSlug?: string
): Promise<number> => {
    let query
    if (topicSlug) {
        query = knexRawFirst<{ count: number }>(
            knex,
            `
            SELECT COUNT(DISTINCT(posts_gdocs.id)) AS count
            FROM posts_gdocs
            JOIN posts_gdocs_x_tags as pgt ON posts_gdocs.id = pgt.gdocId
            JOIN tags ON pgt.tagId = tags.id
            WHERE type = '${OwidGdocType.DataInsight}'
                AND published = TRUE
                AND publishedAt <= NOW()
                AND tags.slug = ?`,
            [topicSlug]
        )
    } else {
        query = knexRawFirst<{ count: number }>(
            knex,
            `
            SELECT COUNT(*) AS count
            FROM posts_gdocs
            WHERE type = '${OwidGdocType.DataInsight}'
                AND published = TRUE
                AND publishedAt <= NOW()`
        )
    }
    return query.then((res) => res?.count ?? 0)
}

export const getTotalNumberOfCharts = (
    knex: KnexReadonlyTransaction
): Promise<number> => {
    return knexRawFirst<{ count: number }>(
        knex,
        `-- sql
            SELECT COUNT(*) AS count
            FROM charts c
            JOIN chart_configs cc ON c.configId = cc.id
            WHERE cc.full ->> "$.isPublished" = "true"
        `
    ).then((res) => res?.count ?? 0)
}

export const getTotalNumberOfInUseGrapherTags = (
    knex: KnexReadonlyTransaction
): Promise<number> => {
    return knexRawFirst<{ count: number }>(
        knex,
        `
        SELECT COUNT(DISTINCT(tagId)) AS count
        FROM chart_tags
        WHERE chartId IN (
        SELECT id
        FROM charts
        WHERE publishedAt IS NOT NULL)`
    ).then((res) => res?.count ?? 0)
}

/**
 * For usage with GdocFactory.load, until we refactor Gdocs to be entirely Knex-based.
 */
export const getHomepageId = (
    knex: KnexReadonlyTransaction
): Promise<string | undefined> => {
    return knexRawFirst<{ id: string }>(
        knex,
        `-- sql
        SELECT
            posts_gdocs.id
        FROM
            posts_gdocs
        WHERE
            type = '${OwidGdocType.Homepage}'
            AND published = TRUE`
    ).then((result) => result?.id)
}

export const getHomepageAnnouncements = (
    knex: KnexReadonlyTransaction
): Promise<OwidGdocMinimalPostInterface[]> => {
    return knexRaw<DbRawPostGdoc>(
        knex,
        `-- sql
        SELECT
            pg.*
        FROM ${PostsGdocsTableName} pg
        WHERE pg.published = TRUE
        AND pg.publishedAt <= NOW()
        AND pg.type = '${OwidGdocType.Announcement}'
        AND pg.publicationContext = 'listed'
        ORDER BY pg.publishedAt DESC
        LIMIT 3
        `
    ).then((rows) => rows.map(rawGdocToMinimalPost))
}

export async function checkIsImageInDB(
    trx: KnexReadonlyTransaction,
    filename: string
): Promise<boolean> {
    const image = await trx("images").where("filename", filename).first()
    return !!image
}

export const getImageMetadataByFilenames = async (
    knex: KnexReadonlyTransaction,
    filenames: string[]
): Promise<Record<string, ImageMetadata & { id: number }>> => {
    if (filenames.length === 0) return {}
    const rows = await knexRaw<ImageMetadata & { id: number }>(
        knex,
        `-- sql
        SELECT
            id,
            filename,
            defaultAlt,
            updatedAt,
            originalWidth,
            originalHeight,
            cloudflareId
        FROM
            images
        WHERE filename IN (?)
        AND replacedBy IS NULL`,
        [filenames]
    )
    return _.keyBy(rows, "filename")
}

export const getPublishedGdocsWithTags = async (
    knex: KnexReadonlyTransaction,
    // The traditional "post" types - doesn't include data insights, author pages, the homepage, etc.
    gdocTypes: OwidGdocType[] = [
        OwidGdocType.Article,
        OwidGdocType.LinearTopicPage,
        OwidGdocType.TopicPage,
        OwidGdocType.AboutPage,
        OwidGdocType.Announcement,
    ],
    options: { excludeDeprecated?: boolean } = {}
): Promise<DBEnrichedPostGdocWithTags[]> => {
    const { excludeDeprecated = false } = options
    const query = `-- sql
        SELECT
        g.manualBreadcrumbs,
        g.content,
        g.createdAt,
        g.id,
        g.markdown,
        g.publicationContext,
        g.published,
        g.publishedAt,
        g.revisionId,
        g.slug,
        g.updatedAt,
        if( COUNT(t.id) = 0, JSON_ARRAY(), JSON_ARRAYAGG(
            JSON_OBJECT(
              'id', t.id,
              'name', t.name,
              'slug', t.slug
          ))) AS tags
    FROM
        posts_gdocs g
    LEFT JOIN posts_gdocs_x_tags gxt ON
        g.id = gxt.gdocId
    LEFT JOIN tags t ON
        gxt.tagId = t.id
    WHERE
        g.published = 1
        AND g.type IN (:gdocTypes)
        AND g.publishedAt <= NOW()
        ${
            excludeDeprecated
                ? `AND (g.content ->> '$."deprecation-notice"' IS NULL)`
                : ""
        }
    GROUP BY g.id
    ORDER BY g.publishedAt DESC`
    return knexRaw<DBRawPostGdocWithTags>(knex, query, {
        gdocTypes,
    }).then((rows) => rows.map(parsePostsGdocsWithTagsRow))
}

export const getNonGrapherExplorerViewCount = (
    knex: KnexReadonlyTransaction
): Promise<number> => {
    return knexRawFirst<{ count: number }>(
        knex,
        `-- sql
        SELECT
            COUNT(*) as count
        FROM
            explorers,
            json_table(config, "$.blocks[*]"
                COLUMNS (
                    type TEXT PATH "$.type",
                    NESTED PATH "$.block[*]"
                        COLUMNS (grapherId INT PATH "$.grapherId")
                    )
            ) t1
        WHERE
            isPublished = 1
            AND type = "graphers"
            AND grapherId IS NULL`
    ).then((res) => res?.count ?? 0)
}

/**
 * 1. Fetch all records in tag_graph:
 *    - isTopic = true when there is a published TP/LTP with the same slug as the tag
 *    - isSearchable = true when isTopic OR the tag has searchableInAlgolia set
 * 2. Group tags by their parentId
 * 3. Return the flat tag graph along with a __rootId property so that the UI knows which record is the root node
 */
export async function getFlatTagGraph(knex: KnexReadonlyTransaction): Promise<
    FlatTagGraph & {
        __rootId: number
    }
> {
    const tagGraphByParentId = await knexRaw<FlatTagGraphNode>(
        knex,
        `-- sql
        SELECT
            tg.parentId,
            tg.childId,
            tg.weight,
            t.slug,
            t.name,
            p.slug IS NOT NULL AS isTopic,
            (p.slug IS NOT NULL OR t.searchableInAlgolia = TRUE) AS isSearchable
        FROM
            tag_graph tg
        LEFT JOIN tags t ON
            tg.childId = t.id
        LEFT JOIN posts_gdocs p ON
            t.slug = p.slug AND p.published = 1 AND p.type IN (:types)
        -- order by descending weight, tiebreak by name
        ORDER BY tg.weight DESC, t.name ASC`,
        {
            types: [OwidGdocType.TopicPage, OwidGdocType.LinearTopicPage],
        }
    ).then((rows) => _.groupBy(rows, "parentId"))

    const tagGraphRootIdResult = await knexRawFirst<{
        id: number
    }>(
        knex,
        `-- sql
        SELECT id FROM tags WHERE name = "${TagGraphRootName}"`
    )
    if (!tagGraphRootIdResult) throw new Error("Tag graph root not found")

    return { ...tagGraphByParentId, __rootId: tagGraphRootIdResult.id }
}

// DFS through the tag graph and track all paths from a child to the root
// e.g. { "childTag": [ [parentTag1, parentTag2, childTag], [parentTag3, childTag] ] }
// Use with getUniqueNamesFromTopicHierarchies to collapse all paths to the child into
// a single array of unique parent tag names, including the original tags if they are topics.
export async function getTagHierarchiesByChildName(
    trx: KnexReadonlyTransaction,
    includeAreasAndTopicsOnly: boolean = false
): Promise<
    Record<DbPlainTag["name"], Pick<DbPlainTag, "id" | "name" | "slug">[][]>
> {
    const { __rootId, ...flatTagGraph } = await getFlatTagGraph(trx)
    const areaAndTopicTagNames = await getAllAreaAndTopicTagNames(trx)
    const tagGraph = createTagGraph(flatTagGraph, __rootId)
    const tagsById = await trx<DbPlainTag>("tags")
        .select("id", "name", "slug")
        .then((tags) => _.keyBy(tags, "id"))

    const pathsByChildName: Record<
        DbPlainTag["name"],
        Pick<DbPlainTag, "id" | "name" | "slug">[][]
    > = {}

    function trackAllPaths(
        node: TagGraphNode,
        currentPath: Pick<DbPlainTag, "id" | "name" | "slug">[] = []
    ): void {
        const currentTag = tagsById[node.id]

        const newPath = [...currentPath, currentTag]

        // Don't add paths for root node
        if (node.id !== __rootId) {
            const nodeName = currentTag.name
            if (!pathsByChildName[nodeName]) {
                pathsByChildName[nodeName] = []
            }

            let pathToAdd = newPath.slice(1) // Exclude root

            if (includeAreasAndTopicsOnly) {
                pathToAdd = pathToAdd.filter((tag) =>
                    areaAndTopicTagNames.includes(tag.name)
                )
            }

            // Only add non-empty paths
            if (pathToAdd.length > 0) {
                pathsByChildName[nodeName].push(pathToAdd)
            }
        }

        for (const child of node.children) {
            trackAllPaths(child, newPath)
        }
    }

    trackAllPaths(tagGraph)

    return pathsByChildName
}

export const getTopicHierarchiesByChildName = (
    trx: KnexReadonlyTransaction
): Promise<
    Record<DbPlainTag["name"], Pick<DbPlainTag, "id" | "name" | "slug">[][]>
> => getTagHierarchiesByChildName(trx, true)

export function getBestBreadcrumbs(
    tags: MinimalTag[],
    parentTagArraysByChildName: Record<
        string,
        Pick<DbPlainTag, "id" | "name" | "slug">[][]
    >
): BreadcrumbItem[] {
    // For each tag, find the best path according to our criteria
    // e.g. { "Nuclear Energy ": ["Energy and Environment", "Energy"], "Air Pollution": ["Energy and Environment"] }
    const result = new Map<number, Pick<DbPlainTag, "id" | "name" | "slug">[]>()

    for (const tag of tags) {
        const paths = parentTagArraysByChildName[tag.name]
        if (paths && paths.length > 0) {
            // Since getFlatTagGraph already orders by weight DESC and name ASC,
            // the first path in the array will be our best path
            result.set(tag.id, paths[0])
        }
    }

    // Only keep the topics in the paths, because only topics are clickable as breadcrumbs
    const topicsOnly = Array.from(result.values()).reduce(
        (acc, path) => {
            return [...acc, path.filter((tag) => tag.slug)]
        },
        [] as Pick<DbPlainTag, "id" | "name" | "slug">[][]
    )

    // Pick the longest path from result, assuming that the longest path is the best
    const longestPath = topicsOnly.reduce((best, path) => {
        return path.length > best.length ? path : best
    }, [])

    const baseUrl = IS_ARCHIVE ? PROD_URL : BAKED_BASE_URL
    const breadcrumbs = longestPath.map((tag) => ({
        label: tag.name,
        href: `${baseUrl}/${tag.slug}`,
    }))

    return breadcrumbs
}

export async function updateTagGraph(
    knex: KnexReadWriteTransaction,
    tagGraph: FlatTagGraph
): Promise<void> {
    const tagGraphRows: {
        parentId: number
        childId: number
        weight: number
    }[] = []

    for (const children of Object.values(tagGraph)) {
        for (const child of children) {
            tagGraphRows.push({
                parentId: child.parentId,
                childId: child.childId,
                weight: child.weight,
            })
        }
    }

    const existingTagGraphRows = await knexRaw<{
        parentId: number
        childId: number
        weight: number
    }>(
        knex,
        `-- sql
        SELECT parentId, childId, weight FROM tag_graph
    `
    )
    // Remove rows that are not in the new tag graph
    // Add rows that are in the new tag graph but not in the existing tag graph
    const rowsToDelete = existingTagGraphRows.filter(
        (row) =>
            !tagGraphRows.some(
                (newRow) =>
                    newRow.parentId === row.parentId &&
                    newRow.childId === row.childId &&
                    newRow.weight === row.weight
            )
    )
    const rowsToAdd = tagGraphRows.filter(
        (newRow) =>
            !existingTagGraphRows.some(
                (row) =>
                    newRow.parentId === row.parentId &&
                    newRow.childId === row.childId &&
                    newRow.weight === row.weight
            )
    )

    if (rowsToDelete.length > 0) {
        await knexRaw(
            knex,
            `-- sql
            DELETE FROM tag_graph
            WHERE parentId IN (?)
            AND childId IN (?)
            AND weight IN (?)
        `,
            [
                rowsToDelete.map((row) => row.parentId),
                rowsToDelete.map((row) => row.childId),
                rowsToDelete.map((row) => row.weight),
            ]
        )
    }

    if (rowsToAdd.length > 0) {
        await knexRaw(
            knex,
            `-- sql
            INSERT INTO tag_graph (parentId, childId, weight)
            VALUES ?
        `,
            [rowsToAdd.map((row) => [row.parentId, row.childId, row.weight])]
        )
    }
}

export function getMinimalTagsWithIsTopic(
    knex: KnexReadonlyTransaction
): Promise<MinimalTagWithIsTopic[]> {
    return knexRaw<MinimalTagWithIsTopic>(
        knex,
        `-- sql
        SELECT t.id,
        t.name,
        t.slug,
        t.slug IS NOT NULL AND MAX(IF(pg.type IN (:types), TRUE, FALSE)) AS isTopic,
        (t.slug IS NOT NULL AND MAX(IF(pg.type IN (:types), TRUE, FALSE))) OR t.searchableInAlgolia AS isSearchable
        FROM tags t
        LEFT JOIN posts_gdocs_x_tags gt ON t.id = gt.tagId
        LEFT JOIN posts_gdocs pg ON gt.gdocId = pg.id
        GROUP BY t.id, t.name
        ORDER BY t.name ASC
    `,
        {
            types: [
                OwidGdocType.TopicPage,
                OwidGdocType.LinearTopicPage,
                OwidGdocType.Article,
            ],
        }
    )
}

export async function getGrapherLinkTargets(
    knex: KnexReadonlyTransaction
): Promise<Pick<DbPlainPostGdocLink, "target">[]> {
    return knexRaw<Pick<DbPlainPostGdocLink, "target">>(
        knex,
        `-- sql
        SELECT target
        FROM posts_gdocs_links
        WHERE linkType = '${ContentGraphLinkType.Grapher}'
        `
    )
}

export async function getStaticVizLinkTargets(
    knex: KnexReadonlyTransaction
): Promise<Pick<DbPlainPostGdocLink, "target">[]> {
    return knexRaw<Pick<DbPlainPostGdocLink, "target">>(
        knex,
        `-- sql
        SELECT DISTINCT target
        FROM posts_gdocs_links
        WHERE linkType = '${ContentGraphLinkType.StaticViz}'
        `
    )
}

/**
 * Get the slugs of all datapages that are linked to in KeyIndicator blocks
 * Optionally exclude homepage KeyIndicator blocks, because for prefetching (the one current usecase for this function)
 * the SiteBaker fetches the indicator metadata separately
 */
export async function getLinkedIndicatorSlugs({
    knex,
    excludeHomepage = false,
}: {
    knex: KnexReadonlyTransaction
    excludeHomepage: boolean
}): Promise<Set<string>> {
    let rawQuery = `-- sql
        SELECT * FROM posts_gdocs WHERE published = TRUE`
    if (excludeHomepage) {
        rawQuery += ` AND type != '${OwidGdocType.Homepage}'`
    }
    return knexRaw<OwidGdoc>(knex, rawQuery)
        .then((gdocs) => gdocs.map((gdoc) => gdocFromJSON(gdoc)))
        .then((gdocs) => gdocs.flatMap((gdoc) => gdoc.linkedKeyIndicatorSlugs))
        .then((slugs) => new Set(slugs))
}

export async function selectReplacementChainForImage(
    trx: KnexReadonlyTransaction,
    id: string
): Promise<DbEnrichedImage[]> {
    return knexRaw<DbEnrichedImage>(
        trx,
        `-- sql
        WITH RECURSIVE replacement_chain AS (
            SELECT i.*
            FROM images i
            WHERE id = ?

            UNION ALL

            SELECT i.*
            FROM images i
            INNER JOIN replacement_chain rc ON i.replacedBy = rc.id
        )
        SELECT * FROM replacement_chain
        `,
        [id]
    )
}

export function getCloudflareImages(
    trx: KnexReadonlyTransaction,
    options?: {
        excludeFeaturedImages?: boolean
        excludeThumbnails?: boolean
        excludeResearchAndWriting?: boolean
    }
): Promise<DbEnrichedImageWithPageviews[]> {
    const {
        excludeFeaturedImages = false,
        excludeThumbnails = false,
        excludeResearchAndWriting = false,
    } = options || {}

    let havingClause = ""
    const conditions: string[] = []

    if (excludeFeaturedImages) {
        conditions.push("isFeaturedImage = 0")
        conditions.push("isBodyContent = 1")
    }
    if (excludeThumbnails) {
        conditions.push("i.filename NOT LIKE '%thumbnail%'")
    }
    if (excludeResearchAndWriting) {
        conditions.push("isInResearchAndWriting = 0")
    }

    if (conditions.length > 0) {
        havingClause = `HAVING ${conditions.join(" AND ")}`
    }

    return knexRaw<DbEnrichedImageWithPageviews>(
        trx,
        `-- sql
        SELECT
            i.*,
            COALESCE(SUM(pv.views_365d), 0) AS views_365d,
            MAX(CASE WHEN i.filename = pg.content->>'$."featured-image"' THEN 1 ELSE 0 END) AS isFeaturedImage,
            MAX(CASE WHEN
                JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body') IS NOT NULL
                AND (
                    JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].primary[*].value.filename') IS NULL
                    AND JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].secondary[*].value.filename') IS NULL
                    AND JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].rows[*].articles[*].value.filename') IS NULL
                    AND JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].more.articles[*].value.filename') IS NULL
                    AND JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].latest.articles[*].value.filename') IS NULL
                )
            THEN 1 ELSE 0 END) AS isBodyContent,
            MAX(CASE WHEN
                JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].primary[*].value.filename') IS NOT NULL
                OR JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].secondary[*].value.filename') IS NOT NULL
                OR JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].rows[*].articles[*].value.filename') IS NOT NULL
                OR JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].more.articles[*].value.filename') IS NOT NULL
                OR JSON_SEARCH(pg.content, 'one', i.filename, NULL, '$.body[*].latest.articles[*].value.filename') IS NOT NULL
            THEN 1 ELSE 0 END) AS isInResearchAndWriting
        FROM images i
        LEFT JOIN posts_gdocs_x_images pxi ON i.id = pxi.imageId
        LEFT JOIN posts_gdocs pg ON pxi.gdocId = pg.id AND pg.published = 1
        LEFT JOIN analytics_pageviews pv ON pv.url = CONCAT('https://ourworldindata.org/', pg.slug)
            AND pv.day = (SELECT MAX(day) FROM analytics_pageviews)
        WHERE i.cloudflareId IS NOT NULL
        AND i.replacedBy IS NULL
        GROUP BY i.id
        ${havingClause}`
    )
}

export function getCloudflareImage(
    trx: KnexReadonlyTransaction,
    filename: string
): Promise<DbEnrichedImageWithUserId | undefined> {
    return knexRawFirst(
        trx,
        `-- sql
        SELECT *
        FROM images
        WHERE filename = ?
        AND replacedBy IS NULL`,
        [filename]
    )
}

/**
 * Get the title, slug, and googleId of all gdocs that reference each image
 */
export function getImageUsage(trx: KnexReadonlyTransaction): Promise<
    Record<
        number,
        {
            title: string
            id: string
        }[]
    >
> {
    return knexRaw<{
        imageId: number
        posts: string
    }>(
        trx,
        `-- sql
        SELECT
        i.id as imageId,
        JSON_ARRAYAGG(
            JSON_OBJECT(
            'title', p.content->>'$.title',
            'id', p.id
            )
        ) as posts
        FROM posts_gdocs p
        JOIN posts_gdocs_x_images pi ON p.id = pi.gdocId
        JOIN images i ON pi.imageId = i.id
        WHERE i.replacedBy IS NULL
        GROUP BY i.id`
    ).then((results) =>
        Object.fromEntries(
            results.map((result) => [result.imageId, JSON.parse(result.posts)])
        )
    )
}

// A searchable tag is either a topic (tag with a published topic page) or has searchableInAlgolia set.
// We want to keep tags that have searchable children (i.e. areas and sub-areas) but not leaf nodes that aren't searchable
function checkDoesFlatTagGraphNodeHaveAnySearchableChildren(
    node: FlatTagGraphNode,
    flatTagGraph: FlatTagGraph
): boolean {
    if (node.isSearchable) return true
    const children = flatTagGraph[node.childId]
    if (!children) return false
    return children.some((child) =>
        checkDoesFlatTagGraphNodeHaveAnySearchableChildren(child, flatTagGraph)
    )
}

export async function generateTopicTagGraph(
    knex: KnexReadonlyTransaction
): Promise<TagGraphRoot> {
    const { __rootId, ...parents } = await getFlatTagGraph(knex)

    const tagGraphSearchableOnly = Object.entries(parents).reduce(
        (acc: FlatTagGraph, [parentId, children]) => {
            acc[Number(parentId)] = children.filter((child) => {
                if (child.parentId === __rootId) return true
                return checkDoesFlatTagGraphNodeHaveAnySearchableChildren(
                    child,
                    parents
                )
            })
            return acc
        },
        {} as FlatTagGraph
    )

    return createTagGraph(tagGraphSearchableOnly, __rootId)
}

/**
 * Fetch all topic tags from the database.
 * Topics are any tags that have a slug.
 * Returns name and slug of each topic tag.
 * Note: This does not include area tags, which are the top-level children of the tag
 * graph root and do not have a slug.
 */
export const getAllTopicTags = async (
    trx: KnexReadonlyTransaction
): Promise<{ slug: string; name: string; id: number }[]> => {
    return knexRaw<{ slug: string; name: string; id: number }>(
        trx,
        `-- sql
        SELECT t.slug, t.name, t.id
        FROM ${TagsTableName} t
        JOIN ${PostsGdocsTableName} pg ON pg.slug = t.slug
        WHERE pg.published = TRUE
        AND pg.type IN (:types)
        AND t.slug IS NOT NULL
        `,
        {
            types: [
                OwidGdocType.TopicPage,
                OwidGdocType.LinearTopicPage,
                OwidGdocType.Article,
            ],
        }
    )
}

/**
 * Fetch all area and topic tag names from the database.
 * Areas are the top-level children of the tag graph root, and don't have a slug.
 * Topics are tags whose slug matches a published topic-page or linear-topic-page,
 * or tags with searchableInAlgolia set.
 */
const getAllAreaAndTopicTagNames = async (
    trx: KnexReadonlyTransaction
): Promise<string[]> => {
    const results = await knexRaw<{ name: string }>(
        trx,
        `-- sql
        WITH topic_tags AS (
            SELECT t.name
            FROM ${TagsTableName} t
            LEFT JOIN ${PostsGdocsTableName} pg
                ON pg.slug = t.slug
                AND pg.published = TRUE
                AND pg.type IN ('topic-page', 'linear-topic-page')
            WHERE pg.id IS NOT NULL OR t.searchableInAlgolia = TRUE
        ),
        area_tags AS (
            SELECT t.name
            FROM ${TagsTableName} t
            JOIN ${TagGraphTableName} tg ON tg.childId = t.id
            JOIN ${TagsTableName} root ON tg.parentId = root.id
            WHERE root.name = '${TagGraphRootName}'
        )
        SELECT name FROM topic_tags
        UNION
        SELECT name FROM area_tags`
    )

    return results.map((row) => row.name)
}

export const getFeaturedMetricsByParentTagName = async (
    trx: KnexReadonlyTransaction
): Promise<FeaturedMetricByParentTagNameDictionary> => {
    const areaAndTopicTags = await getAllAreaAndTopicTagNames(trx)

    // Prepopulate a dictionary with every topic tag and area
    const blankFeaturedMetricsByParentTagName = Object.fromEntries(
        areaAndTopicTags.map((tag) => [tag, []])
    ) as FeaturedMetricByParentTagNameDictionary

    const featuredMetricsByParentTagName = (await knexRaw(
        trx,
        `-- sql
        SELECT m.*, t.name AS parentTagName, t.id AS parentTagId FROM ${FeaturedMetricsTableName} m
        JOIN ${TagsTableName} t ON m.parentTagId = t.id
        ORDER BY m.ranking ASC`
    ).then((rows) =>
        _.groupBy(rows, "parentTagName")
    )) as FeaturedMetricByParentTagNameDictionary

    return {
        ...blankFeaturedMetricsByParentTagName,
        ...featuredMetricsByParentTagName,
    }
}

/**
 * Takes a URL and checks if it points to a valid grapher, explorer, or multi-dim view.
 * Doesn't validate query params as this would be quite complicated / overkill for our needs
 */
export async function validateChartSlug(
    trx: KnexReadonlyTransaction,
    urlString: string
): Promise<{ isValid: boolean; reason: string }> {
    const url = Url.fromURL(urlString)
    if (url.isExplorer) {
        const urlSlug = url.slug
        if (!urlSlug)
            return { isValid: false, reason: "Missing slug in explorer URL" }

        const explorer = await trx(ExplorersTableName)
            .where({ slug: urlSlug, isPublished: true })
            .first()

        if (!explorer)
            return {
                isValid: false,
                reason: "Explorer not found or not published",
            }

        return { isValid: true, reason: "" }
    }

    if (url.isGrapher) {
        const slug = url.slug
        if (!slug)
            return { isValid: false, reason: "Missing slug in grapher URL" }

        const grapher = await knexRaw(
            trx,
            `-- sql
            SELECT id
            FROM ${ChartConfigsTableName}
            WHERE slug = ?
            AND full->>"$.isPublished" = "true"`,
            [slug]
        ).then((rows) => rows[0])

        if (!grapher) {
            const multiDim = await trx(MultiDimDataPagesTableName)
                .where({ slug, published: true })
                .first()

            if (!multiDim)
                return {
                    isValid: false,
                    reason: "Grapher not found or not published",
                }
        }

        return { isValid: true, reason: "" }
    }

    return { isValid: false, reason: "URL is neither explorer nor grapher" }
}

export const getUniqueTopicCount = async (
    trx: KnexReadonlyTransaction
): Promise<number> => {
    const count = await knexRawFirst<{ count: number }>(
        trx,
        `-- sql
        SELECT COUNT(DISTINCT(t.slug)) AS count
        FROM tags t
        LEFT JOIN posts_gdocs p ON t.slug = p.slug
        WHERE t.slug IS NOT NULL AND p.published IS true
        AND p.type IN (:types)`,
        {
            types: [
                OwidGdocType.TopicPage,
                OwidGdocType.LinearTopicPage,
                OwidGdocType.Article,
            ],
        }
    )
        .then((res) => (res ? res.count : 0))
        .catch((e) => {
            console.error("Failed to get unique topic count", e)
            throw e
        })
    // throw on count == 0 also
    if (!count) throw new Error("Failed to get unique topic count")
    return count
}

/**
 * Data structure for topics to be indexed in AI Search
 */
export interface TopicForAISearch {
    id: number
    name: string
    slug: string
    excerpt: string
    markdown: string
}

/**
 * Get topics (tags with associated topic pages) for AI Search indexing.
 * Returns tags that have a matching published topic page (TopicPage or LinearTopicPage).
 */
export async function getTopicsForAISearch(
    knex: KnexReadonlyTransaction
): Promise<TopicForAISearch[]> {
    const rows = await knexRaw<{
        id: number
        name: string
        slug: string
        excerpt: string | null
        markdown: string | null
    }>(
        knex,
        `-- sql
        SELECT
            t.id,
            t.name,
            t.slug,
            JSON_UNQUOTE(JSON_EXTRACT(p.content, '$.excerpt')) AS excerpt,
            p.markdown
        FROM tags t
        JOIN posts_gdocs p ON t.slug = p.slug
        WHERE
            t.slug IS NOT NULL
            AND p.published = 1
            AND p.type IN (:types)
        ORDER BY t.name ASC`,
        {
            types: [OwidGdocType.TopicPage, OwidGdocType.LinearTopicPage],
        }
    )

    return rows.map((row) => ({
        id: row.id,
        name: row.name,
        slug: row.slug,
        excerpt: row.excerpt ?? "",
        markdown: row.markdown ?? "",
    }))
}