import _ from 'lodash/fp';
import { isScalar } from '../../utils/types.mjs';
import { createJoin } from './join.mjs';
import { toColumnName } from './transform.mjs';

const COL_STRAPI_ROW_NUMBER = '__strapi_row_number';
const COL_STRAPI_ORDER_BY_PREFIX = '__strapi_order_by';
/**
 * Builds a SQL CASE expression that maps each row to a numeric status rank:
 *   0 = draft/created (no published sibling with same documentId [+ locale])
 *   1 = modified (draft updated_at > published updated_at for same documentId [+ locale])
 *   2 = published
 *
 * @param db - Database instance (used to access knex raw)
 * @param tableName - Actual DB table name (used in correlated subqueries)
 * @param tableAlias - Alias of the table in the outer query (e.g. "t0")
 * @param isI18n - When true, adds a locale condition to avoid cross-locale contamination
 */ const buildStatusSortExpression = (db, tableName, tableAlias, isI18n = false)=>{
    const localeCondition = isI18n ? ` AND sub.locale = ${tableAlias}.locale` : '';
    return db.connection.raw(`CASE WHEN NOT EXISTS(SELECT 1 FROM ?? sub WHERE sub.document_id = ${tableAlias}.document_id AND sub.published_at IS NOT NULL${localeCondition}) THEN 0 WHEN ${tableAlias}.updated_at > (SELECT MAX(sub.updated_at) FROM ?? sub WHERE sub.document_id = ${tableAlias}.document_id AND sub.published_at IS NOT NULL${localeCondition}) THEN 1 ELSE 2 END`, [
        tableName,
        tableName
    ]);
};
/**
 * Maps processed {@link OrderByValue} entries to descriptors Knex can consume. Translates the
 * virtual `status` sort into a parameterized raw SQL expression.
 */ const toKnexOrderByDescriptor = (db, tableName, rootTableAlias, entry)=>{
    if ('rawExpression' in entry) {
        return {
            column: buildStatusSortExpression(db, tableName, rootTableAlias, entry.isI18n),
            order: entry.order
        };
    }
    return {
        column: entry.column,
        order: entry.order
    };
};
const processOrderBy = (orderBy, ctx)=>{
    const { db, uid, qb, alias } = ctx;
    const meta = db.metadata.get(uid);
    const { attributes } = meta;
    if (typeof orderBy === 'string') {
        if (orderBy === 'status') {
            if (!attributes.publishedAt || !attributes.documentId) {
                throw new Error(`Cannot order by status on model ${uid}: missing publishedAt or documentId`);
            }
            const isI18n = 'locale' in attributes;
            return [
                {
                    rawExpression: 'status',
                    isI18n,
                    order: undefined
                }
            ];
        }
        const attribute = attributes[orderBy];
        if (!attribute) {
            throw new Error(`Attribute ${orderBy} not found on model ${uid}`);
        }
        const columnName = toColumnName(meta, orderBy);
        return [
            {
                column: qb.aliasColumn(columnName, alias)
            }
        ];
    }
    if (Array.isArray(orderBy)) {
        return orderBy.flatMap((value)=>processOrderBy(value, ctx));
    }
    if (_.isPlainObject(orderBy)) {
        return Object.entries(orderBy).flatMap(([key, direction])=>{
            const value = orderBy[key];
            if (key === 'status') {
                if (!attributes.publishedAt || !attributes.documentId) {
                    throw new Error(`Cannot order by status on model ${uid}: missing publishedAt or documentId`);
                }
                const isI18n = 'locale' in attributes;
                return [
                    {
                        rawExpression: 'status',
                        isI18n,
                        order: direction
                    }
                ];
            }
            const attribute = attributes[key];
            if (!attribute) {
                throw new Error(`Attribute ${key} not found on model ${uid}`);
            }
            if (isScalar(attribute.type)) {
                const columnName = toColumnName(meta, key);
                return {
                    column: qb.aliasColumn(columnName, alias),
                    order: direction
                };
            }
            if (attribute.type === 'relation' && 'target' in attribute) {
                const subAlias = createJoin(ctx, {
                    alias: alias || qb.alias,
                    attributeName: key,
                    attribute
                });
                return processOrderBy(value, {
                    db,
                    qb,
                    alias: subAlias,
                    uid: attribute.target
                });
            }
            throw new Error(`You cannot order on ${attribute.type} types`);
        });
    }
    throw new Error('Invalid orderBy syntax');
};
const getStrapiOrderColumnAlias = (column)=>{
    const trimmedColumnName = column.replaceAll('.', '_');
    return `${COL_STRAPI_ORDER_BY_PREFIX}__${trimmedColumnName}`;
};
/**
 * Wraps the original Knex query with deep sorting functionality.
 *
 * The function takes an original query and an OrderByCtx object as parameters and returns a new Knex query with deep sorting applied.
 */ const wrapWithDeepSort = (originalQuery, ctx)=>{
    /**
   * Notes:
   * - The generated query has the following flow: baseQuery (filtered unsorted data) -> T (partitioned/sorted data) --> resultQuery (distinct, paginated, sorted data)
   * - Pagination and selection are transferred from the original query to the outer one to avoid pruning rows too early
   * - Filtering (where) has to be done in the deepest sub query possible to avoid processing invalid rows and corrupting the final results
   * - We assume that all necessary joins are done in the original query (`originalQuery`), and every needed column is available with the right name and alias.
   */ const { db, qb, uid } = ctx;
    const { tableName } = db.metadata.get(uid);
    // The orderBy is cloned to avoid unwanted mutations of the original object
    const orderBy = _.cloneDeep(qb.state.orderBy);
    // Separate column-based entries from raw-expression entries (e.g. status)
    const columnOrderBy = orderBy.filter((ob)=>'column' in ob);
    const rawExpressionOrderBy = orderBy.filter((ob)=>'rawExpression' in ob);
    // 0. Init a new Knex query instance (referenced as resultQuery) using the DB connection
    //    The connection reuse the original table name (aliased if needed)
    const resultQueryAlias = qb.getAlias();
    const aliasedTableName = qb.mustUseAlias() ? alias(resultQueryAlias, tableName) : tableName;
    const resultQuery = db.getConnection(aliasedTableName);
    // 1. Clone the original query to create the sub-query (referenced as baseQuery) and avoid any mutation on the initial object
    const baseQuery = originalQuery.clone();
    const baseQueryAlias = qb.getAlias();
    // Clear unwanted statements from the sub-query 'baseQuery'
    // Note: `first()` is cleared through the combination of `baseQuery.clear('limit')` and calling `baseQuery.select(...)` again
    // Note: Those statements will be re-applied when duplicates are removed from the final selection
    baseQuery// Columns selection
    .clear('select')// Pagination and sorting
    .clear('order').clear('limit').clear('offset');
    // Override the initial select and return only the columns needed for the partitioning.
    // Only column-based orderBy entries are included here; raw expressions are applied later.
    baseQuery.select(// Always select the row id for future manipulation
    prefix(qb.alias, 'id'), // Select every column used in an order by clause, but alias it for future reference
    // i.e. if t2.name is present in an order by clause:
    //      Then, "t2.name" will become "t2.name as __strapi_order_by__t2_name"
    ...columnOrderBy.map((orderByClause)=>alias(getStrapiOrderColumnAlias(orderByClause.column), orderByClause.column)));
    // 2. Create a sub-query callback to extract and sort the partitions using row number
    const partitionedQueryAlias = qb.getAlias();
    const selectRowsAsNumberedPartitions = (partitionedQuery)=>{
        // Transform order by clause to their alias to reference them from baseQuery
        const prefixedOrderBy = columnOrderBy.map((orderByClause)=>({
                column: prefix(baseQueryAlias, getStrapiOrderColumnAlias(orderByClause.column)),
                order: orderByClause.order
            }));
        // partitionedQuery select must contain every column used for sorting
        const orderByColumns = prefixedOrderBy.map(_.prop('column'));
        partitionedQuery.select(// Always select baseQuery.id
        prefix(baseQueryAlias, 'id'), // Sort columns
        ...orderByColumns)// The row number is used to assign an index to every row in every partition
        .rowNumber(COL_STRAPI_ROW_NUMBER, (subQuery)=>{
            for (const orderByClause of prefixedOrderBy){
                subQuery.orderBy(orderByClause.column, orderByClause.order, 'last');
            }
            // And each partition/group is created based on baseQuery.id
            subQuery.partitionBy(`${baseQueryAlias}.id`);
        }).from(baseQuery.as(baseQueryAlias)).as(partitionedQueryAlias);
    };
    // 3. Create the final resultQuery query, that select and sort the wanted data using T
    // Filter to string-only select items before diffing (Knex.Raw items are passed through as-is)
    const stringSelect = qb.state.select.filter((s)=>typeof s === 'string');
    const originalSelect = _.difference(stringSelect, // Remove column-based order by columns from the initial select (raw expressions are not in select)
    columnOrderBy.map(_.prop('column')))// Alias everything in resultQuery
    .map((col)=>`${resultQueryAlias}.${col}`);
    resultQuery.select(originalSelect)// Join T to resultQuery to access sorted data
    // Notes:
    // - Only select the first row for each partition
    // - Since we're applying the "where" statement directly on baseQuery (and not on resultQuery), we're using an inner join to avoid unwanted rows
    .innerJoin(selectRowsAsNumberedPartitions, function joinPartitionedRows() {
        this// Only select rows that are returned by T
        .on(`${partitionedQueryAlias}.id`, `${resultQueryAlias}.id`)// By only selecting the rows number equal to 1, we make sure we don't have duplicate, and that
        // we're selecting rows in the correct order amongst the groups created by the "partition by"
        .andOnVal(`${partitionedQueryAlias}.${COL_STRAPI_ROW_NUMBER}`, '=', 1);
    });
    // Re-apply pagination params
    if (qb.state.limit) {
        resultQuery.limit(qb.state.limit);
    }
    if (qb.state.offset) {
        resultQuery.offset(qb.state.offset);
    }
    if (qb.state.first) {
        resultQuery.first();
    }
    // Re-apply the sort using T values (column-based), then append raw expression sorts.
    // Cast to any[] because Knex's TS types don't accept Knex.Raw in the column position,
    // even though Knex supports it at runtime.
    resultQuery.orderBy([
        // Transform column-based "order by" clause to their T alias and prefix them with T alias
        ...columnOrderBy.map((orderByClause)=>({
                column: prefix(partitionedQueryAlias, getStrapiOrderColumnAlias(orderByClause.column)),
                order: orderByClause.order
            })),
        // Rebuild raw expression entries with the correct outer alias (resultQueryAlias)
        ...rawExpressionOrderBy.map((entry)=>({
                column: buildStatusSortExpression(db, tableName, resultQueryAlias, entry.isI18n),
                order: entry.order
            })),
        // Add T.id to the order by clause to get consistent results in case several rows have the exact same order
        {
            column: `${partitionedQueryAlias}.id`,
            order: 'asc'
        }
    ]);
    return resultQuery;
};
// Utils
const alias = _.curry((alias, value)=>`${value} as ${alias}`);
const prefix = _.curry((prefix, value)=>`${prefix}.${value}`);

export { buildStatusSortExpression, getStrapiOrderColumnAlias, processOrderBy, toKnexOrderByDescriptor, wrapWithDeepSort };
//# sourceMappingURL=order-by.mjs.map