--liquibase formatted sql
--changeset agentneo:fn_GetBiggestFile runOnChange:true stripComments:false endDelimiter:/
--comment: Create or replace xyz.fn_GetBiggestFile

----------------------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  17/06/2026
-- Description: Returns the metadata for the largest non-soft-deleted file (by FileSizeBytes) in a
--              project. NULL sizes rank lowest; ties broken by earliest InsertedOn then
--              FileReferenceId. Returns zero rows when no eligible file exists.
----------------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetBiggestFile";
CREATE OR REPLACE FUNCTION xyz."fn_GetBiggestFile"(_projectId UUID)
RETURNS TABLE (
    "fileReferenceId"  UUID,
    "projectId"        UUID,
    "fileName"         TEXT,
    "fileExtension"    TEXT,
    "xyzDisplayName"   TEXT,
    "description"      TEXT,
    "cloudStoragePath" TEXT,
    "insertedOn"       TIMESTAMP WITH TIME ZONE,
    "createdBy"        TEXT,
    "lastModifiedBy"   TEXT,
    "lastModifiedOn"   TIMESTAMP WITH TIME ZONE,
    "fileSizeBytes"    BIGINT,
    "fileHash"         TEXT,
    "importedFromCDE"  BOOLEAN,
    "thirdPartyFileId" TEXT
)
AS $$
DECLARE
    _projectShardId INT;
BEGIN
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    RETURN QUERY
    SELECT
        ref."FileReferenceId",
        _projectId,
        ref."FileName",
        ref."FileExtension",
        ref."XyzDisplayName",
        ref."Description",
        ref."CloudStoragePath",
        ref."InsertedOn",
        ref."CreatedBy",
        ref."LastModifiedBy",
        ref."LastModifiedOn",
        ref."FileSizeBytes",
        ref."FileHash",
        ref."ImportedFromCDE",
        ref."ThirdPartyFileId"
    FROM xyz."FileReference" ref
    WHERE ref."ProjectShardId" = _projectShardId
        AND ref."DeletedOn" IS NULL
    ORDER BY
        ref."FileSizeBytes" DESC NULLS LAST,
        ref."InsertedOn" ASC,
        ref."FileReferenceId" ASC
    LIMIT 1;
END
$$ LANGUAGE plpgsql;
/