import { Pool } from "pg";
import { v4 as uuidv4 } from "uuid";
import { query } from "../../../src/db/db";
import { CoordinateDTO, Device, Issue, RoomCapturePoint } from "../../../src/models/ingress";
import { CategoryTypeHierarchyMapping } from "../../../src/types/categoryTypeHierarchyMapping";
import { encryptCdeToken } from "../../../src/util/cde.token.util";
import { createAuthToken } from "./util";
import { linkFileReferences } from "../../../src/services/issues.fileReference.service";
import { CdeTypes } from "../../../src/types/project.attachment.types";

let pool: Pool | null = null;

function getDatabaseConfig() {
    return {
        host: "localhost",
        user: "citus",
        password: process.env.AZURE_POSTGRESQL_PASSWORD,
        database: "xyz-platform-database-v2",
        ssl: false,
        port: process.env.PG_PORT ? parseInt(process.env.PG_PORT, 10) : 5432,
    };
}

const getDbPool = async () => {
    const pool = new Pool(getDatabaseConfig());
    return pool;
};

// eslint-disable-next-line  @typescript-eslint/no-explicit-any
async function executeQuery(text: string, params: any[]) {
    if (!pool) {
        pool = await getDbPool();
    }
    return pool.query(text, params);
}

async function executeSimpleQuery(text: string) {
    if (!pool) {
        pool = await getDbPool();
    }
    return pool.query(text);
}

export async function setupAutodeskCloudHub(autodeskCloudHubId: string) {
    const callProcedureQuery = `CALL xyz."usp_InsertAutodeskCloudHub"($1, $2, $3)`;

    await executeQuery(callProcedureQuery, [autodeskCloudHubId, "", false]);

    const selectQuery = 'SELECT * FROM xyz."AutodeskCloudHub" WHERE "AccHubId" = $1';
    return executeQuery(selectQuery, [autodeskCloudHubId]);
}

export async function setupProjectAndGetDetails(mongoProjectId: string) {
    const autodeskCloudHubId = uuidv4();
    const accHubIdResult = await setupAutodeskCloudHub(autodeskCloudHubId);

    const callProcedureQuery = `CALL xyz."usp_InsertProject"
    ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::xyz."BuildType", $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24)`;
    await query(callProcedureQuery, [
        mongoProjectId,
        accHubIdResult.rows[0].AutodeskCloudHubId,
        "accProjectId",
        "targetAccFolderUrn",
        "E2E-TEST-PROJECT-NAME",
        "E2E-TEST-PROJECT-DESCRIPTION",
        "GB",
        "tenantName",
        "timezone",
        "mongoTenantId",
        "LEASE",
        "NONE", // progressWeightingMethod
        "METRIC", // measurementType
        null, // currencyId
        "1 Test Street", // addressLine1
        null, // addressLine2
        "London", // city
        "SW1A 1AA", // postcode
        null, // contractor
        false, // isPortfolioEnabled
        null, // (unused/null param)
        51.5074, // latitude
        -0.1278, // longitude
        "EMEA", // region
    ]);
    const selectQuery = 'SELECT * FROM xyz."Project" WHERE "MongoProjectId" = $1';
    const projectResult = await executeQuery(selectQuery, [mongoProjectId]);
    return projectResult.rows[0];
}

async function cleanupProjectPhotos(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    // Delete 360Captures first (they reference FileReference)
    const delete360CapturesQuery = 'DELETE FROM xyz."360Capture" WHERE "ProjectShardId" = $1';
    await executeQuery(delete360CapturesQuery, [projectShardId]);
    // Delete photos (they reference FileReference)
    const deletePhotosQuery = 'DELETE FROM xyz."Photo" WHERE "ProjectShardId" = $1';
    await executeQuery(deletePhotosQuery, [projectShardId]);
    // Delete file references (they reference Project)
    const deleteFileRefsQuery = 'DELETE FROM xyz."FileReference" WHERE "ProjectShardId" = $1';
    await executeQuery(deleteFileRefsQuery, [projectShardId]);
}

export async function deleteProject(projectId: string) {
    await cleanupProjectActivityCategories(projectId);
    await cleanupProjectCategoryType(projectId);
    await cleanupIssueType(projectId);
    await cleanupProjectIssueStatus(projectId);
    await cleanupProjectModelFolder(projectId);
    await cleanupProjectPhotos(projectId);
    await cleanupProjectSystemTypes(projectId);
    await cleanupProjectCommissioningWorkflows(projectId);

    const deleteQuery = 'DELETE FROM xyz."Project" WHERE "ProjectId" = $1';
    await executeQuery(deleteQuery, [projectId]);
}

export async function cleanupProjectSystemTypes(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    await executeQuery('DELETE FROM xyz."SystemType" WHERE "ProjectShardId" = $1', [projectShardId]);
}

export async function cleanupProjectCommissioningWorkflows(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    await executeQuery('DELETE FROM xyz."CommissioningWorkflow" WHERE "ProjectShardId" = $1', [projectShardId]);
}

export async function cleanupProjectCategoryType(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const deleteQuery = 'DELETE FROM xyz."CategoryType" WHERE "ProjectShardId" = $1';
    await executeQuery(deleteQuery, [projectShardId]);
}

export async function cleanupProjectActivityCategories(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const deleteQuery = 'DELETE FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1';
    await executeQuery(deleteQuery, [projectShardId]);
}

export async function cleanupProjectIssueStatus(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const deleteQuery = 'DELETE FROM xyz."IssueStatus" WHERE "ProjectShardId" = $1';
    await executeQuery(deleteQuery, [projectShardId]);
}

export async function cleanupProjectModelFolder(projectId: string) {
    console.log(`Deleting project ModelFolder ${projectId}`);
    const projectShardId = await getProjectShardId(projectId);
    const deleteQuery = 'DELETE FROM xyz."ModelFolder" WHERE "ProjectShardId" = $1';
    await executeQuery(deleteQuery, [projectShardId]);
}

export async function setupIssue(
    issueId: string,
    issueTypeId: string,
    projectId: string,
    title: string,
    description: string,
    xMeters: number,
    yMeters: number,
    zMeters: number,
    createdBy: string,
    issueSeverityCategoryId: string,
    issueLocationId: string,
    issueStatusId: string,
    modelId: string,
    modelElementId: string,
    reporterEmail: string,
    assigneeEmail: string
): Promise<Issue> {
    const projectShardId = await getProjectShardId(projectId);
    const insertQuery = `
        INSERT INTO xyz."Issue" ("IssueId", "IssueTypeId", "ProjectShardId", "Title", "Description", "XMeters",
                                 "YMeters",
                                 "ZMeters", "CreatedBy", "IssueSeverityCategoryId", "IssueLocationId", "IssueStatusId",
                                 "ModelElementId", "DueDate", "ReporterEmail", "AssigneeEmail", "ImportedFromCde",
                                 "IssueRaisedOn", "IsDeleted", "LastModifiedOn", "LastModifiedBy")
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19,
                NOW() AT TIME ZONE \'UTC\', 'e2e-test') RETURNING *;`;

    const issueModelMapInsertQuery = `INSERT INTO xyz."IssueModelMapping" ("IssueId", "ProjectShardId", "ModelId", "CreatedBy")
                                      VALUES ($1, $2, $3, $4) ON CONFLICT DO NOTHING;`;

    await executeQuery(insertQuery, [
        issueId,
        issueTypeId,
        projectShardId,
        title,
        description,
        xMeters,
        yMeters,
        zMeters,
        createdBy,
        issueSeverityCategoryId,
        issueLocationId,
        issueStatusId,
        modelElementId,
        "NOW()",
        reporterEmail,
        assigneeEmail,
        "FALSE",
        "NOW()",
        "FALSE",
    ]);

    await executeQuery(issueModelMapInsertQuery, [issueId, projectShardId, modelId, createdBy]);

    const rowResult = await executeQuery(
        `SELECT *
                                          FROM xyz."fn_GetIssue"($1, $2)`,
        [projectId, issueId]
    );
    const row = rowResult.rows[0];
    return {
        indexId: row.Id,
        issueId: row.IssueId,
        projectId: projectId,
        title: row.Title,
        issueLocationId: row.LocationDescription,
        issueTypeId: row.IssueTypeId,
        issueStatusId: row.IssueStatusId,
        description: row.Description,
        xMeters: row.XMeters,
        yMeters: row.YMeters,
        zMeters: row.ZMeters,
        modelId: row.ModelId,
        modelElementId: row.ModelElementId,
    };
}

/** Soft-delete issues (sets IsDeleted) — used by E2E to simulate tombstones while keeping rows. */
export async function markIssuesAsDeleted(projectId: string, issueIds: string[]): Promise<void> {
    if (issueIds.length === 0) {
        return;
    }
    const projectShardId = await getProjectShardId(projectId);
    await executeQuery(`UPDATE xyz."Issue" SET "IsDeleted" = TRUE WHERE "ProjectShardId" = $1 AND "IssueId" = ANY($2::uuid[])`, [projectShardId, issueIds]);
}

/** Hard-delete issues created in E2E (no comments/file refs expected). */
export async function deleteIssuesForE2ECleanup(projectId: string, issueIds: string[]): Promise<void> {
    if (issueIds.length === 0) {
        return;
    }
    const projectShardId = await getProjectShardId(projectId);
    await executeQuery(`DELETE FROM xyz."IssueModelMapping" WHERE "ProjectShardId" = $1 AND "IssueId" = ANY($2::uuid[])`, [projectShardId, issueIds]);
    await executeQuery(`DELETE FROM xyz."Issue" WHERE "ProjectShardId" = $1 AND "IssueId" = ANY($2::uuid[])`, [projectShardId, issueIds]);
}

export async function getIssueTypesSimplified(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const getQuery = `SELECT *
                      FROM xyz."IssueType"
                      WHERE "ProjectShardId" = '${projectShardId}'`;
    const queryResult = await executeQuery(getQuery, []);
    return queryResult.rows.map((row) => ({
        typeName: row.TypeName,
        displayName: row.DisplayName,
    }));
}

export async function cleanupIssueType(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const getQuery = `SELECT *
                      FROM xyz."IssueType"
                      WHERE "ProjectShardId" = '${projectShardId}'`;
    const queryResult = await executeQuery(getQuery, []);

    for (const row of queryResult.rows) {
        const deleteQuery = `DELETE
                             FROM xyz."IssueType"
                             WHERE "ProjectShardId" = '${projectShardId}'
                               AND "IssueTypeId" = '${row.IssueTypeId}'`;
        try {
            await executeQuery(deleteQuery, []);
        } catch {
            // console.error(e);
        }
    }
    return queryResult.rows.map((row) => ({
        typeName: row.TypeName,
        displayName: row.DisplayName,
    }));
}

export async function setupIssueType(projectId: string, type: string) {
    const projectShardId = await getProjectShardId(projectId);
    const insertQuery = `INSERT INTO xyz."IssueType" ("IssueTypeId", "ProjectShardId", "TypeName", "DisplayName",
                                                      "Description", "CreatedBy", "IsActiveInUI", "ImportedFromCde",
                                                      "ValidForIssueCreate", "ValidForIssueUpdate")
                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, FALSE,
                                 FALSE) RETURNING "IssueTypeId", "DisplayName", "Description","TypeName";`;

    const queryResult = await executeQuery(insertQuery, [uuidv4(), projectShardId, type, `displayName-${type}`, `description-${type}`, "e2e-test", true, false]);

    const row = queryResult.rows[0];
    return {
        issueTypeId: row.IssueTypeId,
        displayName: row.DisplayName,
        description: row.Description,
        typeName: row.TypeName,
    };
}

