export function fetchCustomersSubtreeThroughSQL(topLevelParentId: number, maxDepth: number = 10, log?: (message: string) => void): { [id: number]: {id: number, parent: number | null, children: number[]} } {
    /*
    Runs a SQL query to fetch the subtree of customers with a given top level parent id.
    The result is a dictionary where each node has an id, parent id and children ids.
    It doesn't care if the customer is inactive or not.
     */
    function buildTree(queryResult: query.QueryResultMap[]): { [id: number]: {id: number, parent: number | null, children: number[]} } {
        /**
         * This function builds a tree from the result of a SQL query that returns a list of ids.
         */
        const nodes: { [id: number]: {id: number, parent: number | null, children: number[]} } = {};
        for (const row of queryResult) {
            const ids = Object.values(row) as number[];
            for (let i = 0; i < ids.length; i++) {
                const id = ids[i];
                if (id !== null) {
                    // Get parent id if it exists, otherwise null
                    const parentId = i > 0 ? ids[i - 1] : null;
                    // Get the next id if it exists, otherwise null
                    const childId = i < ids.length - 1 ? ids[i + 1] : null;

                    // If the node doesn't exist, create it
                    if (!nodes[id]) {
                        nodes[id] = {id: id, parent: parentId, children: []}
                    }

                    // If the childId exists and it's not already in the childrenIds array, add it
                    if (childId !== null && !nodes[id].children.includes(childId)) {
                        nodes[id].children.push(childId);
                    }
                }
            }
        }
        return nodes;
    }

    if (maxDepth <= 1) {
        return {[topLevelParentId]: {id: topLevelParentId, parent: null, children: []}};
    }
    let selectPart = `SELECT `
    for (let i = 1; i <= maxDepth; i++) {
        selectPart += `c${i}.id as c${i}_id, `;
    }
    selectPart = selectPart.slice(0, -2) + ' FROM customer c1 ';
    let joins = '';
    for (let i = 2; i <= maxDepth + 1; i++) {
        joins += `LEFT JOIN customer c${i} ON c${i - 1}.id = c${i}.parent `;
    }
    const sql = selectPart + joins + `WHERE c1.id = ${topLevelParentId}`;
    log?.(sql)
    const result = SQLUnlimited(sql);
    return buildTree(result);
}

import {fetchCustomersSubtreeThroughSQL, getTopLevelParentId} from "../netsuite-libs/Helpers";