namespace PERMDAL.QueryBuilders;

// Required indexes:
//   MMEASURE: (TENANTID, STATUS) INCLUDE (NAME, DESCRIPTION, MEASURETYPE, UNIT, ISTEXT, KRAID, LOVTYPEID)
public static class MeasureQB
{
    public const string GET_BY_ID = @"
        SELECT M.MEASUREID    AS MeasureId,
               M.NAME         AS MeasureName,
               M.DESCRIPTION  AS Description,
               M.KRAID        AS KRAId,
               M.LOVTYPEID    AS LovTypeId,
               M.ISTEXT       AS IsText,
               M.REMARKS      AS Remarks,
               M.CREATEDBYID  AS CreatedById,
               M.CREATEDON    AS CreatedOn,
               M.MODIFIEDBYID AS ModifiedById,
               M.MODIFIEDON   AS ModifiedOn,
               M.SORTORDER    AS SortOrder,
               M.STATUS       AS Status,
               M.VERSION      AS Version,
               M.SOURCETYPE   AS SourceType,
               M.TENANTID     AS TenantId,
               M.MEASURETYPE  AS MeasureType,
               M.UNIT         AS Unit
        FROM   MMEASURE M
        WHERE  M.MEASUREID = @MeasureId
        AND    M.TENANTID  = @TenantId";

    public const string GET_LIST = @"
        SELECT M.MEASUREID    AS MeasureId,
               M.NAME         AS MeasureName,
               M.DESCRIPTION  AS Description,
               M.MEASURETYPE  AS MeasureType,
               M.UNIT         AS Unit,
               M.ISTEXT       AS IsText,
               M.STATUS       AS Status,
               M.KRAID        AS KRAId,
               K.KRANAME      AS KRAName,
               M.LOVTYPEID    AS LovTypeId,
               LT.LOVTYPENAME AS LovTypeName,
               COUNT(1) OVER() AS TotalCount
        FROM   MMEASURE M
        LEFT JOIN MKRA     K  ON M.KRAID     = K.KRAID
        LEFT JOIN MLOVTYPE LT ON M.LOVTYPEID = LT.LOVTYPEID
        WHERE  M.TENANTID = @TenantId
        AND    (@Status      IS NULL OR M.STATUS      = @Status)
        AND    (@MeasureType IS NULL OR M.MEASURETYPE = @MeasureType)
        ORDER  BY M.NAME
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_SELECT_LIST = @"
        SELECT M.MEASUREID    AS MeasureId,
               M.NAME         AS MeasureName,
               M.MEASURETYPE  AS MeasureType,
               M.UNIT         AS Unit
        FROM   MMEASURE M
        WHERE  M.TENANTID = @TenantId
        AND    M.STATUS   = 1
        {DYNAMIC_WHERE}
        ORDER  BY M.NAME";

    public const string INSERT = @"
        INSERT INTO MMEASURE
            (MEASUREID, NAME, DESCRIPTION, KRAID, LOVTYPEID, ISTEXT, REMARKS,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, SOURCETYPE, TENANTID,
             MEASURETYPE, UNIT)
        VALUES
            (@MeasureId, @MeasureName, @Description, @KRAId, @LovTypeId, @IsText, @Remarks,
             @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SortOrder, @Status, 1, @SourceType, @TenantId,
             @MeasureType, @Unit)";

    public const string UPDATE = @"
        UPDATE MMEASURE
        SET    NAME         = @MeasureName,
               DESCRIPTION  = @Description,
               KRAID        = @KRAId,
               LOVTYPEID    = @LovTypeId,
               ISTEXT       = @IsText,
               REMARKS      = @Remarks,
               MEASURETYPE  = @MeasureType,
               UNIT         = @Unit,
               STATUS       = @Status,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE(),
               VERSION      = VERSION + 1
        WHERE  MEASUREID = @MeasureId
        AND    TENANTID  = @TenantId";

    public const string DELETE = @"
        DELETE FROM MMEASURE
        WHERE  MEASUREID = @MeasureId
        AND    TENANTID  = @TenantId";

    public const string EXISTS_NAME = @"
        SELECT COUNT(1)
        FROM   MMEASURE
        WHERE  NAME       = @MeasureName
        AND    TENANTID   = @TenantId
        AND    MEASUREID <> @MeasureId";
}