export async function setupTestCoordinate(projectId: string): Promise<CoordinateDTO> {
    const pointId = uuidv4().substring(20);
    const xCoordinate = Math.random() * 1000;
    const yCoordinate = Math.random() * 1000;
    const zCoordinate = Math.random() * 1000;
    const createdBy = "E2E test";

    return await setupCoordinate(projectId, pointId, xCoordinate, yCoordinate, zCoordinate, createdBy);
}

export async function setupCoordinate(
    projectId: string,
    pointId: string,
    xCoordinate: number,
    yCoordinate: number,
    zCoordinate: number,
    createdBy: string
): Promise<CoordinateDTO> {
    const callProcedureQuery = `
        SELECT *
        FROM xyz."fn_InsertCoordinate"(
                $1::UUID,
                $2::VARCHAR,
                $3::FLOAT,
                $4::FLOAT,
                $5::FLOAT,
                $6::VARCHAR
             )`;
    await query(callProcedureQuery, [projectId, pointId, xCoordinate, yCoordinate, zCoordinate, createdBy]);
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT *
                         FROM xyz."Coordinate"
                         WHERE "ProjectShardId" = $1
                           AND "PointId" = $2`;
    const result = await executeQuery(selectQuery, [projectShardId, pointId]);
    const createdCoordinate = result.rows[0];

    return {
        indexId: createdCoordinate.Id,
        coordinateId: createdCoordinate.CoordinateId,
        projectId: projectId,
        pointId: createdCoordinate.PointId,
        xCoordinate: createdCoordinate.XCoordinate,
        yCoordinate: createdCoordinate.YCoordinate,
        zCoordinate: createdCoordinate.ZCoordinate,
    };
}

export async function setupConflictCoordinate(
    projectId: string,
    conflictType: string,
    coordinateId: string,
    pointId: string,
    xCoordinate: number,
    yCoordinate: number,
    zCoordinate: number,
    createdBy: string
): Promise<{
    coordinateConflictId: string;
    projectId: string;
    coordinateId: string;
    conflictType: string;
    pointId: string;
    xCoordinate: number;
    yCoordinate: number;
    zCoordinate: number;
}> {
    const insertQuery = `
        INSERT INTO xyz."CoordinateConflict" ("CoordinateConflictId",
                                              "ConflictType",
                                              "ProjectShardId",
                                              "CoordinateId",
                                              "PointId",
                                              "XCoordinate",
                                              "YCoordinate",
                                              "ZCoordinate",
                                              "CreatedBy",
                                              "CreatedDate")
        VALUES ($1::UUID,
                $2::VARCHAR,
                $3::INT,
                $4::UUID,
                $5::VARCHAR,
                $6::FLOAT,
                $7::FLOAT,
                $8::FLOAT,
                $9::VARCHAR,
                NOW()::TIMESTAMP WITH TIME ZONE) RETURNING
            "CoordinateConflictId",
            "ProjectShardId",
            "CoordinateId",
            "ConflictType",
            "PointId",
            "XCoordinate",
            "YCoordinate",
            "ZCoordinate";
    `;
    const projectShardId = await getProjectShardId(projectId);

    const result = await query(insertQuery, [uuidv4(), conflictType, projectShardId, coordinateId, pointId, xCoordinate, yCoordinate, zCoordinate, createdBy]);
    const createdCoordinateConflict = result.rows[0];

    return {
        coordinateConflictId: createdCoordinateConflict.CoordinateConflictId,
        projectId: projectId,
        coordinateId: createdCoordinateConflict.CoordinateId,
        conflictType: createdCoordinateConflict.ConflictType,
        pointId: createdCoordinateConflict.PointId,
        xCoordinate: createdCoordinateConflict.XCoordinate,
        yCoordinate: createdCoordinateConflict.YCoordinate,
        zCoordinate: createdCoordinateConflict.ZCoordinate,
    };
}

export async function getCoordinates(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."Coordinate" where "ProjectShardId" = $1';
    const projectResult = await executeQuery(selectQuery, [projectShardId]);
    return projectResult.rows;
}

export async function getElementInstallationStatus(projectId: string, elementId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ElementInstallationStatus" where "ProjectShardId" = $1 and "ModelElementId" = $2';
    const result = await executeQuery(selectQuery, [projectShardId, elementId]);
    return result.rows;
}

export async function getCoordinateConflicts(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."CoordinateConflict" where "ProjectShardId" = $1';
    const projectResult = await executeQuery(selectQuery, [projectShardId]);
    return projectResult.rows;
}

export async function getIssues(issueId: string, projectId: string) {
    const selectQuery = 'SELECT * FROM xyz."fn_GetIssue"($1, $2)';
    const projectResult = await executeQuery(selectQuery, [projectId, issueId]);
    return projectResult.rows;
}

export async function getAnyIssueSeverityCategoryId() {
    const selectQuery = 'SELECT * FROM xyz."IssueSeverityCategory" limit 1';

    const projectResult = await executeQuery(selectQuery, []);
    return projectResult.rows[0].IssueSeverityCategoryId;
}

export async function getIssueTypeId(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."IssueType" where "ProjectShardId" = $1 and "IsActiveInUI" = true limit 1';

    const projectResult = await executeQuery(selectQuery, [projectShardId]);
    return projectResult.rows[0].IssueTypeId;
}

export async function getIssueStatusId(projectId: string, status: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."IssueStatus" where "ProjectShardId" = $1 and "Status" = $2';

    const projectResult = await executeQuery(selectQuery, [projectShardId, status]);
    return projectResult.rows[0].IssueStatusId;
}

export async function getIssueById(issueId: string) {
    const selectQuery = 'SELECT * FROM xyz."Issue" where "IssueId" = $1';
    const projectResult = await executeQuery(selectQuery, [issueId]);
    return projectResult.rows;
}

export async function getFileReference(fileReferenceId: string) {
    const selectQuery = 'SELECT * FROM xyz."FileReference" where "FileReferenceId" = $1';
    const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
    return projectResult.rows;
}

export async function getBatchFile(fileId: string) {
    const selectQuery = 'SELECT * FROM xyz."BatchFile" where "BatchFileId" = $1';
    const projectResult = await executeQuery(selectQuery, [fileId]);
    return projectResult.rows;
}

export async function getVideo(fileReferenceId: string) {
    const selectQuery = 'SELECT * FROM xyz."Video" where "FileReferenceId" = $1';
    const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
    return projectResult.rows;
}

export async function getAllVideos(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."Videos" where "ProjectShardId" = $1';
    return await executeQuery(selectQuery, [projectShardId]);
}

export async function getPhoto(fileReferenceId: string) {
    const selectQuery = 'SELECT * FROM xyz."Photo" where "FileReferenceId" = $1';
    const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
    return projectResult.rows;
}

export async function getAllPhotos(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."Photo" where "ProjectShardId" = $1';
    return await executeQuery(selectQuery, [projectShardId]);
}

export async function get360Capture(fileReferenceId: string) {
    const selectQuery = 'SELECT * FROM xyz."360Capture" where "FileReferenceId" = $1';
    const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
    return projectResult.rows;
}

export async function getAll360Captures(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."360Capture" where "ProjectShardId" = $1';
    return await executeQuery(selectQuery, [projectShardId]);
}

export async function linkFileToIssue(projectId: string, isuseId: string, fileReferenceId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const insertQuery = 'INSERT INTO xyz."IssueFileReferenceMapping" ("IssueId", "ProjectShardId", "FileReferenceId") VALUES ($1, $2, $3)';
    await executeQuery(insertQuery, [isuseId, projectShardId, fileReferenceId]);
}

export async function unlinkFileFromIssue(projectId: string, issueId: string, fileReferenceId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const deleteQuery = 'DELETE FROM xyz."IssueFileReferenceMapping" WHERE "IssueId" = $1 AND "ProjectShardId" = $2 AND "FileReferenceId" = $3';
    await executeQuery(deleteQuery, [issueId, projectShardId, fileReferenceId]);
}

export async function getIssueHistory(projectId: string, issueId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."IssueHistory" WHERE "ProjectShardId" = $1 AND "IssueId" = $2';
    const queryResult = await executeQuery(selectQuery, [projectShardId, issueId]);
    return queryResult.rows;
}

export async function getIssueFileReferenceMappings(projectId: string, issueId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."IssueFileReferenceMapping" WHERE "ProjectShardId" = $1 AND "IssueId" = $2';
    const queryResult = await executeQuery(selectQuery, [projectShardId, issueId]);
    return queryResult.rows;
}

/** Same row shape as the API uses (joins FileReference + Video). */
export async function getProjectVideoRow(projectId: string, fileReferenceId: string) {
    const result = await executeQuery(`SELECT * FROM xyz."fn_GetProjectVideo_V2"($1, $2)`, [projectId, fileReferenceId]);
    return result.rows[0];
}

/** E2E-only updates on `xyz."Video"` for asserting API `videoTakenOn` / `thumbnailDownloadUrl` against DB state. */
export async function setVideoE2eMetadata(fileReferenceId: string, opts: { videoTakenOnIso?: string | null; thumbnailCloudStoragePath?: string | null }) {
    if (opts.videoTakenOnIso !== undefined) {
        await executeQuery(`UPDATE xyz."Video" SET "VideoTakenOn" = $1::timestamptz WHERE "FileReferenceId" = $2::uuid`, [opts.videoTakenOnIso || null, fileReferenceId]);
    }
    if (opts.thumbnailCloudStoragePath !== undefined) {
        await executeQuery(`UPDATE xyz."Video" SET "ThumbnailCloudStoragePath" = $1 WHERE "FileReferenceId" = $2::uuid`, [opts.thumbnailCloudStoragePath, fileReferenceId]);
    }
}

export async function createVideoRefInDB(basePhotoBlobPath: string, projectId: string, fileReferenceId: string, mongoDeviceId: string, status: string) {
    const projectShardId = await getProjectShardId(projectId);
    const signedUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}`;
    try {
        let insertQuery =
            'INSERT INTO xyz."FileReference" ' +
            '("FileReferenceId", "ProjectShardId", "FileName", "FileExtension", "FileSizeBytes", "CloudStoragePath", "CreatedBy", "LastModifiedOn", "LastModifiedBy") ' +
            "VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() AT TIME ZONE 'UTC', $8) on conflict do nothing";
        await executeQuery(insertQuery, [fileReferenceId, projectShardId, "e2e-video-file-name", "e2e-file-extension", 1238, signedUrl, "e2e-test", "e2e-test"]);

        insertQuery = 'INSERT INTO xyz."Video" ' + '("FileReferenceId", "ProjectShardId","MongoDeviceId","Status")' + "VALUES ($1, $2, $3, $4) on conflict do nothing";

        await executeQuery(insertQuery, [fileReferenceId, projectShardId, mongoDeviceId, status]);
    } catch (err) {
        console.error(err);
    }
}

export async function create360CaptureRefInDB(basePhotoBlobPath: string, projectId: string, fileReferenceId: string, mongoDeviceId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const signedUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}`;
    const signedSmallUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}_small`;
    try {
        let insertQuery =
            'INSERT INTO xyz."FileReference" ' +
            '("FileReferenceId", "ProjectShardId", "FileName", "FileExtension", "FileSizeBytes", "CloudStoragePath", "CreatedBy", "LastModifiedOn", "LastModifiedBy") ' +
            "VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() AT TIME ZONE 'UTC', $8)";
        await executeQuery(insertQuery, [fileReferenceId, projectShardId, "e2e-360-capture-file-name", "e2e-file-extension", 1238, signedUrl, "e2e-test", "e2e-test"]);

        insertQuery =
            'INSERT INTO xyz."360Capture" ' +
            '("FileReferenceId", "ProjectShardId","SmallImageCloudStoragePath", "ActualXMeters", "ActualYMeters", "ActualZMeters", "MongoDeviceId")  ' +
            "VALUES ($1, $2, $3, $4, $5, $6, $7)";

        await executeQuery(insertQuery, [fileReferenceId, projectShardId, signedSmallUrl, 123.4, 45.6, 789.0, mongoDeviceId]);
    } catch (err) {
        console.error(err);
    }
}

export async function createPhotoRefInDB(basePhotoBlobPath: string, projectId: string, fileReferenceId: string, mongoDeviceId?: string) {
    const projectShardId = await getProjectShardId(projectId);

    const signedUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}`;
    const signedSmallUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}_small`;

    try {
        let insertQuery =
            'INSERT INTO xyz."FileReference" ' +
            '("FileReferenceId", "ProjectShardId", "FileName", "FileExtension", "FileSizeBytes", "CloudStoragePath", "CreatedBy", "LastModifiedOn", "LastModifiedBy") ' +
            "VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() AT TIME ZONE 'UTC', $8)";
        await executeQuery(insertQuery, [fileReferenceId, projectShardId, "e2e-fileName.jpg", "jpg", 1238, signedUrl, "e2e-test", "e2e-test"]);

        insertQuery =
            'INSERT INTO xyz."Photo" ' + '("FileReferenceId", "ProjectShardId","SmallImageCloudStoragePath", "MongoDeviceId")  ' + "VALUES ($1, $2, $3, $4) on conflict do nothing";

        await executeQuery(insertQuery, [fileReferenceId, projectShardId, signedSmallUrl, mongoDeviceId]);
    } catch (err) {
        console.error(err);
    }
}

export async function createIssueFileReferenceMapping(projectId: string, issueId: string, fileReferenceId: string) {
    await linkFileReferences(projectId, issueId, [{ fileReferenceId: fileReferenceId, type: "PHOTO" }], "E2E Test", "XYZ");
}

export async function createDBIssueFileReferenceMapping(projectId: string, issueId: string, fileReferenceId: string) {
    const porjectShardId = await getProjectShardId(projectId);
    const insertQuery = `INSERT INTO xyz."IssueFileReferenceMapping" ("IssueId", "FileReferenceId",
                                                         "ProjectShardId")
                         VALUES ($1, $2, $3) ON CONFLICT DO NOTHING;`;
    await executeQuery(insertQuery, [issueId, fileReferenceId, porjectShardId]);
}

/**
 * Seed a representative set of IssueMappingHistory rows (model / photo / activity-category)
 * for the issue-history e2e fixture. IssueMappingHistory is blanket-TRUNCATEd by other specs'
 * cleanup, so the activity-history spec seeds these in its own before() rather than relying on
 * the data dump surviving. MappedItemIds reference the dump-seeded lookups for the project.
 */
export async function seedIssueMappingHistoryFixture(projectId: string, issueId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const first = async (sql: string) => (await executeQuery(sql, [projectShardId])).rows;
    const [model] = await first('SELECT "ModelId" FROM xyz."Model" WHERE "ProjectShardId" = $1 ORDER BY "ModelName" LIMIT 1');
    const [file] = await first('SELECT "FileReferenceId" FROM xyz."FileReference" WHERE "ProjectShardId" = $1 ORDER BY "FileName" LIMIT 1');
    const cats = await first('SELECT "ActivityCategoryId" FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1 ORDER BY "CategoryName" LIMIT 2');
    const insert = `INSERT INTO xyz."IssueMappingHistory"
            ("TransactionId", "IssueId", "ProjectShardId", "MappingTableName", "MappedItemId", "MappedItemType", "OperationType", "Platform", "LastModifiedBy", "ValidFrom")
            VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, 'UPDATED', 'XYZ', 'user_e2e@example.com', $6) ON CONFLICT DO NOTHING;`;
    await executeQuery(insert, [issueId, projectShardId, "IssueModelMapping", model.ModelId, "MODEL", "2025-11-26T15:27:43Z"]);
    await executeQuery(insert, [issueId, projectShardId, "IssueFileReferenceMapping", file.FileReferenceId, "PHOTO", "2025-11-26T15:27:47Z"]);
    for (const c of cats) {
        await executeQuery(insert, [issueId, projectShardId, "IssueActivityCategoryMapping", c.ActivityCategoryId, "ACTIVITY_CATEGORY", "2026-03-09T10:46:34Z"]);
    }
}

export async function createModelElement(
    projectId: string,
    modelElementId: string,
    originalSourceModelElementId: string,
    latestSourceModeElementId: string,
    latestSourceElementId: string
) {
    const projectShardId = await getProjectShardId(projectId);
    await createSourceModelElement(projectShardId, originalSourceModelElementId);
    const insertQuery = `INSERT INTO xyz."ModelElement" ("ModelElementId", "ProjectShardId",
                                                         "OriginalSourceModelElementId", "LatestSourceModelElementId",
                                                         "LatestSourceElementId", "CreatedBy")
                         VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT DO NOTHING;`;
    await executeQuery(insertQuery, [modelElementId, projectShardId, originalSourceModelElementId, latestSourceModeElementId, latestSourceElementId, "E2E-Tester"]);
}

async function createSourceModelElement(projectShardId: string, elementId: string) {
    const insertQuery = `INSERT INTO xyz."SourceModelElement"
                         ("SourceModelElementId", "ProjectShardId", "SourceElementId", "OriginalSourceModelElementId")
                         VALUES ($2, $1, $3, $4) ON CONFLICT DO NOTHING;`;
    await executeQuery(insertQuery, [projectShardId, elementId, elementId, elementId]);
}

export async function createSiteMarkerWithMapping(projectId: string, udi: string) {
    const projectShardId = await getProjectShardId(projectId);
    const sitemarkerId = uuidv4();
    try {
        const insertQuery = `INSERT INTO xyz."SiteMarker"("SiteMarkerId", "SiteMarkerBatchId", "Udi", "Code",
                                                          "MongoSiteMarkerId", "MongoSiteMarkerBatchId", "Status",
                                                          "CreatedBy", "InsertedOn")
                             VALUES ($1, '8ae9cb25-beb5-4d0f-a4f0-05f98bd2cf85', $2, 'code-1', 'mong-marker-id-1',
                                     'BatchId01', 'TRANSFORM_ATTACHED', 'E2E Test', NOW()::TIMESTAMP WITH TIME ZONE)`;
        await executeQuery(insertQuery, [sitemarkerId, udi]);
    } catch (err) {
        console.error("error while creating marker", err);
    }
    const insertProjectMappingQuery = `INSERT INTO xyz."ProjectSiteMarkerMapping"("SiteMarkerId",
                                                                                  "ProjectShardId",
                                                                                  "CreatedBy",
                                                                                  "InsertedOn")
                                       VALUES ($1,
                                               $2,
                                               'e2e test',
                                               NOW()::TIMESTAMP WITH TIME ZONE) `;
    await executeQuery(insertProjectMappingQuery, [sitemarkerId, projectShardId]);
}

export async function getCategoryTypeByTypeName(projectId: string, typeName: string) {
    const projectShardId = await getProjectShardId(projectId);

    const selectQuery = 'SELECT * FROM xyz."CategoryType" WHERE "ProjectShardId" = $1 AND "TypeName" = $2 order by "InsertedOn" desc';
    const projectResult = await executeQuery(selectQuery, [projectShardId, typeName]);
    return projectResult.rows;
}

export async function getDeviceToken(deviceId: string) {
    const selectQuery = `SELECT *
                         FROM xyz."DeviceToken"
                         WHERE "DeviceTokenId" = $1`;
    const result = await executeQuery(selectQuery, [deviceId]);
    return result.rows;
}

export async function getDefaultDisciplineByName(typeName: string) {
    const selectQuery = 'SELECT * FROM xyz."DefaultDiscipline" WHERE "DisciplineName" = $1';
    const projectResult = await executeQuery(selectQuery, [typeName]);
    return projectResult.rows;
}

export async function getDefaultDisciplineById(id: string) {
    const selectQuery = 'SELECT * FROM xyz."DefaultDiscipline" WHERE "DisciplineId" = $1';
    const projectResult = await executeQuery(selectQuery, [id]);
    return projectResult.rows;
}

export async function resetModelFolder(modelFolderId: string) {
    const updateQuery = `UPDATE xyz."ModelFolder"
                         SET "LastModifiedBy" = null,
                             "LastModifiedOn" = null
                         WHERE "ModelFolderId" = $1`;
    await executeQuery(updateQuery, [modelFolderId]);
}

export async function resetModel(modelId: string) {
    const updateQuery = `UPDATE xyz."Model"
                         SET "IsDeleted" = false
                         WHERE "ModelId" = $1`;
    await executeQuery(updateQuery, [modelId]);
}

export async function getModelsForProject(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."Model" WHERE "ProjectShardId" = $1 ORDER BY "Id" DESC';
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function deleteCdeUserMapping(cdeProjectId: string) {
    const deleteQuery = 'DELETE from xyz."CdeProjectCdeUserMapping" WHERE "CdeProjectId" =$1';
    await executeQuery(deleteQuery, [cdeProjectId]);
}

export async function getProjectLinkToCdeProjectForUser(cdeProjectId: string) {
    const selectQuery = `SELECT *
                         FROM xyz."CdeProjectCdeUserMapping"
                         WHERE "CdeProjectId" = $1`;

    const { rows } = await query(selectQuery, [cdeProjectId]);
    return rows.length === 1;
}

export async function createCategoryType(projectId: string, typeName: string, importedFromSch: boolean) {
    const projectShardId = await getProjectShardId(projectId);
    const categoryTypeUUID = uuidv4();

    const level = CategoryTypeHierarchyMapping[typeName as keyof typeof CategoryTypeHierarchyMapping];

    const insertQuery =
        'INSERT INTO xyz."CategoryType" ("CategoryTypeId", "ProjectShardId", "TypeName", "HierarchyLevel", "ImportedFromSchedule") VALUES ($1, $2, $3, $4, $5) ON Conflict ("ProjectShardId", "TypeName", "ActvCodeTypeScope", "ImportedFromSchedule") do nothing';
    try {
        await executeQuery(insertQuery, [categoryTypeUUID, projectShardId, typeName, level, importedFromSch]);
        return categoryTypeUUID;
    } catch (err) {
        console.error(err);
        throw err;
    }
}

export async function createActivityCategory(projectId: string, parentCategoryId: string | null, categoryName: string, categoryTypeId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const activityCategoryUUID = uuidv4();
    const insertQuery =
        'INSERT INTO xyz."ActivityCategory" ("ActivityCategoryId", "ParentActivityCategoryId", "CategoryName", "ProjectShardId", "CategoryTypeId", "CategoryIdentifier") VALUES ($1, $2, $3,$4, $5, $6)';
    await executeQuery(insertQuery, [activityCategoryUUID, parentCategoryId, categoryName, projectShardId, categoryTypeId, categoryName]);
    return activityCategoryUUID;
}

export async function createActivityForAProject(projectId: string, userActivityId: string) {
    const projectShardId = await getProjectShardId(projectId);

    const insertQuery = `INSERT INTO xyz."Activity" ("ActivityId", "ProjectShardId", "UserActivityId", "SystemIdentifier")
                         VALUES ($1, $2, $3, $4)`;
    const activityUUID = uuidv4();
    await executeQuery(insertQuery, [activityUUID, projectShardId, userActivityId, userActivityId]);
    return activityUUID;
}

export async function updateElementActivityMappingToActive(modelActivityMappingId: string) {
    const updateQuery = `UPDATE xyz."ModelElementActivityMapping"
                         SET "IsActive" = TRUE
                         WHERE "ModelElementActivityMappingId" = $1`;
    await executeQuery(updateQuery, [modelActivityMappingId]);
}

export async function updateScheduleRevisionBaseLine(scheduleRevisionId: string, isBaseLine: boolean) {
    const updateQuery = `UPDATE xyz."ScheduleRevision"
                         SET "IsBaseline" = $1
                         WHERE "ScheduleRevisionId" = $2`;
    await executeQuery(updateQuery, [isBaseLine, scheduleRevisionId]);
}

export async function updateScheduleRevisionHistoricalBaseline(scheduleRevisionId: string, isHistoricalBaseline: boolean) {
    const updateQuery = `UPDATE xyz."ScheduleRevision"
                         SET "IsHistoricalBaseline" = $1
                         WHERE "ScheduleRevisionId" = $2`;
    await executeQuery(updateQuery, [isHistoricalBaseline, scheduleRevisionId]);
}

export async function updateScheduleRevisionCurrent(scheduleRevisionId: string, isCurrent: boolean) {
    const updateQuery = `UPDATE xyz."ScheduleRevision"
                         SET "IsCurrent" = $1
                         WHERE "ScheduleRevisionId" = $2`;
    await executeQuery(updateQuery, [isCurrent, scheduleRevisionId]);
}

export async function updateAllScheduleRevisionCurrentForProject(projectId: string, isCurrent: boolean) {
    const projectShardId = await getProjectShardId(projectId);

    const updateQuery = `UPDATE xyz."ScheduleRevision"
                         SET "IsCurrent" = $1
                         WHERE "ProjectShardId" = $2`;

    await executeQuery(updateQuery, [isCurrent, projectShardId]);
}

export async function getScheduleRevision(scheduleRevisionId: string) {
    const selectQuery = `SELECT *
                         FROM xyz."ScheduleRevision"
                         WHERE "ScheduleRevisionId" = $1`;
    const queryResult = await executeQuery(selectQuery, [scheduleRevisionId]);
    return queryResult.rows;
}

export async function getScheduleRevisionByUserFileId(userFileId: string) {
    console.log(`fetching schedule revision by userFileId ${userFileId}`);
    const selectQuery = `SELECT *
                         FROM xyz."ScheduleRevision"
                         WHERE "UserFileId" = $1`;
    const queryResult = await executeQuery(selectQuery, [userFileId]);
    return queryResult.rows;
}

export async function getActivityCategoryMapping(projectId: string, activityId: string, activityCategoryId: string) {
    const projectShardId = await getProjectShardId(projectId);

    const selectQuery = 'SELECT * FROM xyz."ActivityCategoryMapping" WHERE "ProjectShardId" = $1 AND "ActivityId"=$2  AND "ActivityCategoryId"=$3';
    const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, activityCategoryId]);
    return queryResult.rows;
}

export async function createActivityCategoryMapping(projectId: string, activityCategoryId: string, activityId: string, categoryTypeId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const activityCategoryMappingId = uuidv4();
    const insertQuery =
        'INSERT INTO xyz."ActivityCategoryMapping" ("ActivityCategoryMappingId", "ProjectShardId", "ActivityId", "ActivityCategoryId", "CategoryTypeId") VALUES ($1, $2, $3, $4, $5)';
    await executeQuery(insertQuery, [activityCategoryMappingId, projectShardId, activityId, activityCategoryId, categoryTypeId]);
    return activityCategoryMappingId;
}

export async function getElementActivityMapping(projectId: string, activityId: string, modelElementId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ModelElementActivityMapping" WHERE "ProjectShardId" = $1 AND "ActivityId"=$2  AND "ModelElementId"=$3 ';
    const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, modelElementId]);
    return queryResult.rows;
}

async function getProjectShardId(projectId: string) {
    const selectQuery = 'SELECT * FROM xyz."fn_GetProjectShardId" ($1)';
    const queryResult = await executeQuery(selectQuery, [projectId]);
    return queryResult.rows[0].fn_GetProjectShardId;
}

export async function getActivityCategories(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1';
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function getProject(projectId: string) {
    const selectQuery = 'SELECT * FROM xyz."Project" where "ProjectId"= ($1)';
    const queryResult = await executeQuery(selectQuery, [projectId]);
    return queryResult.rows[0];
}

export async function getProjectChangelog(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ProjectChangelog" WHERE "ProjectShardId" = $1';
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows[0];
}

export async function getProjectByMongoId(mongoProjectId: string) {
    const selectQuery = 'SELECT * FROM xyz."Project" WHERE "MongoProjectId" = $1';
    const { rows } = await query(selectQuery, [mongoProjectId]);
    return rows;
}

export async function getModelFolderByProject(projectId: string, groupName: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ModelFolder" WHERE "ProjectShardId" = $1 AND "FolderName" = $2';
    const queryResult = await executeQuery(selectQuery, [projectShardId, groupName]);
    return queryResult.rows;
}

export async function getModelFolderById(modelFolderId: string) {
    const selectQuery = 'SELECT * FROM xyz."ModelFolder" WHERE "ModelFolderId" = $1';
    const queryResult = await executeQuery(selectQuery, [modelFolderId]);
    return queryResult.rows;
}

export async function getLatestModelVersion(projectId: string, modelId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ModelVersion" WHERE "ProjectShardId" = $1 AND "ModelId" = $2 ORDER BY "VersionNumber" DESC LIMIT 1';
    const queryResult = await executeQuery(selectQuery, [projectShardId, modelId]);
    return queryResult.rows[0];
}

export async function getModelVersions(projectId: string, modelId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ModelVersion" WHERE "ProjectShardId" = $1 AND "ModelId" = $2';
    const queryResult = await executeQuery(selectQuery, [projectShardId, modelId]);
    return queryResult.rows;
}

export async function getAllElementActivityMapping(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ModelElementActivityMapping" WHERE "ProjectShardId" = $1';
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function getActivityCategoryById(projectId: string, activityCategoryId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1 AND "ActivityCategoryId" = $2';
    const queryResult = await executeQuery(selectQuery, [projectShardId, activityCategoryId]);
    return queryResult.rows;
}

export async function getActivityCategoryByName(projectId: string, categoryName: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1 AND "CategoryName"=$2  order by "InsertedOn" desc';
    const queryResult = await executeQuery(selectQuery, [projectShardId, categoryName]);
    return queryResult.rows;
}

export async function getScheduleActivityCategoryMapping(projectId: string, scheduleActivityCategoryMappingId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."ScheduleActivityCategoryMapping" WHERE "ProjectShardId" = $1 AND "ScheduleActivityCategoryMappingId"=$2';
    const queryResult = await executeQuery(selectQuery, [projectShardId, scheduleActivityCategoryMappingId]);
    return queryResult.rows;
}

export async function createScheduleActivityCategoryMapping(projectId: string, activityCategoryId: string, activityId: string, categoryTypeId: string, scheduleRevisionId: string) {
    const projectShardId = await getProjectShardId(projectId);

    const scheduleActivityCateogryMappingUUID = uuidv4();
    const insertQuery =
        'INSERT INTO xyz."ScheduleActivityCategoryMapping" ("ScheduleActivityCategoryMappingId", "ProjectShardId", "ScheduleRevisionId", "ActivityId", "CategoryTypeId", "ActivityCategoryId") VALUES ($1, $2, $3, $4, $5, $6)';
    await executeQuery(insertQuery, [scheduleActivityCateogryMappingUUID, projectShardId, scheduleRevisionId, activityId, categoryTypeId, activityCategoryId]);
    return scheduleActivityCateogryMappingUUID;
}

export async function createIssueActivityCategoryMapping(projectId: string, issueId: string, activityCategoryId: string, categoryTypeId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const insertQuery =
        'INSERT INTO xyz."IssueActivityCategoryMapping" ("ProjectShardId", "IssueId", "ActivityCategoryId", "CategoryTypeId", "CreatedBy") VALUES ($1, $2, $3, $4, $5);';
    await executeQuery(insertQuery, [projectShardId, issueId, activityCategoryId, categoryTypeId, "E2E Test"]);
}

export async function createRoomCapturePoint(
    projectId: string,
    modelRoomId: string,
    modelLevelId: string,
    userCapturePointId: string,
    createdFrom: string,
    xMeters: number,
    yMeters: number,
    zMeters: number,
    createdBy: string
) {
    const roomCapturePointId = uuidv4();
    const projectShardId = await getProjectShardId(projectId);
    const insertQuery =
        'INSERT INTO xyz."RoomCapturePoint" ("RoomCapturePointId", "ProjectShardId", "ModelRoomId", "ModelLevelId", "UserCapturePointId", "CreatedFrom", "XMeters", "YMeters", "ZMeters", "CreatedBy") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)';
    try {
        await executeQuery(insertQuery, [roomCapturePointId, projectShardId, modelRoomId, modelLevelId, userCapturePointId, createdFrom, xMeters, yMeters, zMeters, createdBy]);
    } catch (e) {
        console.error(e);
    }
    return roomCapturePointId;
}

export async function createUserFile(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const userFileIdUUID = uuidv4();
    const fileType = "fileType";
    const fileName = "Model-1-root-v2";
    const fileExtension = "xyz";
    const cloudStoragePath = "xyz";
    const uploadedOn = "2024-07-03 08:03:38.602167+00";
    const uploadedBy = "manual";
    const insertQuery =
        'INSERT INTO xyz."UserFile" ("UserFileId","ProjectShardId","FileType","FileName","FileExtension","CloudStoragePath","UploadedOn","UploadedBy") VALUES ($1, $2, $3, $4, $5, $6, $7, $8)';
    await executeQuery(insertQuery, [userFileIdUUID, projectShardId, fileType, fileName, fileExtension, cloudStoragePath, uploadedOn, uploadedBy]);
    return userFileIdUUID;
}

/**
 * Creates a UserFile in Queued, cancellable state for E2E tests that need to assert
 * successful cancel (200). The row has IngestStatus='Queued', IsCancellable=TRUE, CancelRequested=FALSE.
 */
export async function createCancellableUserFile(projectId: string): Promise<string> {
    const projectShardId = await getProjectShardId(projectId);
    const userFileIdUUID = uuidv4();
    const fileType = "Model";
    const fileName = "e2e-cancellable-model";
    const fileExtension = "xyz";
    const cloudStoragePath = "e2e/cancellable";
    const uploadedOn = "2024-07-03 08:03:38.602167+00";
    const uploadedBy = "e2e-test";
    const insertQuery = `INSERT INTO xyz."UserFile" (
        "UserFileId", "ProjectShardId", "FileType", "FileName", "FileExtension",
        "CloudStoragePath", "UploadedOn", "UploadedBy", "IngestStatus", "IsCancellable", "CancelRequested"
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'Queued', TRUE, FALSE)`;
    await executeQuery(insertQuery, [userFileIdUUID, projectShardId, fileType, fileName, fileExtension, cloudStoragePath, uploadedOn, uploadedBy]);
    return userFileIdUUID;
}

export async function getUserFile(projectId: string, userFileId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."UserFile" WHERE "ProjectShardId" = $1 AND "UserFileId" = $2';
    const queryResult = await executeQuery(selectQuery, [projectShardId, userFileId]);
    return queryResult.rows;
}

export async function createScheduleRevision(projectId: string, userFileId: string, isBaseLine: boolean, isCurrent: boolean, isHistoricalBaseline: boolean) {
    const projectShardId = await getProjectShardId(projectId);

    const scheduleRevisionIdUUID = uuidv4();
    const scheduleName = "test schedule";
    const insertQuery = `INSERT INTO xyz."ScheduleRevision" ("ScheduleRevisionId","ProjectShardId","UserFileId","ScheduleName","IsBaseline","IsCurrent","IsHistoricalBaseline") VALUES ($1, $2, $3, $4, $5, $6, $7)`;
    await executeQuery(insertQuery, [scheduleRevisionIdUUID, projectShardId, userFileId, scheduleName, isBaseLine, isCurrent, isHistoricalBaseline]);
    return scheduleRevisionIdUUID;
}

export async function setScheduleRevisionDates(projectId: string, scheduleRevisionId: string, scheduleStartDate: string, scheduleFinishDate: string) {
    const projectShardId = await getProjectShardId(projectId);
    const updateQuery = 'UPDATE xyz."ScheduleRevision" SET "ScheduleStartDate" = $1, "ScheduleFinishDate" = $2 WHERE "ProjectShardId" = $3 AND "ScheduleRevisionId" = $4';
    await executeQuery(updateQuery, [scheduleStartDate, scheduleFinishDate, projectShardId, scheduleRevisionId]);
}

export async function getCoordinateConflict(projectId: string): Promise<any> {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."CoordinateConflict"';
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function createScheduleCalendar(projectId: string, scheduleRevisionId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const scheduleCalendarId = uuidv4();
    const scheduleProjectId = null;
    const calendarName = "DefaultCalendar";
    const calendarType = "Standard";
    const dailyHours = 8.0;
    const workingDayRules = "Monday-Friday: 8 hours";
    const clndrId = 1;

    const insertQuery = `
        INSERT INTO xyz."ScheduleCalendar" ("ScheduleCalendarId", "ScheduleRevisionId", "ProjectShardId",
                                            "ScheduleProjectId", "CalendarName", "CalendarType",
                                            "DailyHours", "WeeklyHours", "MonthlyHours",
                                            "YearlyHours", "IsPrivate", "WorkingDayRules",
                                            "ClndrId", "ProjId", "BaseClndrId")
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
    `;

    await executeQuery(insertQuery, [
        scheduleCalendarId,
        scheduleRevisionId,
        projectShardId,
        scheduleProjectId,
        calendarName,
        calendarType,
        dailyHours,
        null,
        null,
        null,
        null,
        workingDayRules,
        clndrId,
        null,
        null,
    ]);

    return scheduleCalendarId;
}

export async function createWbs(projectId: string, fullWbsCode: string, systemIdentifier: string) {
    const wbsId = uuidv4();
    const projectShardId = await getProjectShardId(projectId);

    const insertQuery = `INSERT INTO xyz."Wbs" ("WbsId", "ProjectShardId", "FullWbsCode", "SystemIdentifier")
                         VALUES ($1, $2, $3, $4)`;

    await executeQuery(insertQuery, [wbsId, projectShardId, fullWbsCode, systemIdentifier]);

    return wbsId;
}

export async function createScheduleWbs(projectId: string, scheduleRevisionId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const fullWbsCode = "WBS-001";
    const systemIdentifier = "WBS-SAMPLE-1";

    const wbsId = await createWbs(projectId, fullWbsCode, systemIdentifier);

    const parentWbsId = uuidv4();
    const wbsName = "test";
    const startDate = "2024-09-01 08:00:00";
    const finishDate = "2024-09-30 17:00:00";
    const actualStartDate = "2024-09-01 08:00:00";
    const actualFinishDate = "2024-09-30 17:00:00";
    const isRoot = true;
    const wbsCode = "WBS-002";
    const sourceFileWbsId = "12345";

    const insertQuery =
        'INSERT INTO xyz."ScheduleWbs" ("WbsId","ScheduleRevisionId","ProjectShardId","ParentWbsId","WbsName","FullWbsCode","StartDate","FinishDate", "ActualStartDate", "ActualFinishDate", "IsRootWbs", "WbsCode","SystemIdentifier", "SourceFileWbsId") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)';
    await executeQuery(insertQuery, [
        wbsId,
        scheduleRevisionId,
        projectShardId,
        parentWbsId,
        wbsName,
        fullWbsCode,
        startDate,
        finishDate,
        actualStartDate,
        actualFinishDate,
        isRoot,
        wbsCode,
        systemIdentifier,
        sourceFileWbsId,
    ]);
    return wbsId;
}

export async function createScheduleActivity(projectId: string, scheduleRevisionId: string, parentWbsId: any, scheduleCalendarId: string, activityId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const activityIdUUID = uuidv4();

    const activityType = "Task";
    const activityStatus = "In Progress";
    const userActivityId = "ACT-001";
    const activityName = "Activity 1";
    const onDrivingPath = true;

    const startDate = "2024-09-01 08:00:00";
    const finishDate = "2024-09-10 17:00:00";
    const actualStartDate = "2024-09-01 08:00:00";
    const actualFinishDate = null;

    const systemIdentifier = "SAMPLE-SYS-IDENTIFIER";
    const taskId = 1;
    const projId = 1;
    const sourceFileWbsId = 1;
    const clndrId = 1;
    const validForProgressCalculations = true;

    const insertQuery = `INSERT INTO xyz."ScheduleActivity" ("ActivityId", "ScheduleRevisionId", "ProjectShardId",
                                                             "ParentWbsId",
                                                             "ScheduleCalendarId", "ActivityType", "ActivityStatus",
                                                             "UserActivityId", "ActivityName", "OnDrivingPath",
                                                             "StartDate",
                                                             "FinishDate", "ActualStartDate", "ActualFinishDate",
                                                             "SystemIdentifier",
                                                             "TaskId", "ProjId", "SourceFileWbsId", "ClndrId",
                                                             "ValidForProgressCalculations")
                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19,
                                 $20)`;
    await executeQuery(insertQuery, [
        activityId,
        scheduleRevisionId,
        projectShardId,
        parentWbsId,
        scheduleCalendarId,
        activityType,
        activityStatus,
        userActivityId,
        activityName,
        onDrivingPath,
        startDate,
        finishDate,
        actualStartDate,
        actualFinishDate,
        systemIdentifier,
        taskId,
        projId,
        sourceFileWbsId,
        clndrId,
        validForProgressCalculations,
    ]);

    return activityIdUUID;
}

export async function createScheduleDependency(
    projectId: string,
    scheduleRevisionId: string,
    activityId: string,
    predecessorActivityId: string,
    taskPredId: number,
    taskId: number,
    predTaskId: number
) {
    const projectShardId = await getProjectShardId(projectId);
    const scheduleDependencyId = uuidv4();
    const dependency = "dependency";

    const insertQuery = `
        INSERT INTO xyz."ScheduleDependency" ("ScheduleDependencyId", "ScheduleRevisionId", "ProjectShardId",
                                              "ActivityId", "PredecessorActivityId", "Dependency",
                                              "TaskPredId", "TaskId", "PredTaskId")
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
    `;

    await executeQuery(insertQuery, [scheduleDependencyId, scheduleRevisionId, projectShardId, activityId, predecessorActivityId, dependency, taskPredId, taskId, predTaskId]);

    return scheduleDependencyId;
}

export async function revertProjectSoftDelete(projectId: string, projectShardId: string) {
    console.log("DB_HELPER||Reverting project soft delete for projectId: ", projectId);
    const updateQuery = `UPDATE xyz."Project"
                         SET "IsDeleted" = false
                         WHERE "ProjectId" = $1`;

    await executeQuery(updateQuery, [projectId]);
    // Create ProjectMapping
    const createProjectMappingQuery = `INSERT INTO xyz."ProjectIdMapping" ("ProjectShardId", "ProjectId")
                                       VALUES ($1, $2)`;
    await executeQuery(createProjectMappingQuery, [projectShardId, projectId]);
}

export async function getScheduleActivity(ScheduleRevisionId: string) {
    const selectQuery = 'SELECT * FROM xyz."ScheduleActivity" WHERE "ScheduleRevisionId" = $1';
    const queryResult = await executeQuery(selectQuery, [ScheduleRevisionId]);
    return queryResult.rows[0];
}

export async function getCdeAccountByThirdPartyAccountId(thirdPartyAccountId: string) {
    const selectQuery = 'SELECT * FROM xyz."CdeAccount" WHERE "ThirdPartyCdeAccountId" = $1';
    const queryResult = await executeQuery(selectQuery, [thirdPartyAccountId]);
    return queryResult.rows[0];
}

export async function cleanupCDEProjectDetails(thirdPartyAccountId: string) {
    const selectCdeAccountQuery = 'SELECT * FROM xyz."CdeProject" WHERE "ThirdPartyCdeProjectId" = $1 ORDER BY "InsertedOn" DESC';

    const result = await executeQuery(selectCdeAccountQuery, [thirdPartyAccountId]);

    if (result.rows.length > 0) {
        const deleteSyncConfig = 'DELETE FROM xyz."CdeSyncConfig" WHERE "CdeProjectId" = $1';

        await executeQuery(deleteSyncConfig, [result.rows[0].CdeProjectId]);
        await cleanupUserByEmail("user@xyz.com");
        const deleteCdeRole = 'DELETE FROM xyz."CdeRole" WHERE "CdeProjectId" = $1';
        await executeQuery(deleteCdeRole, [result.rows[0].CdeProjectId]);
    }
    const deleteCdeAccount = 'DELETE FROM xyz."CdeProject" WHERE "ThirdPartyCdeProjectId" = $1';
    await executeQuery(deleteCdeAccount, [thirdPartyAccountId]);
}

export async function cleanupUserByEmail(email: string) {
    const selectCdeUser = 'SELECT * FROM xyz."CdeUser" WHERE "XyzUserEmail" = $1';
    const result = await executeQuery(selectCdeUser, [email]);
    if (result.rows.length > 0) {
        try {
            console.log("deleting user mapping for " + result.rows[0].CdeUserId);
            const deleteCdeProjectCdeUserMapping = 'DELETE FROM xyz."CdeProjectCdeUserMapping" where "CdeUserId"=$1';
            await executeQuery(deleteCdeProjectCdeUserMapping, [result.rows[0].CdeUserId]);
        } catch (err) {
            console.error(err);
        }
        try {
            const deleteCdeUser = 'DELETE FROM xyz."CdeUser" WHERE "CdeUserId" = $1';
            await executeQuery(deleteCdeUser, [result.rows[0].CdeUserId]);
        } catch (err) {
            console.error(err);
        }
    }
}

export async function cleanUpIssueMappingHistory() {
    const clearMappingHistory = 'TRUNCATE TABLE xyz."IssueMappingHistory"';
    await executeSimpleQuery(clearMappingHistory);
}

export async function getCdeProjectByThirdPartyProjectId(thirdPartyProjectId: string) {
    const selectQuery = 'SELECT * FROM xyz."CdeProject" WHERE "ThirdPartyCdeProjectId" = $1';
    const queryResult = await executeQuery(selectQuery, [thirdPartyProjectId]);
    return queryResult.rows[0];
}

export async function getCdeSyncConfigByCdeProjectId(cdeProjectId: string) {
    const selectQuery = 'SELECT * FROM xyz."CdeSyncConfig" WHERE "CdeProjectId" = $1';
    const queryResult = await executeQuery(selectQuery, [cdeProjectId]);
    return queryResult.rows[0];
}

export async function getCdeProjectToXyzUserLink(cdeProjectId: string) {
    const selectQuery = 'SELECT * FROM xyz."CdeProjectCdeUserMapping" WHERE "CdeProjectId" = $1';
    const queryResult = await executeQuery(selectQuery, [cdeProjectId]);
    return queryResult.rows[0];
}

export async function getProjectLocations(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = 'SELECT * FROM xyz."IssueLocation" WHERE "ProjectShardId" = $1';
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function cleanupIssueLocation(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const deleteQuery = 'DELETE  FROM xyz."IssueLocation" WHERE "ProjectShardId" = $1 AND "ThirdPartyLocationId" is not null';
    const queryResult = await executeQuery(deleteQuery, [projectShardId]);
    return queryResult.rows;
}

export async function createScheduleProject(projectId: string, scheduleRevisionId: string, projShortName: string, projId: number) {
    const projectShardId = await getProjectShardId(projectId);
    const scheduleProjectId = uuidv4();

    const insertQuery = `
        INSERT INTO xyz."ScheduleProject" ("ScheduleProjectId", "ScheduleRevisionId", "ProjectShardId",
                                           "ProjShortName", "ProjId")
        VALUES ($1, $2, $3, $4, $5)
    `;

    await executeQuery(insertQuery, [scheduleProjectId, scheduleRevisionId, projectShardId, projShortName, projId]);

    return scheduleProjectId;
}

export async function createScheduleResource(projectId: string, scheduleRevisionId: string, resourceName: string, userResourceId: string, resourceType: string, rsrcId: number) {
    const projectShardId = await getProjectShardId(projectId);
    const scheduleResourceId = uuidv4();

    const insertQuery = `
        INSERT INTO xyz."ScheduleResource" ("ScheduleResourceId", "ScheduleRevisionId", "ProjectShardId",
                                            "ResourceName", "UserResourceId", "ResourceType", "RsrcId")
        VALUES ($1, $2, $3, $4, $5, $6, $7)
    `;

    await executeQuery(insertQuery, [scheduleResourceId, scheduleRevisionId, projectShardId, resourceName, userResourceId, resourceType, rsrcId]);

    return scheduleResourceId;
}

export async function createScheduleResourceRate(projectId: string, scheduleRevisionId: string, scheduleResourceId: string, rsrcRateId: number, rsrcId: number) {
    const projectShardId = await getProjectShardId(projectId);
    const scheduleResourceRateId = uuidv4();

    const insertQuery = `
        INSERT INTO xyz."ScheduleResourceRate" ("ScheduleResourceRateId", "ScheduleRevisionId", "ProjectShardId",
                                                "ScheduleResourceId", "RsrcRateId", "RsrcId")
        VALUES ($1, $2, $3, $4, $5, $6)
    `;

    await executeQuery(insertQuery, [scheduleResourceRateId, scheduleRevisionId, projectShardId, scheduleResourceId, rsrcRateId, rsrcId]);

    return scheduleResourceRateId;
}

export async function updateStatusReport(projectId: string, scheduleRevisionId: string, blobUrl: string) {
    const projectShardId = await getProjectShardId(projectId);
    const updateQuery = `update reporting."ProgressOutput"
                         set "CloudStoragePath" = $1
                         Where "ProjectShardId" = $2
                           AND "ScheduleRevisionId" = $3`;
    try {
        console.log('updaditng  reporting."ProgressOutput"');
        await executeQuery(updateQuery, [blobUrl, projectShardId, scheduleRevisionId]);
    } catch (e) {
        console.error(e);
    }
}

/**
 * Creates a ProgressOutput record in the reporting schema for schedule parquet files.
 *
 * @param projectId - The project ID
 * @param scheduleRevisionId - The schedule revision ID
 * @param cloudStoragePath - The cloud storage path to the parquet file
 * @param outputType - The output type (default: 'planned')
 * @param outputHierarchyLevel - The output hierarchy level (default: 'activity')
 * @returns The created ProgressOutput ID
 */
export async function createProgressOutput(
    projectId: string,
    scheduleRevisionId: string,
    cloudStoragePath: string,
    outputType: string = "planned",
    outputHierarchyLevel: string = "activity"
): Promise<string> {
    const projectShardId = await getProjectShardId(projectId);
    const progressOutputId = uuidv4();

    // Get project name
    const projectQuery = `SELECT "Name"
                          FROM xyz."Project"
                          WHERE "ProjectId" = $1`;
    const projectResult = await executeQuery(projectQuery, [projectId]);
    const projectName = projectResult.rows.length > 0 ? projectResult.rows[0].Name : "Test Project";

    const insertQuery = `INSERT INTO reporting."ProgressOutput"("ProgressOutputId",
                                                                "ProjectShardId",
                                                                "ProjectId",
                                                                "ProjectName",
                                                                "ScheduleRevisionId",
                                                                "CloudStoragePath",
                                                                "OutputType",
                                                                "OutputHierarchyLevel",
                                                                "CalculatedOn",
                                                                "IsClientFacing")
                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW(), $9)`;

    await executeQuery(insertQuery, [progressOutputId, projectShardId, projectId, projectName, scheduleRevisionId, cloudStoragePath, outputType, outputHierarchyLevel, true]);

    return progressOutputId;
}

/** Stable id so e2e can replace the same mapping artefact across runs. */
const E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID = "f111e2e0-e2e0-42e0-8e2e-000000000001";
const E2E_PROJECT_ROOM_LEVEL_MODEL_VERSION_ID = "8d4ed424-05c4-4a17-a42d-94f1a6973dc8";

/**
 * Registers (or replaces) a ModelArtefact row so `getMappedArtefactByProjectId` returns
 * project-room-level-mapping parquet at the given blob path (full `container/path` as returned by uploadCloudFile).
 */
export async function ensureProjectRoomLevelMappingArtefactForE2e(projectId: string, fullCloudStoragePath: string): Promise<void> {
    const projectShardId = await getProjectShardId(projectId);
    const userFileId = "3efb7809-dd1b-4344-aa11-0b549579bb60";

    await executeQuery(`DELETE FROM xyz."ModelVersionArtefactMapping" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`, [
        E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID,
        projectShardId,
    ]);
    await executeQuery(`DELETE FROM xyz."ModelArtefact" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`, [E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID, projectShardId]);

    const insertArtefact = `
        INSERT INTO xyz."ModelArtefact" (
            "ModelArtefactId",
            "ProjectShardId",
            "UserFileId",
            "OutputFormat",
            "FileName",
            "FileExtension",
            "CloudStoragePath",
            "ArtefactHash",
            "IsClientFacing",
            "ConverterImageName",
            "CreatedBy",
            "FileSizeBytes",
            "OutputContent",
            "IsProjectScoped"
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
    `;
    await executeQuery(insertArtefact, [
        E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID,
        projectShardId,
        userFileId,
        "parquet",
        "project-room-level-mapping",
        "parquet",
        fullCloudStoragePath,
        "e2e-room-level-mapping-hash",
        true,
        "e2e-converter",
        "E2E-Test",
        1024,
        "project-room-level-mapping",
        false,
    ]);

    const insertMapping = `
        INSERT INTO xyz."ModelVersionArtefactMapping" ("InsertedOn", "ProjectShardId", "ModelArtefactId", "ModelVersionId")
        VALUES (NOW(), $1, $2, $3)
    `;
    await executeQuery(insertMapping, [projectShardId, E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID, E2E_PROJECT_ROOM_LEVEL_MODEL_VERSION_ID]);
}

export const E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID = "c111e2e0-e2e0-42e0-8e2e-000000000002";

/**
 * Registers (or replaces) a ModelArtefact row for `client-element-metas` parquet so
 * `getElementIdsForModel` in models.service can read element IDs at delete time.
 */
export async function ensureClientElementMetasArtefactForE2e(projectId: string, modelVersionId: string, fullCloudStoragePath: string): Promise<void> {
    const projectShardId = await getProjectShardId(projectId);
    const userFileId = "3efb7809-dd1b-4344-aa11-0b549579bb60";

    await executeQuery(`DELETE FROM xyz."ModelVersionArtefactMapping" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`, [
        E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID,
        projectShardId,
    ]);
    await executeQuery(`DELETE FROM xyz."ModelArtefact" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`, [E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, projectShardId]);

    await executeQuery(
        `INSERT INTO xyz."ModelArtefact" (
            "ModelArtefactId", "ProjectShardId", "UserFileId", "OutputFormat",
            "FileName", "FileExtension", "CloudStoragePath", "ArtefactHash",
            "IsClientFacing", "ConverterImageName", "CreatedBy", "FileSizeBytes",
            "OutputContent", "IsProjectScoped"
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)`,
        [
            E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID,
            projectShardId,
            userFileId,
            "parquet",
            "client-element-metas",
            "parquet",
            fullCloudStoragePath,
            "e2e-element-metas-hash",
            true,
            "e2e-converter",
            "E2E-Test",
            1024,
            "client-element-metas",
            false,
        ]
    );

    await executeQuery(
        `INSERT INTO xyz."ModelVersionArtefactMapping" ("InsertedOn", "ProjectShardId", "ModelArtefactId", "ModelVersionId")
         VALUES (NOW(), $1, $2, $3)`,
        [projectShardId, E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, modelVersionId]
    );
}

export async function cleanupClientElementMetasArtefactForE2e(projectId: string): Promise<void> {
    const projectShardId = await getProjectShardId(projectId);
    await executeQuery(`DELETE FROM xyz."ModelVersionArtefactMapping" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`, [
        E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID,
        projectShardId,
    ]);
    await executeQuery(`DELETE FROM xyz."ModelArtefact" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`, [E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, projectShardId]);
}

export async function createScheduleActivityResourceMapping(
    projectId: string,
    scheduleRevisionId: string,
    activityId: string,
    taskRsrcId: number,
    taskId: number,
    resourceType: string
) {
    const projectShardId = await getProjectShardId(projectId);
    const scheduleActivityResourceMappingId = uuidv4();

    const insertQuery = `
        INSERT INTO xyz."ScheduleActivityResourceMapping" ("ScheduleActivityResourceMappingId", "ScheduleRevisionId",
                                                           "ProjectShardId",
                                                           "ActivityId", "TaskRsrcId", "TaskId", "ResourceType")
        VALUES ($1, $2, $3, $4, $5, $6, $7)
    `;

    await executeQuery(insertQuery, [scheduleActivityResourceMappingId, scheduleRevisionId, projectShardId, activityId, taskRsrcId, taskId, resourceType]);

    return scheduleActivityResourceMappingId;
}

export async function createScheduleNonWorkingDay(
    projectId: string,
    scheduleRevisionId: string,
    scheduleCalendarId: string,
    nonWorkingDate: string,
    isoDayIndex: number,
    isPublicHoliday: boolean
) {
    const projectShardId = await getProjectShardId(projectId);
    const scheduleNonWorkingDayId = uuidv4();

    const insertQuery = `
        INSERT INTO xyz."ScheduleNonWorkingDay" ("ScheduleNonWorkingDayId", "ProjectShardId", "ScheduleRevisionId",
                                                 "ScheduleCalendarId", "NonWorkingDate", "IsoDayIndex",
                                                 "IsPublicHoliday")
        VALUES ($1, $2, $3, $4, $5, $6, $7)
    `;

    await executeQuery(insertQuery, [scheduleNonWorkingDayId, projectShardId, scheduleRevisionId, scheduleCalendarId, nonWorkingDate, isoDayIndex, isPublicHoliday]);

    return scheduleNonWorkingDayId;
}

export async function setupRoomCapturePoints(projectId: string, upsertedBy: string, roomCapturePoints: RoomCapturePoint[]) {
    const mappedRoomCapturePoints = roomCapturePoints.map((point) => ({
        RoomCapturePointId: point.roomCapturePointId,
        ModelRoomId: point.modelRoomId,
        ModelLevelId: point.modelLevelId,
        UserCapturePointId: point.userCapturePointId,
        XMeters: point.xMeters,
        YMeters: point.yMeters,
        ZMeters: point.zMeters,
        CreatedFrom: point.createdFrom,
        CreatedBy: upsertedBy,
    }));

    const roomCapturePointsJson = JSON.stringify(mappedRoomCapturePoints);
    const callProcedureQuery = `CALL xyz."usp_UpsertRoomCapturePoints"($1, $2, $3)`;
    try {
        await executeQuery(callProcedureQuery, [projectId, upsertedBy, roomCapturePointsJson]);
    } catch (error) {
        console.log(error);
    }
}

export async function setupTestConflictCoordinate(
    projectId: string,
    coordinate: string
): Promise<{
    coordinateConflictId: string;
    projectId: string;
    coordinateId: string;
    conflictType: string;
    pointId: string;
    xCoordinate: number;
    yCoordinate: number;
    zCoordinate: number;
}> {
    const conflictType = "Point_Id";
    const coordinateId = coordinate;
    const pointId = "point";
    const xCoordinate = Math.random() * 1000;
    const yCoordinate = Math.random() * 1000;
    const zCoordinate = Math.random() * 1000;
    return await setupConflictCoordinate(projectId, conflictType, coordinateId, pointId, xCoordinate, yCoordinate, zCoordinate, "unknown");
}

export async function getRoomCapturePointByRoomCapturePointId(roomCapturePointId: string) {
    const selectQuery = 'SELECT * FROM xyz."RoomCapturePoint" WHERE "RoomCapturePointId" = $1';
    const queryResult = await executeQuery(selectQuery, [roomCapturePointId]);
    return queryResult.rows;
}

export async function deleteRoomCapturePointByUserCapturePointId(userCapturePointId: string) {
    const callProcedureQuery = `DELETE
                                FROM xyz."RoomCapturePoint"
                                WHERE "UserCapturePointId" = $1`;
    await executeQuery(callProcedureQuery, [userCapturePointId]);
}

export async function getMongoProjectIdByProjectId(projectId: string) {
    const selectQuery = 'SELECT * FROM xyz."Project" WHERE "ProjectId" = $1';
    const queryResult = await executeQuery(selectQuery, [projectId]);
    return queryResult.rows[0].MongoProjectId;
}

export async function getCdeUserByCdeEmailId(cdeEmailId: string) {
    const selectQuery = 'SELECT * FROM xyz."CdeUser" WHERE "CdeUserEmail" = $1';
    const queryResult = await executeQuery(selectQuery, [cdeEmailId]);
    return queryResult.rows[0];
}

export async function getCdeUserByXyzEmailId(xyzEmailId: string) {
    const selectQuery = 'SELECT * FROM xyz."CdeUser" WHERE "XyzUserEmail" = $1';
    const queryResult = await executeQuery(selectQuery, [xyzEmailId]);
    return queryResult.rows[0];
}

export async function insertProjectSyncConfig(cdeProjectId: string, projectId: string, syncConfig: any) {
    const projectShardId = await getProjectShardId(projectId);

    const insertQuery =
        'INSERT INTO xyz."CdeSyncConfig" ' +
        '("ProjectShardId","SyncConfig", "CdeProjectId","CreatedBy","SyncActive","CdeSyncConfigId")' +
        ' VALUES ($1,$2,$3,$4,$5, $6) ON CONFLICT ("CdeProjectId", "ProjectShardId") DO UPDATE ' +
        "    SET         " +
        '"SyncConfig" = COALESCE(EXCLUDED."SyncConfig", xyz."CdeSyncConfig"."SyncConfig") ;';

    await executeQuery(insertQuery, [projectShardId, syncConfig, cdeProjectId, "E2E-Test", "TRUE", cdeProjectId]);
}

export async function createCdeProject(cdeProjectId: string, cdeAccountId: string, thirdPartyCdeProjectId: string, cdeProjectName: string) {
    try {
        const selectQuery = 'SELECT * FROM xyz."CdeProject" where "ThirdPartyCdeProjectId" = $1';
        const queryResult = await executeQuery(selectQuery, [thirdPartyCdeProjectId]);
        if (queryResult.rows.length > 0) {
            console.log("createCdeProject: createCdeProject already exists");
        } else {
            const insertQuery = `INSERT INTO xyz."CdeProject" ("CdeProjectId", "CdeAccountId", "ThirdPartyCdeProjectId",
                                                               "CdeProjectName", "CdeType", "CreatedBy")
                                 VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT
                                 ON CONSTRAINT "CdeProject_pkey" DO
            UPDATE SET
                "ThirdPartyCdeProjectId" = EXCLUDED."ThirdPartyCdeProjectId";`;
            await executeQuery(insertQuery, [cdeProjectId, cdeAccountId, thirdPartyCdeProjectId, cdeProjectName, CdeTypes.ACC, "Autodesk-e2e-test"]);
        }
    } catch (err) {
        console.error("Error while createCdeProject ", err);
        throw err;
    }
}

export async function createOrUpdateCdeUser(XyzUserEmail: string, cdePlatformName: string, futureExpiryForToken: boolean) {
    const autodeskToken = await createAuthToken("autodesk-user", [], futureExpiryForToken);
    const accessToken = encryptCdeToken(autodeskToken.token);
    const refreshToken = encryptCdeToken("dummy refresh Token");
    const thirdPartyCdeUserId = "third_party_cde_user_id";
    const cdeEmailId = "cde_email_id@cde.com";
    let queryResult: any;
    try {
        const insertQuery = `
            INSERT INTO xyz."CdeUser" ("ThirdPartyCdeUserId", "CdeUserEmail", "XyzUserEmail", "AccessToken",
                                       "RefreshToken",
                                       "AuthGranted", "CreatedBy", "CdePlatformName")
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8) ON CONFLICT ("CdeUserEmail", "CdePlatformName") DO
            UPDATE
                SET
                    "AccessToken" = EXCLUDED."AccessToken", "RefreshToken" = EXCLUDED."RefreshToken" RETURNING *`;

        queryResult = await executeQuery(insertQuery, [thirdPartyCdeUserId, cdeEmailId, XyzUserEmail, accessToken, refreshToken, true, XyzUserEmail, cdePlatformName]);
        console.log("createOrUpdateCdeUser called: " + XyzUserEmail, cdePlatformName);
    } catch (err) {
        console.error("Error while creating OrUpdateCdeUser", err);
    }
    return queryResult.rows[0];
}

export async function setupDevice(deviceId: string, deviceName: string, deviceCode: string, isActivated: boolean, status: string, deviceUdi: string, createdBy: string) {
    const insertQuery = `INSERT INTO xyz."Device" ("DeviceId", "DeviceName", "DeviceCode", "IsActivated", "Status",
                                                   "Udi", "CreatedBy")
                         VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("DeviceId") DO NOTHING;`;
    await executeQuery(insertQuery, [deviceId, deviceName, deviceCode, isActivated, status, deviceUdi, createdBy]);
}

export async function updateDeviceTenant(deviceId: string, tenantId: string) {
    const updateQuery = `UPDATE xyz."Device"
                         SET "TenantId" = $1
                         WHERE "DeviceId" = $2`;
    await executeQuery(updateQuery, [tenantId, deviceId]);
}

export async function createDeviceProjectMapping(deviceId: string, projectId: string) {
    const shardId = await getProjectShardId(projectId);

    const insertQuery = `INSERT INTO xyz."DeviceProjectMapping" ("DeviceId", "ProjectShardId", "CreatedBy")
                         VALUES ($1, $2, 'e2e-Test') ON CONFLICT ("ProjectShardId", "DeviceId") DO NOTHING;`;
    await executeQuery(insertQuery, [deviceId, shardId]);
}

export async function getDeviceById(deviceId: string) {
    const selectQuery = ` SELECT *
                          from xyz."Device"
                          WHERE "DeviceId" = $1`;
    const queryResult = await executeQuery(selectQuery, [deviceId]);
    return queryResult.rows[0];
}

export async function getDeviceMappingsById(deviceId: string) {
    const selectQuery = ` SELECT *
                          from xyz."DeviceProjectMapping"
                          WHERE "DeviceId" = $1`;
    const queryResult = await executeQuery(selectQuery, [deviceId]);
    return queryResult.rows;
}

export async function attachDeviceUdiToPhoto(deviceUdi: string, fileReferenceId: string) {
    const updateQuery = `UPDATE xyz."Photo"
                         SET "DeviceUdi" = $1
                         WHERE "FileReferenceId" = $2`;
    await executeQuery(updateQuery, [deviceUdi, fileReferenceId]);
}

export async function attachDeviceUdiToVideo(deviceUdi: string, fileReferenceId: string) {
    const updateQuery = `UPDATE xyz."Video"
                         SET "DeviceUdi" = $1
                         WHERE "FileReferenceId" = $2`;
    await executeQuery(updateQuery, [deviceUdi, fileReferenceId]);
}

export async function setupDevices(createdBy: string, devices: Device[]) {
    const mappedDevices = devices.map((device) => ({
        DeviceId: device.deviceId,
        DeviceName: device.deviceName,
        DeviceCode: device.deviceCode,
        IsActivated: device.isActivated,
        Status: device.status,
        Udi: device.deviceUdi,
        CreatedBy: createdBy,
    }));

    const insertQuery = `
        INSERT INTO xyz."Device" ("DeviceId", "DeviceName", "DeviceCode", "IsActivated", "Status", "Udi", "CreatedBy")
        VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("DeviceId") DO NOTHING;
    `;

    try {
        for (const device of mappedDevices) {
            await executeQuery(insertQuery, [device.DeviceId, device.DeviceName, device.DeviceCode, device.IsActivated, device.Status, device.Udi, device.CreatedBy]);
        }
    } catch (error) {
        console.error(error);
    }
}

export async function setupTimezones() {
    const insertQuery = `
        INSERT INTO xyz."Timezone" ("TimezoneId", "Timezone")
        VALUES ($1, $2) ON CONFLICT ("TimezoneId") DO NOTHING;
    `;

    const mockTimezones = [
        {
            timezoneId: "aff125a9-4df0-483b-b839-3c437f225cdc",
            timezone: "Europe/London",
        },
        {
            timezoneId: "bff125a9-4df0-483b-b839-3c437f225cdc",
            timezone: "Europe/Paris",
        },
    ];

    try {
        // Postgres repo inserts defaults, removing so the test is easier to maintain.
        await executeQuery('DELETE FROM xyz."Timezone";', []);
        for (const tz of mockTimezones) {
            await executeQuery(insertQuery, [tz.timezoneId, tz.timezone]);
        }
    } catch (error) {
        console.error(error);
    }
}

export async function setupCurrencies() {
    const insertQuery = `
        INSERT INTO xyz."Currency" ("CurrencyId", "Name", "Code")
        VALUES ($1, $2, $3) ON CONFLICT ("CurrencyId") DO NOTHING;
    `;

    const mockCurrencies = [
        {
            currencyId: "123e4567-e89b-12d3-a456-426614174000",
            currencyName: "United States Dollar",
            currencyCode: "USD",
        },
        {
            currencyId: "223e4567-e89b-12d3-a456-426614174001",
            currencyName: "Euro",
            currencyCode: "EUR",
        },
    ];

    try {
        // Postgres repo inserts defaults, removing so the test is easier to maintain.
        await executeQuery('DELETE FROM xyz."Currency";', []);
        for (const currency of mockCurrencies) {
            await executeQuery(insertQuery, [currency.currencyId, currency.currencyName, currency.currencyCode]);
        }
    } catch {
        //console.error(error);
    }
}

export async function getCurrencyByCurrencyCode(currencyCode: string) {
    const selectQuery = ` SELECT *
                          from xyz."Currency"
                          WHERE "Code" = $1`;
    const queryResult = await executeQuery(selectQuery, [currencyCode]);
    return queryResult.rows[0];
}

export async function setupPortfolios(tenantId: string): Promise<{ defaultPortfolioId: string; otherPortfolioId: string }> {
    const getDefaultQuery = `SELECT "PortfolioId" FROM xyz."Portfolio" WHERE "TenantId" = $1 AND "IsDefaultForTenant" = TRUE AND "IsDeleted" = FALSE LIMIT 1`;
    let defaultPortfolioId: string;
    try {
        const defaultResult = await executeQuery(getDefaultQuery, [tenantId]);
        if (defaultResult.rows.length > 0) {
            defaultPortfolioId = defaultResult.rows[0].PortfolioId;
        } else {
            defaultPortfolioId = uuidv4();
            const insertDefault = `INSERT INTO xyz."Portfolio" ("PortfolioId", "Name", "Description", "TenantId", "CurrencyId", "IsDefaultForTenant", "CreatedBy")
                VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("PortfolioId") DO NOTHING;`;
            await executeQuery(insertDefault, [defaultPortfolioId, "E2E Default Portfolio", "Default for tenant", tenantId, null, true, "e2e-test"]);
        }
        const otherPortfolioId = uuidv4();
        const insertOther = `INSERT INTO xyz."Portfolio" ("PortfolioId", "Name", "Description", "TenantId", "CurrencyId", "IsDefaultForTenant", "CreatedBy")
            VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("PortfolioId") DO NOTHING;`;
        await executeQuery(insertOther, [otherPortfolioId, "E2E Other Portfolio", "Non-default", tenantId, null, false, "e2e-test"]);
        return { defaultPortfolioId, otherPortfolioId };
    } catch (err) {
        console.error("setupPortfolios error", err);
        throw err;
    }
}

export async function setProjectIconUrl(projectId: string, iconUrl: string) {
    const updateQuery = `UPDATE xyz."Project" SET "IconUrl" = $1 WHERE "ProjectId" = $2`;
    await executeQuery(updateQuery, [iconUrl, projectId]);
}

export async function setProjectPortfolio(projectId: string, portfolioId: string) {
    const updateQuery = `UPDATE xyz."Project" SET "PortfolioId" = $1, "IsPortfolioEnabled" = TRUE WHERE "ProjectId" = $2`;
    await executeQuery(updateQuery, [portfolioId, projectId]);
}

export async function setProjectProgress(projectId: string) {
    const updateProjectQuery = `UPDATE xyz."Project" SET "ProgressWeightingId" = '2d3d42f1-d112-42cb-8885-0bc544a4ea2a' WHERE "ProjectId" = $1`;
    await executeQuery(updateProjectQuery, [projectId]);

    // Insert progress for current date and multiple past months so fn_GetPortfolioProjectData can
    // populate both latest progress and intervalProgress (e.g. monthly SPI aggregation).
    const insertReportQuery = `INSERT INTO reporting."ProjectProgress"("ProjectShardId", "CalendarDate",
                                                                       "TotalPlannedLaborUnits", "TotalLinkedElements",
                                                                       "LaborWeightedPlannedProgress",
                                                                       "LaborWeightedActualProgress",
                                                                       "ElementWeightedPlannedProgress",
                                                                       "ElementWeightedActualProgress",
                                                                       "LaborWeightedSpi", "ElementWeightedSpi",
                                                                       "ProgressOutputId", "DagsterRunId")
                               SELECT p."ProjectShardId",
                                      d."CalendarDate",
                                      1000.00,
                                      50,
                                      0.25,
                                      0.20,
                                      0.30,
                                      0.22,
                                      0.80,
                                      0.73,
                                      'e2e00000-0000-0000-0000-000000000001'::UUID,
                                      'e2e-dashboard-test-run'
                               FROM xyz."Project" p
                               CROSS JOIN (
                                   SELECT CURRENT_DATE AS "CalendarDate"
                                   UNION ALL SELECT (date_trunc('month', CURRENT_DATE) - INTERVAL '1 month')::date
                                   UNION ALL SELECT (date_trunc('month', CURRENT_DATE) - INTERVAL '2 months')::date
                                   UNION ALL SELECT (date_trunc('month', CURRENT_DATE) - INTERVAL '3 months')::date
                               ) d
                               WHERE p."ProjectId" = $1
                               ON CONFLICT ("ProjectShardId", "CalendarDate") DO NOTHING`;

    await executeQuery(insertReportQuery, [projectId]);
}

export async function getProjectIdByTenantId(tenantId: string): Promise<string | null> {
    const selectQuery = `SELECT p."ProjectId" FROM xyz."Project" p
                         JOIN xyz."Portfolio" portfolio ON portfolio."PortfolioId" = p."PortfolioId"
                         WHERE portfolio."TenantId" = $1 AND p."IsDeleted" = FALSE LIMIT 1`;
    const result = await executeQuery(selectQuery, [tenantId]);
    return result.rows.length > 0 ? result.rows[0].ProjectId : null;
}

export async function getTenantIdForProject(projectId: string): Promise<string | null> {
    const selectQuery = `SELECT portfolio."TenantId" FROM xyz."Project" p
                         JOIN xyz."Portfolio" portfolio ON portfolio."PortfolioId" = p."PortfolioId"
                         WHERE p."ProjectId" = $1`;
    const result = await executeQuery(selectQuery, [projectId]);
    return result.rows.length > 0 ? result.rows[0].TenantId : null;
}

export async function linkProjectToProgressWeighting(projectId: string, progressWeightingId: string) {
    const updateQuery = `UPDATE xyz."Project"
                         SET "ProgressWeightingId" = $1
                         WHERE "ProjectId" = $2`;
    await executeQuery(updateQuery, [progressWeightingId, projectId]);
    console.log(`Linked project ${projectId} to progress weighting ${progressWeightingId}`);
}

export async function getActivitiesProgress(projectId: string, activityId: string, calendarDate: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT *
                         FROM xyz."ActivityProgress"
                         WHERE "ProjectShardId" = $1
                           AND "ActivityId" = $2
                           AND DATE ("CalendarDate") = DATE ($3)`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, calendarDate]);
    return queryResult.rows;
}

export async function getActivitiesProgressHistory(projectId: string, activityId: string, calendarDate: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT *
                         FROM xyz."ActivityProgressHistory"
                         WHERE "ProjectShardId" = $1
                           AND "ActivityId" = $2
                           AND DATE ("CalendarDate") = DATE ($3)
                         ORDER BY "InsertedOn" DESC`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, calendarDate]);
    return queryResult.rows;
}

export async function getAllActivitiesProgressForDate(projectId: string, calendarDate: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT *
                         FROM xyz."ActivityProgress"
                         WHERE "ProjectShardId" = $1
                           AND DATE ("CalendarDate") = DATE ($2)`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, calendarDate]);
    return queryResult.rows;
}

export async function createModelWithVersion(
    projectId: string,
    modelId: string,
    parentModelFolderId: string | null,
    modelName: string,
    isCurrentVersion: boolean
): Promise<{ modelId: string; modelVersionId: string }> {
    const projectShardId = await getProjectShardId(projectId);
    const modelVersionId = uuidv4();

    const userFileId = await createUserFile(projectId);

    const insertModelQuery = `
        INSERT INTO xyz."Model" ("InsertedOn", "ModelId", "ProjectShardId", "ParentModelFolderId", 
                                 "ModelName", "Discipline", "IsDeleted")
        VALUES (NOW(), $1, $2, $3, $4, 'E2E-Test-Discipline', FALSE)
        ON CONFLICT ("ProjectShardId", "ModelId") DO NOTHING
    `;

    await executeQuery(insertModelQuery, [modelId, projectShardId, parentModelFolderId, modelName]);

    const insertVersionQuery = `
        INSERT INTO xyz."ModelVersion" ("ModelVersionId", "ProjectShardId", "ModelId", "UserFileId",
                                        "VersionName", "VersionNumber", "IsCurrentVersion", "InsertedOn")
        VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())
        ON CONFLICT ("ProjectShardId", "ModelVersionId") DO NOTHING
    `;

    await executeQuery(insertVersionQuery, [
        modelVersionId,
        projectShardId,
        modelId,
        userFileId,
        isCurrentVersion ? "Current Version" : "Old Version",
        isCurrentVersion ? 2 : 1,
        isCurrentVersion,
    ]);

    return { modelId, modelVersionId };
}

export async function deleteModelAndVersions(projectId: string, modelId: string): Promise<void> {
    const projectShardId = await getProjectShardId(projectId);

    // Collect artefact IDs linked to this model's versions before removing the mappings.
    const artefactResult = await executeQuery(
        `SELECT DISTINCT "ModelArtefactId" FROM xyz."ModelVersionArtefactMapping"
         WHERE "ProjectShardId" = $1
           AND "ModelVersionId" IN (
               SELECT "ModelVersionId" FROM xyz."ModelVersion"
               WHERE "ProjectShardId" = $1 AND "ModelId" = $2
           )`,
        [projectShardId, modelId]
    );
    const artefactIds: string[] = artefactResult.rows.map((r: any) => r.ModelArtefactId);

    await executeQuery(
        `DELETE FROM xyz."ModelVersionArtefactMapping"
         WHERE "ProjectShardId" = $1
           AND "ModelVersionId" IN (
               SELECT "ModelVersionId" FROM xyz."ModelVersion"
               WHERE "ProjectShardId" = $1 AND "ModelId" = $2
           )`,
        [projectShardId, modelId]
    );

    if (artefactIds.length > 0) {
        await executeQuery(
            `DELETE FROM xyz."ModelArtefact"
             WHERE "ProjectShardId" = $1
               AND "ModelArtefactId" = ANY($2::uuid[])`,
            [projectShardId, artefactIds]
        );
    }

    await executeQuery(`DELETE FROM xyz."ModelVersion" WHERE "ProjectShardId" = $1 AND "ModelId" = $2`, [projectShardId, modelId]);

    await executeQuery(`DELETE FROM xyz."Model" WHERE "ProjectShardId" = $1 AND "ModelId" = $2`, [projectShardId, modelId]);
}

export async function getModelById(projectId: string, modelId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."Model" WHERE "ProjectShardId" = $1 AND "ModelId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, modelId]);
    return queryResult.rows[0];
}

export async function getCommissioningWorkflowById(projectId: string, commissioningWorkflowId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."CommissioningWorkflow" WHERE "ProjectShardId" = $1 AND "CommissioningWorkflowId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, commissioningWorkflowId]);
    return queryResult.rows[0];
}

export async function getCommissioningWorkflowsByProject(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."CommissioningWorkflow" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function getSystemTypeById(projectId: string, systemTypeId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."SystemType" WHERE "ProjectShardId" = $1 AND "SystemTypeId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, systemTypeId]);
    return queryResult.rows[0];
}

export async function getSystemTypesByProject(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."SystemType" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function getCommissioningTaskFolderById(projectId: string, commissioningTaskFolderId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."CommissioningTaskFolder" WHERE "ProjectShardId" = $1 AND "CommissioningTaskFolderId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, commissioningTaskFolderId]);
    return queryResult.rows[0];
}

export async function getAssetTypeById(projectId: string, assetTypeId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."AssetType" WHERE "ProjectShardId" = $1 AND "AssetTypeId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, assetTypeId]);
    return queryResult.rows[0];
}

export async function getAssetTypesByProject(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."AssetType" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function getAssetById(projectId: string, assetId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."Asset" WHERE "ProjectShardId" = $1 AND "AssetId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, assetId]);
    return queryResult.rows[0];
}

export async function getAssetsByProject(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."Asset" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function getSystemById(projectId: string, systemId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."CommissioningSystem" WHERE "ProjectShardId" = $1 AND "CommissioningSystemId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, systemId]);
    return queryResult.rows[0];
}

export async function getSystemsByProject(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."CommissioningSystem" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}

export async function getAssetTypeSystemTypeMappingById(projectId: string, mappingId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."AssetTypeSystemTypeMapping" WHERE "ProjectShardId" = $1 AND "AssetTypeSystemTypeMappingId" = $2`;
    const queryResult = await executeQuery(selectQuery, [projectShardId, mappingId]);
    return queryResult.rows[0];
}

export async function getAssetTypeSystemTypeMappingsByProject(projectId: string) {
    const projectShardId = await getProjectShardId(projectId);
    const selectQuery = `SELECT * FROM xyz."AssetTypeSystemTypeMapping" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
    const queryResult = await executeQuery(selectQuery, [projectShardId]);
    return queryResult.rows;
}